엑셀 중복값 찾기, 방법 3가지로 완벽 정리

엑셀에서 중복 데이터를 찾아야 하는 상황이 생각보다 많죠. 수백 행짜리 명단에서 중복 이름을 걸러내거나, 주문 데이터에서 같은 주문번호가 두 번 들어갔는지 확인하거나, 이메일 목록에서 중복을 제거할 때 같은 경우요. 방법을 모르면 눈으로 일일이 찾아야 하는데, 그건 진짜 고문이죠.

조건부 서식으로 중복값 시각적으로 표시하기

엑셀 중복값 찾기에서 가장 직관적인 방법은 조건부 서식을 이용하는 겁니다. 중복된 셀에 색상을 입혀주는 방식이라 한눈에 파악할 수 있어요.

방법은 간단합니다. 먼저 중복을 확인하려는 셀 범위를 선택합니다. 예를 들어 A1부터 A100까지 이름이 있다면 그 범위를 드래그해서 선택하고요. 그 다음 상단 메뉴에서 홈 탭을 클릭하고, 스타일 그룹에서 조건부 서식 버튼을 클릭합니다. 드롭다운 메뉴에서 셀 강조 규칙 – 중복 값을 선택하면 대화상자가 뜨는데, 여기서 강조 색상을 정하고 확인을 누르면 끝이에요.

이 방법의 장점은 실시간으로 반영된다는 거예요. 나중에 데이터를 추가하거나 수정해도 자동으로 중복 여부가 업데이트됩니다. 단점은 중복값이 몇 개인지 수치로 파악하기 어렵고, 중복을 제거하는 기능까지는 없다는 점이에요.

참고로 조건부 서식은 한 열뿐만 아니라 여러 열을 동시에 선택해도 적용됩니다. 다만 여러 열을 선택하면 각 열 내에서 독립적으로 중복을 찾는 방식이 아니라, 선택된 전체 셀 범위 내에서 중복을 찾으니까 주의해야 해요.

COUNTIF 함수로 중복값 개수 세기

엑셀 중복값 찾기의 두 번째 방법은 COUNTIF 함수입니다. 단순히 표시만 하는 게 아니라 각 값이 몇 번 나오는지 숫자로 확인할 수 있어서 더 세밀한 분석이 가능해요.

기본 사용법은 이렇습니다. B1 셀에 =COUNTIF($A$1:$A$100, A1)라고 입력하면 A1의 값이 A1:A100 범위에서 몇 번 나오는지 숫자로 표시됩니다. 이 수식을 B100까지 복사하면 모든 행의 중복 횟수를 한 번에 확인할 수 있죠.

결과 값이 1이면 고유값, 2 이상이면 중복값입니다. 이걸 필터와 조합하면 더 편리해요. B열에 COUNTIF 결과를 채운 뒤 데이터 탭에서 필터를 걸고, B열 필터를 2 이상으로 설정하면 중복값만 따로 볼 수 있거든요.

• 기본 중복 확인 – =COUNTIF($A$1:$A$100, A1) → 1이면 고유, 2 이상이면 중복
• 중복 여부 표시 – =IF(COUNTIF($A$1:$A$100, A1)>1, “중복”, “고유”)
• 첫 번째만 고유로 표시 – =IF(COUNTIF($A$1:A1, A1)>1, “중복”, “고유”)
• 여러 열 기준 중복 – =COUNTIFS($A$1:$A$100, A1, $B$1:$B$100, B1)

세 번째 수식이 꽤 유용합니다. 절대참조($A$1:A1)에서 끝 행을 상대참조로 두는 트릭인데, 이렇게 하면 처음 나타난 값은 고유로, 두 번째부터는 중복으로 표시해줘요. 실제로 데이터를 정제할 때 처음 나타난 값만 남기고 나머지를 지우는 작업에 씁니다.

중복 제거 기능으로 한 번에 삭제하기

중복값을 찾는 것에서 한 걸음 더 나아가 아예 제거하고 싶다면 데이터 탭의 중복 제거 기능을 쓰면 됩니다. 엑셀 중복값 찾기에서 가장 빠른 처리 방법이에요.

사용법은 데이터 범위 내 아무 셀이나 클릭 – 데이터 탭 – 데이터 도구 그룹에서 중복 제거 버튼 클릭입니다. 대화상자가 뜨면 어느 열을 기준으로 중복을 판단할지 선택할 수 있어요. 예를 들어 이름과 전화번호 두 열을 선택하면, 두 값이 모두 같은 행만 중복으로 처리합니다.

한 가지 주의할 점은 이 기능이 원본 데이터를 직접 수정한다는 겁니다. 되돌리기(Ctrl+Z)는 되지만, 중요한 데이터라면 작업 전에 복사본을 만들어두는 게 좋아요. 저도 이거 한 번 잘못 써서 1,000행짜리 데이터를 날린 경험이 있거든요. 다행히 바로 Ctrl+Z로 복구했지만, 아찔했습니다.

