엑셀 중복값 찾기 색깔로 표시하는 방법
얼마 전 거래처 목록을 합치는데 같은 회사명이 여러 번 들어가 있었습니다. 눈으로 훑으면 놓치기 쉬워서 엑셀 중복값 찾기 색깔 기능을 썼더니 몇 번의 클릭으로 확인할 수 있었습니다. 다만 기본 기능은 중복된 값의 첫 번째 셀까지 모두 칠합니다. 두 번째로 등장한 값만 표시하고 싶다면 설정 방법이 조금 다릅니다.
같은 값이 있는 셀에 바로 색깔 넣기
회사명이 A열에 있고 A1이 제목이라면, 데이터가 들어 있는 A2:A300을 선택합니다. 그다음 홈 → 조건부 서식 → 셀 강조 표시 규칙 → 중복 값으로 이동하세요. 창에서 ‘중복’을 선택하고 원하는 색을 고른 뒤 확인을 누르면 됩니다. 엑셀 버전에 따라 메뉴의 띄어쓰기나 위치는 조금 달라도 ‘조건부 서식’ 안에서 찾을 수 있습니다.
예를 들어 A2와 A18에 모두 ‘가온상사’가 있으면 두 셀에 같은 색이 들어갑니다. 세 번 등장하면 세 셀 모두 표시됩니다. 이 기능은 중복 그룹 전체를 보여주는 방식이라, 처음 입력된 값은 그대로 두고 뒤에 추가된 값만 골라내는 용도와는 구분해야 합니다.
범위를 고를 때는 제목 셀을 빼는 편이 좋습니다. 여러 열을 한꺼번에 선택하면 서로 다른 열의 같은 값까지 비교 대상에 들어갈 수 있습니다. 회사명을 확인하려면 회사명 열만, 이메일을 확인하려면 이메일 열만 선택하는 식으로 시작하면 결과를 이해하기 쉽습니다.
두 번째로 나온 값부터 표시하려면
원본으로 남길 첫 번째 값은 색칠하지 않고, 반복해서 나온 값만 표시할 수도 있습니다. A2:A300을 선택한 상태에서 홈 → 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정을 고릅니다. 수식 입력 칸에는 =AND(A2<>"",COUNTIF($A$2:A2,A2)>1)을 넣고 ‘서식’에서 채우기 색을 지정합니다.
이 수식은 A2부터 현재 행까지 같은 값이 몇 번 나왔는지 셉니다. 그래서 첫 번째 ‘가온상사’는 색이 없고 두 번째부터 색이 들어갑니다. 앞의 A2<>"" 조건은 빈 셀까지 중복으로 칠해지는 일을 막습니다. 실제 데이터가 A5에서 시작한다면 수식의 시작 위치와 선택 범위도 A5로 맞춰야 합니다.
여기서 ‘첫 번째’는 현재 행 순서를 기준으로 합니다. 날짜가 가장 빠른 거래를 원본으로 삼으려면 날짜순으로 먼저 정렬한 뒤 규칙을 적용하세요. 정렬 전에 어떤 행을 기준으로 남길지 정해두면 나중에 중복 항목을 검토할 때 덜 헷갈립니다.
셀 하나가 아니라 해당 행 전체를 칠하기
회사명만 붉게 보이면 옆의 담당자나 금액을 확인할 때 시선이 왔다 갔다 합니다. A열 회사명을 기준으로 A열부터 D열까지 한 줄 전체를 칠하려면 A2:D300을 선택하고 새 조건부 서식 규칙에 =AND($A2<>"",COUNTIF($A$2:$A$300,$A2)>1)을 입력합니다. 그러면 같은 회사명이 두 번 이상 있는 모든 행이 표시됩니다.
수식의 $A2는 판단 기준을 A열에 고정한다는 뜻입니다. 이 표시가 빠지면 B열, C열로 서식이 적용될 때 참조 열도 옮겨가 기대와 다른 행이 칠해질 수 있습니다. 반면 행 번호 2는 각 행을 따라 바뀌어야 하므로 고정하지 않습니다.
행 전체를 칠하되 첫 등장 행은 제외하려면 COUNTIF 부분을 COUNTIF($A$2:$A2,$A2)>1로 바꾸면 됩니다. 범위의 끝이 현재 행까지 늘어나면서 두 번째 등장부터 표시됩니다. 규칙을 만든 뒤에는 조건부 서식 → 규칙 관리에서 적용 범위가 A2:D300인지 확인하면 좋습니다.
색이 예상과 다를 때 확인할 것
같아 보이는 이름인데 한쪽만 색이 없다면 셀 앞뒤의 공백을 먼저 의심해볼 만합니다. 외부 자료에서 복사한 텍스트에는 눈에 잘 띄지 않는 공백이나 줄바꿈이 섞이기도 합니다. 옆 열에 =TRIM(CLEAN(A2))을 넣어 값을 비교해보고, 필요하면 다듬은 열을 기준으로 중복을 찾으세요. 다만 이 수식으로도 처리되지 않는 특수 공백이 있을 수 있어, 그런 경우에는 원본 문자를 따로 확인해야 합니다.
이미 다른 조건부 서식이 들어간 파일에서는 새 색이 보이지 않거나 예상한 색과 다르게 보일 수 있습니다. 조건부 서식 → 규칙 관리에서 같은 범위에 적용된 규칙을 살펴보세요. 이전 실험에서 만든 규칙만 지우려면 해당 규칙을 선택해 삭제하면 됩니다.
색칠된 항목만 보고 싶다면 데이터에 필터를 켠 뒤 해당 열의 필터 메뉴에서 색 기준 필터를 선택할 수 있습니다. 검토가 끝나기 전에는 ‘중복 제거’를 바로 누르지 않는 편이 안전합니다. 그 기능은 값을 표시하는 것이 아니라 중복된 행을 삭제하므로, 먼저 색으로 확인하고 보관할 행을 결정하는 흐름이 마음 편합니다.
