엑셀 조건부서식 중복값 색으로 찾는 방법
거래처 목록을 두 파일에서 합쳤을 때 같은 이름이 여러 줄 보인 적이 있습니다. 눈으로 훑으면 금방 찾을 것 같지만, 200줄만 넘어가도 하나씩 놓치게 되더군요. 이럴 때 엑셀 조건부서식 중복값 규칙을 걸면 반복된 값을 색으로 바로 확인할 수 있습니다.
다만 같은 값이 두 번 나온다는 뜻과 같은 거래가 두 번 입력됐다는 뜻은 다릅니다. 이름이 같아도 전화번호가 다를 수 있고, 같은 주문번호가 다시 나와도 처리 상태가 다를 수 있습니다. 먼저 무엇을 중복으로 볼지 정해두면 색이 칠해진 뒤의 판단이 쉬워집니다.
한 열에서 중복값을 표시하는 방법
A열에 주문번호가 있고 A1은 제목, 실제 데이터가 A2부터 A100까지 있다고 해보겠습니다. A2:A100을 선택한 뒤 홈 → 조건부 서식 → 셀 강조 표시 규칙 → 중복 값으로 들어갑니다. 표시할 색을 고르고 확인을 누르면 끝입니다. Microsoft 지원 안내에도 같은 메뉴 경로가 나와 있습니다.
예를 들어 A2가 1024, A3이 1031, A4가 1024라면 A2와 A4가 함께 강조됩니다. 기본 규칙은 두 번째로 나온 셀만 표시하는 기능이 아닙니다. 같은 값으로 판정된 항목을 모두 보여주므로, 어느 쪽을 남길지 직접 확인하기 좋습니다.
범위를 잡을 때는 제목 셀을 빼고 실제 데이터만 선택하는 편이 깔끔합니다. A열 전체를 선택하면 아래쪽 빈 셀까지 규칙의 적용 범위에 들어가 관리가 번거로워질 수 있습니다. 데이터가 계속 늘어나는 목록이라면 먼저 표로 만든 뒤 데이터 열에 규칙을 적용하는 방법도 편합니다.
두 번째로 나온 값부터 색칠하려면
기본 규칙으로는 첫 번째 입력값도 함께 칠해집니다. 먼저 들어온 값은 두고, 나중에 반복된 값만 표시하고 싶다면 수식을 쓰면 됩니다. A2:A100을 선택하고 홈 → 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정을 고릅니다. 수식 칸에는 다음 내용을 넣습니다.
=AND($A2<>"",COUNTIF($A$2:$A2,$A2)>1)
색을 지정하고 확인하면 같은 주문번호의 두 번째 행부터 표시됩니다. COUNTIF가 A2에서 현재 행까지 값이 몇 번 나왔는지 세기 때문입니다. A2에서는 한 번, 같은 값이 A4에 다시 나오면 그때는 두 번입니다. 앞의 $A2<>"" 조건은 빈 셀이 강조되지 않도록 넣었습니다.
여기서 시작 행 숫자가 중요합니다. 실제 데이터가 A3에서 시작한다면 선택 범위와 수식의 A2를 모두 A3으로 바꿔야 합니다. 범위는 A3부터인데 수식만 A2를 바라보면 색이 한 행씩 어긋나 보일 수 있습니다.
이름과 전화번호가 모두 같은 행 찾기
고객 명단에서는 이름 하나만 같다고 중복 고객으로 단정하기 어렵습니다. A열이 이름, B열이 전화번호라면 두 값을 묶어서 확인하는 편이 낫습니다. A2:B100을 선택해 새 규칙의 수식 칸에 아래 수식을 입력합니다.
=AND($A2<>"",$B2<>"",COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1)
이름과 전화번호가 모두 같은 행에만 색이 들어갑니다. 예를 들어 ‘김민수’가 세 명이어도 전화번호가 일치하는 두 행만 강조됩니다. A열과 B열을 한꺼번에 선택한 뒤 기본 중복 값 규칙을 쓰는 것과는 다릅니다. 기본 규칙은 선택된 셀의 값을 비교하므로, 행 전체가 같은지를 판단하는 용도로 쓰기 어렵습니다.
행 전체를 눈에 띄게 하고 싶다면 적용 범위를 A2:C100처럼 넓히고, 수식은 그대로 A열과 B열을 검사하도록 두면 됩니다. 수식의 $A2와 $B2에서 달러 기호는 열을 고정합니다. 그래서 C열의 처리 상태까지 같은 색으로 칠하면서도 중복 판단은 이름과 전화번호로 할 수 있습니다.
색이 예상과 다를 때 확인할 것
먼저 홈 → 조건부 서식 → 규칙 관리에서 적용 대상 범위를 확인합니다. 데이터를 붙여 넣거나 행을 추가한 뒤 규칙이 목록 일부에만 적용돼 있으면 같은 값인데도 한쪽에 색이 없을 수 있습니다. 수식 규칙이라면 선택 범위의 첫 행과 수식의 행 번호가 맞는지도 같이 봅니다.
화면에는 같아 보이는데 색이 다르면 셀 내용을 확인해야 합니다. 앞뒤에 공백이 들어갔는지, 숫자처럼 보이는 값이 텍스트로 저장됐는지 살펴보면 원인을 찾는 경우가 많습니다. 특히 외부 시스템에서 받은 목록은 복사 과정에서 눈에 잘 띄지 않는 문자가 섞이기도 합니다. 원본을 바로 고치기보다 옆 열에서 값을 확인한 다음 필요한 부분만 손보는 편이 안전합니다.
조건부 서식은 데이터를 지우지 않고 표시만 바꿉니다. 색을 보고 삭제까지 결정했다면 먼저 파일을 복사해 두고, 어떤 열을 기준으로 남길지 확인한 뒤 데이터 → 중복 제거를 사용하면 됩니다. 제 경험상 중복을 찾는 시간보다 ‘정말 같은 기록인가’를 확인하는 시간이 더 중요했습니다. 색은 후보를 좁혀주고, 남길 행은 사람이 판단하는 쪽이 실수를 줄입니다.