방법	장점	단점	추천 상황
조건부 서식	시각적, 실시간 반영	개수 파악 어려움	빠른 시각 확인
COUNTIF 함수	개수 파악, 세밀한 제어	수식 작성 필요	분석·정제 작업
중복 제거	빠른 삭제, 간단	원본 수정됨	최종 정리 단계

UNIQUE 함수 – 엑셀 365/2021 이상에서만

엑셀 365나 2021 버전을 사용하고 있다면 UNIQUE 함수가 있습니다. 이 함수는 엑셀 중복값 찾기를 넘어서, 중복을 자동으로 제거한 고유값 목록을 별도 셀 범위에 출력해주는 함수예요.

사용법은 간단합니다. =UNIQUE(A1:A100)를 입력하면 A1:A100에서 중복을 제거한 고유값 목록이 자동으로 채워집니다. 원본 데이터는 그대로 유지되면서 별도 열에 정제된 목록이 생기죠. 데이터가 업데이트되면 UNIQUE 결과도 자동으로 바뀝니다.

조금 더 활용하면 =SORT(UNIQUE(A1:A100))처럼 SORT 함수와 결합해서 알파벳·가나다 순으로 정렬된 고유값 목록을 만들 수도 있어요. 예전 버전에서는 상상도 못 했던 편리함이죠. 다만 회사 PC에서 구버전 엑셀을 쓴다면 #NAME? 오류가 나니까 버전 확인을 먼저 하세요.

여러 열 기준 중복 찾기 – 실무에서 자주 쓰는 패턴

실무에서는 한 열이 아니라 여러 열을 조합했을 때 중복인 경우를 찾아야 할 때가 많습니다. 예를 들어 이름이 같아도 생년월일이 다르면 다른 사람이고, 이름과 생년월일이 모두 같아야 중복으로 봐야 하는 상황이요.

이때는 COUNTIFS 함수를 씁니다. =COUNTIFS($A$1:$A$100, A1, $B$1:$B$100, B1)처럼 조건 쌍을 추가하면 두 열 기준 중복을 확인할 수 있어요. 열이 세 개라도 같은 방식으로 조건을 추가하면 됩니다.

또 다른 방법은 보조 열을 만드는 겁니다. C열에 =A1&B1처럼 두 열을 이어 붙인 문자열을 만들고, 그 C열에 COUNTIF나 조건부 서식을 적용하는 거예요. 엑셀이 익숙하지 않은 분들한테는 이 방법이 더 이해하기 쉽더라고요. 원리를 눈으로 확인하면서 진행할 수 있으니까요.

마이크로소프트 공식 지원 페이지 Microsoft 지원에서 각 함수의 상세한 사용법과 예제를 확인할 수 있습니다. 특히 COUNTIFS 함수 페이지에는 다양한 실전 예제가 있어서 참고하기 좋아요.

자주 묻는 질문 FAQ

Q. 엑셀 중복값 찾기를 할 때 대소문자를 구별하나요?
A. COUNTIF 함수와 조건부 서식은 기본적으로 대소문자를 구별하지 않습니다. “Apple”과 “apple”을 같은 값으로 처리해요. 대소문자를 구별하려면 EXACT 함수를 결합한 배열 수식을 써야 합니다.

Q. 공백이 포함된 셀도 중복으로 처리되나요?
A. 네, “홍길동 “(끝에 공백)”과 “홍길동”은 다른 값으로 처리됩니다. 공백 때문에 중복을 못 찾는 경우가 꽤 있어요. TRIM 함수로 먼저 공백을 제거한 후 중복 찾기를 하는 게 좋습니다.

Q. 중복 제거 시 어떤 행이 남고 어떤 행이 삭제되나요?
A. 기본적으로 처음 나타난 행이 남고 나머지가 삭제됩니다. 이 순서를 바꾸려면 먼저 데이터를 정렬해서 남기고 싶은 행이 위로 오게 만든 뒤 중복 제거를 실행하면 됩니다.

Q. 구글 시트에서도 같은 방법을 쓸 수 있나요?
A. 조건부 서식과 COUNTIF는 구글 시트에서도 거의 동일하게 작동합니다. 메뉴 위치가 조금 다를 뿐이에요. 구글 시트는 UNIQUE 함수도 지원하고, 별도의 UNIQUE 버튼이 없어도 수식으로 쓸 수 있습니다.

Q. 중복값을 찾고 나서 하이라이트된 셀만 따로 복사하는 방법이 있나요?
A. 조건부 서식으로 색상이 입혀진 셀만 복사하려면 필터에서 색상으로 필터링하는 방법을 씁니다. 데이터 탭의 필터를 켜고, 해당 열 드롭다운에서 색 기준 필터를 선택하면 됩니다. 이렇게 필터링된 결과만 선택해서 복사하면 돼요.