엑셀 표에서 중복값만 골라내는 실무 팁

주문 목록이나 고객 명단을 정리하다 보면 같은 이메일이나 전화번호가 두 번 들어가 있는 걸 뒤늦게 발견할 때가 있다. 발송을 두 번 하거나, 집계가 어긋나는 원인이 대부분 이런 중복값이다. 나는 실무에서 상황에 따라 COUNTIF, 조건부 서식, 고급 필터를 섞어 썼는데, 막상 정리해보니 언제 뭘 써야 하는지 기준이 헷갈릴 수 있어서 하나씩 짚어본다.

아래 예시로 계속 설명한다. 주문번호(A), 고객명(B), 이메일(C)이 있는 표에서 이메일이 중복된 행을 찾는 상황이다.

1001행과 1003행의 이메일이 mj@test.com으로 같다. 이걸 찾는 방법은 목적에 따라 다르다.

빠르게 눈으로 확인만 하고 싶을 때: 조건부 서식

C2:C5를 선택하고 홈 탭 → 조건부 서식 → 셀 강조 규칙 → 중복 값을 누르면, mj@test.com이 들어간 두 셀이 자동으로 색칠된다. 표를 건드리지 않고 눈으로만 확인하고 싶을 때 가장 빠르다. 다만 몇 번 중복됐는지, 어떤 행끼리 겹치는지는 색만 봐서는 알기 어렵다.

몇 번 중복됐는지 세고 싶을 때: COUNTIF

D2에 =COUNTIF($C$2:$C$5,C2)를 넣고 아래로 채우면 각 이메일이 표 전체에 몇 번 나오는지 숫자로 나온다. mj@test.com이 있는 두 행은 2가 찍히고 나머지는 1이 찍힌다. 여기에 =COUNTIF($C$2:$C$5,C2)>1로 바꾸면 TRUE/FALSE로 바로 중복 여부만 걸러낼 수 있다. 나는 이후 이 D열을 자동필터로 TRUE만 남기는 식으로 자주 썼다.

여기서 $C$2:$C$5처럼 달러 기호($)가 붙은 건 “절대참조”다. 이 수식을 D2에서 D5까지 아래로 채우기 하면, 뒤쪽의 C2 부분(비교 대상)은 C3, C4, C5로 한 칸씩 같이 내려가야 하지만, 앞쪽의 검색 범위 $C$2:$C$5는 항상 표 전체를 가리켜야 하므로 움직이면 안 된다. 달러 기호를 붙이지 않으면 채우기 할 때 범위도 같이 밀려버려서 엉뚱한 셀을 세게 된다. 달러 기호가 있는 부분은 고정, 없는 부분은 채우는 방향대로 같이 움직인다고 기억하면 된다.

고객명과 이메일이 둘 다 같아야 진짜 중복으로 볼 때처럼 조건이 두 개 이상이면 COUNTIF 대신 =COUNTIFS($B$2:$B$5,B2,$C$2:$C$5,C2)를 쓴다. 이메일은 같은데 고객명이 다른 경우(가족이 같은 이메일을 쓰는 경우 등)까지 중복으로 걸러내고 싶지 않을 때 유용하다.

중복을 뺀 목록만 뽑고 싶을 때: 고급 필터

데이터 탭 → 정렬 및 필터 → 고급을 누르고, “다른 장소에 복사” 옵션을 선택한 뒤 “동일한 레코드는 하나만” 체크박스를 켜면 중복을 뺀 목록이 지정한 위치에 복사된다. 원본 표는 그대로 두고 결과만 다른 곳에 뽑아낼 수 있어서, 원본을 건드리기 부담스러울 때 이 방법을 쓴다. 참고로 데이터 탭의 “중복된 항목 제거” 버튼은 원본 표에서 중복 행을 바로 삭제해버리므로, 원본을 보존해야 하는 상황이라면 고급 필터 쪽이 안전하다.

엑셀 버전에 따라 달라지는 것

여기까지 소개한 조건부 서식, COUNTIF, COUNTIFS, 고급 필터는 오래된 버전인 엑셀 2016에서도 전부 그대로 쓸 수 있다. 반면 최근 구독형인 마이크로소프트 365나 엑셀 2021 이상에서는 =UNIQUE(C2:C5) 함수 하나로 중복을 뺀 목록을 즉시 뽑아낼 수 있다. 수식 하나만 입력하면 결과가 자동으로 아래 셀까지 펼쳐지는 방식이라 별도 메뉴를 거칠 필요가 없다. 다만 이 함수는 2016이나 2019 버전에는 없으므로, 회사 PC가 구버전이라면 UNIQUE 대신 앞서 소개한 고급 필터 방법을 쓰면 된다.

정리

중복만 눈으로 확인하려면 조건부 서식, 몇 번 겹쳤는지 세거나 조건을 걸어 걸러내려면 COUNTIF·COUNTIFS, 원본은 그대로 두고 중복 없는 목록만 따로 뽑으려면 고급 필터를 쓰면 된다. 365를 쓰고 있다면 마지막 상황에서는 UNIQUE 함수 하나로 끝나니 그쪽을 먼저 써보는 걸 추천한다.