엑셀 중복값 찾기 완벽 정리 — 조건부 서식부터 COUNTIF까지
데이터 정리하다가 뭔가 이상하다 싶었는데 알고 보니 같은 항목이 두 번 들어있던 거, 한 번쯤 경험해봤을 겁니다. 거래처 목록, 고객 명단, 재고 데이터 — 어떤 작업이든 중복이 끼어들면 집계가 꼬이죠. 엑셀 중복값 찾기는 생각보다 방법이 여러 가지라서, 상황에 맞게 골라 쓰면 됩니다.
이 글에서는 클릭 몇 번으로 되는 방법부터 함수를 활용하는 방법까지 단계별로 설명합니다. 엑셀 버전은 Microsoft 365 / Excel 2019 기준이지만, 대부분 2016에서도 동일하게 동작합니다.
조건부 서식으로 중복값 색상 표시하기
가장 빠른 방법입니다. 함수 없이 클릭 몇 번이면 중복된 셀을 색깔로 표시해줍니다. 눈으로 확인하는 게 목적이라면 이걸로 충분합니다.
적용 순서를 설명하겠습니다.
1단계 – 중복을 확인하고 싶은 범위를 선택합니다. 예를 들어 이름이 들어있는 A열 전체라면 A1:A100 같은 식으로요.
2단계 – 상단 메뉴에서 [홈] 탭 → [조건부 서식] → [셀 강조 규칙] → [중복 값]을 클릭합니다.
3단계 – 팝업 창이 뜨면 “중복” 선택 상태에서 원하는 서식(빨간 채우기, 노란 텍스트 등)을 고르고 확인을 누릅니다.
그러면 중복된 값이 있는 셀이 바로 색상으로 표시됩니다. 데이터가 많을 때 한눈에 파악하기 좋죠.
주의 — 조건부 서식의 “중복 값”은 범위 안에서 2회 이상 등장하는 값을 모두 표시합니다. 즉 원본과 복사본 양쪽 모두 색칠됩니다. “복사본만” 표시하려면 아래의 COUNTIF 방법을 써야 합니다.
COUNTIF 함수로 중복 여부 정확히 파악하기
엑셀 중복값 찾기를 함수로 할 때 가장 자주 쓰는 게 COUNTIF입니다. 특정 값이 몇 번 등장하는지 셉니다. 결과가 2 이상이면 중복이 있다는 뜻이죠.
기본 형태는 이렇습니다. =COUNTIF($A$2:$A$100, A2)
이 수식을 B열에 넣고 아래로 복사하면, B열에 각 행이 A열에서 몇 번 등장하는지 숫자로 표시됩니다. 2가 나오면 한 번 중복, 3이면 두 번 중복이죠.
여기서 한 발 더 나아가 IF 함수를 결합하면 “중복 여부”를 텍스트로 표시할 수 있습니다.
=IF(COUNTIF($A$2:$A$100, A2)>1, "중복", "")
이렇게 하면 중복된 행에만 “중복”이라는 텍스트가 표시됩니다. 나중에 필터로 “중복” 텍스트만 걸러내면 중복 항목만 따로 볼 수 있습니다.
첫 번째 항목은 살리고 두 번째부터 중복으로 표시하려면? — =IF(COUNTIF($A$2:A2, A2)>1, "중복", "") 이렇게 범위의 시작은 절대참조($A$2), 끝은 상대참조(A2)로 씁니다. 그러면 위에서 아래로 내려가면서 처음 등장한 항목은 통과, 두 번째부터 “중복”으로 표시됩니다.
저도 이 방법을 처음 배웠을 때 “왜 이렇게 불편하게 만든 거지” 싶었는데, 익숙해지고 나니까 이게 사실 꽤 유연한 방식이더라고요. 조건을 내가 원하는 대로 조절할 수 있으니까요.
중복 제거 기능 — 클릭 한 번에 삭제
중복을 확인하고 나서 아예 없애버리고 싶다면 내장된 “중복 제거” 기능이 편합니다.
사용 방법은 간단합니다. 데이터 범위를 선택하고 [데이터] 탭 → [중복 항목 제거]를 클릭합니다. 어떤 열을 기준으로 중복을 판단할지 선택할 수 있고, 확인을 누르면 첫 번째 항목은 남기고 나머지 중복 행을 삭제합니다.
| 방법 | 특징 | 적합한 상황 |
|---|---|---|
| 조건부 서식 | 색상으로 시각화, 데이터 유지 | 눈으로 확인만 할 때 |
| COUNTIF 함수 | 중복 횟수 및 위치 정확히 파악 | 분석하거나 필터링할 때 |
| 중복 제거 기능 | 중복 행을 즉시 삭제 | 중복 없이 정리된 데이터 필요할 때 |
| 고급 필터 | 원본 유지하고 고유값만 추출 | 원본 손대기 싫을 때 |
중복 제거 기능은 되돌릴 수 없으니 사용 전에 꼭 파일을 복사해두세요. 실수로 원본을 망친 경험이 있는 사람이라면 알겠지만, 저장까지 해버리면 진짜 난감하거든요.
고급 필터로 중복 없는 목록 만들기
원본 데이터는 건드리지 않고 고유값만 별도로 뽑고 싶을 때는 고급 필터가 좋습니다.
방법은 이렇습니다. 데이터 범위를 선택하고 [데이터] 탭 → [고급]을 클릭합니다. 팝업에서 “다른 장소에 복사”를 선택하고, 복사할 위치를 지정한 뒤 “고유 레코드만” 체크박스를 선택하면 됩니다. 확인을 누르면 중복 없이 정리된 목록이 지정한 위치에 생성됩니다.
원본은 그대로 두고 정리된 버전만 따로 만드는 거라, 비교하거나 원본 데이터 보존이 필요한 상황에서 씁니다. 저도 거래처 목록 정리할 때 이 방법을 주로 쓰더라고요.
여러 열을 기준으로 중복 찾기 — 복합 조건
이름 열 하나만이 아니라 이름+이메일 조합이 동일한 행을 찾아야 하는 경우도 있습니다. 이럴 때는 COUNTIFS 함수를 씁니다.
=COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)
A열(이름)과 B열(이메일) 모두 같은 행의 개수를 셉니다. 결과가 2 이상이면 두 조건 모두 동일한 중복 행이 있다는 뜻입니다.
이 방식은 “이름은 같지만 이메일이 다른 동명이인”을 걸러낼 수 있어서, 단일 열로 찾는 것보다 정확도가 높습니다. 마이크로소프트 공식 COUNTIFS 함수 설명도 참고해보시면 도움이 됩니다.
엑셀 중복값 찾기 — 상황별 추천 방법
눈으로만 확인 → 조건부 서식 / 분석·필터링 필요 → COUNTIF(S) / 삭제까지 → 중복 제거 기능 / 원본 보존하고 추출만 → 고급 필터
자주 묻는 질문 FAQ
Q. 대소문자가 다른 경우(Apple, apple)도 중복으로 처리하나요?
A. COUNTIF는 기본적으로 대소문자를 구분하지 않습니다. “Apple”과 “apple”은 같은 값으로 처리돼요. 대소문자를 구분해야 하는 상황이면 EXACT 함수와 SUMPRODUCT를 결합하는 방법을 써야 합니다.
Q. 중복 제거 후 몇 개가 삭제됐는지 확인할 수 있나요?
A. 네, 중복 제거 기능을 실행하면 “XX개의 중복 값이 발견되어 제거되었습니다”라는 팝업이 뜹니다. 단, 취소는 Ctrl+Z로 바로 해야 하고, 파일 저장 후에는 복구가 어렵습니다.
Q. 공백(스페이스)이 포함된 경우도 중복으로 잡힐까요?
A. 아닙니다. “김철수”와 “김철수 “(뒤에 공백 포함)는 다른 값으로 처리됩니다. TRIM 함수로 앞뒤 공백을 제거한 뒤 중복을 찾는 게 좋습니다.
Q. 엑셀 함수 없이 중복을 찾는 가장 빠른 방법은 무엇인가요?
A. 조건부 서식을 이용한 색상 표시가 가장 빠릅니다. 범위 선택 → 조건부 서식 → 중복 값 3단계로 10초면 됩니다.
Q. 숫자 데이터와 텍스트로 저장된 숫자가 섞여 있을 때 중복이 안 잡히는 이유가 뭔가요?
A. 숫자 1과 텍스트 “1”은 엑셀에서 다른 값으로 취급합니다. 이 경우 TEXT 함수나 VALUE 함수로 형식을 통일한 뒤 중복을 찾아야 합니다.
엑셀 중복값 찾기는 방법만 알면 사실 별거 아닌데, 처음엔 어디서 시작해야 할지 모르니까 막막하죠. 저도 처음엔 일일이 눈으로 찾다가 결국 이상한 집계를 제출했던 흑역사가 있습니다. 조건부 서식 하나만 알아도 그런 실수는 확 줄일 수 있습니다.
