엑셀 중복 데이터 찾는 법, 왜 매번 헷갈릴까요
엑셀 중복 데이터 찾는 법은 크게 세 가지입니다. 눈으로 대충 훑는 방법, 조건부 서식으로 색을 입혀 시각적으로 찾는 방법, COUNTIF 함수로 정확한 개수를 세는 방법입니다. 실무에서는 이 세 가지를 상황에 맞게 섞어 쓰는 것이 가장 빠릅니다.
명단이 수백, 수천 행을 넘어가면 사람 눈으로 중복을 걸러내는 것은 사실상 불가능합니다. 특히 관공서나 중소기업에서 자주 다루는 거래처 목록, 참석자 명단, 자산 대장 같은 데이터는 담당자가 바뀌거나 여러 부서에서 취합할 때 같은 항목이 두 번, 세 번씩 들어가는 일이 흔합니다. 이번 글에서는 클릭 몇 번으로 끝나는 방법부터, 정확한 검증이 필요할 때 쓰는 함수 방법까지 순서대로 정리했습니다.
조건부 서식으로 중복 값 색칠하기 (가장 빠른 방법)
가장 빠르게 확인하고 싶다면 조건부 서식을 씁니다. 중복된 셀을 자동으로 눈에 띄는 색으로 표시해주기 때문에 별도 수식 없이도 바로 파악할 수 있습니다.
- 중복을 확인할 범위를 마우스로 선택합니다. (예: A2:A200)
- 상단 메뉴에서 홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값을 클릭합니다.
- 대화상자가 뜨면 “중복”이 선택된 상태로 두고, 원하는 서식(예: 진한 빨강 텍스트가 있는 연한 빨강 채우기)을 고른 뒤 확인을 누릅니다.
이렇게 하면 같은 값을 가진 셀들이 모두 같은 색으로 표시됩니다. 이 방법은 데이터를 지우거나 바꾸지 않고 눈으로만 확인할 때 유용합니다. 다만 몇 번 중복되었는지 정확한 숫자를 알려주지는 않기 때문에, 정량적인 확인이 필요하면 다음 방법을 함께 씁니다.
조건부 서식을 활용한 다른 데이터 검증 방법은 엑셀 조건부 서식으로 데이터 이상치·중복 한눈에 찾기에서 더 자세히 다루고 있습니다.
COUNTIF 함수로 중복 횟수 정확히 세기
색으로 확인했다면, 이제 그 값이 정확히 몇 번 중복되었는지 세어보는 단계입니다. COUNTIF 함수 하나로 충분합니다.
=COUNTIF($A$2:$A$200, A2)이 수식을 B2 셀에 넣고 아래로 채우면, 각 행의 값이 전체 범위 안에서 몇 번 나오는지 숫자로 보여줍니다. 결과가 1이면 유일한 값, 2 이상이면 중복된 값입니다.
거래처명처럼 텍스트가 완전히 똑같지 않아도 사실상 같은 값인 경우(예: “㈜픽셀앤코드”와 “픽셀앤코드”)도 있으니, 이런 경우에는 TRIM이나 정규표현식 방식의 정리가 먼저 필요할 수 있습니다. 텍스트를 정리하는 규칙이 궁금하다면 비전공자를 위한 정규표현식 입문을 참고하시면 도움이 됩니다.
중복 여부만 O, X로 표시하고 싶다면 IF와 함께 조합합니다.
=IF(COUNTIF($A$2:$A$200, A2)>1, "중복", "")이렇게 하면 중복된 행에만 “중복”이라는 표시가 붙어서, 필터 기능으로 해당 행만 따로 걸러낼 수 있습니다.
여러 열을 기준으로 중복을 찾고 싶을 때
이름만으로는 중복을 판단하기 어려운 경우도 많습니다. 예를 들어 이름과 전화번호가 모두 같아야 진짜 중복이라고 볼 수 있는 명단이라면, COUNTIF 대신 COUNTIFS를 씁니다.
=COUNTIFS($A$2:$A$200, A2, $B$2:$B$200, B2)이 수식은 A열(이름)과 B열(전화번호)이 동시에 같은 경우에만 개수를 셉니다. 조건을 하나 더 추가하고 싶다면 범위와 조건 쌍을 계속 이어 붙이면 됩니다.
중복 제거까지 한 번에 끝내는 법
찾는 것에서 끝내지 않고 아예 중복 행을 지우고 싶다면 엑셀 기본 기능인 “중복된 항목 제거”를 쓰면 됩니다.
- 데이터 범위를 선택합니다.
- 데이터 탭 → 중복된 항목 제거를 클릭합니다.
- 어떤 열을 기준으로 중복을 판단할지 체크박스로 선택하고 확인을 누릅니다.
다만 이 기능은 되돌리기가 어려운 경우도 있으니, 실행 전에 반드시 원본 파일을 복사해두는 것을 권장합니다. 특히 여러 사람이 같은 파일을 공유해서 쓰는 환경이라면 실수로 필요한 데이터까지 지워질 위험이 있습니다. 공유 파일에서 자주 발생하는 사고 유형은 엑셀 파일 여러 명이 같이 쓸 때, 덮어쓰기 사고 막는 법에서 다루고 있으니 함께 참고하시면 좋습니다.
수천 행이 넘는 데이터를 정기적으로 취합하고 정리해야 한다면, 매번 엑셀에서 수동으로 확인하는 대신 파이썬으로 자동화하는 방법도 고려할 수 있습니다. pandas의 duplicated 함수 하나면 조건부 서식보다 훨씬 빠르게 대용량 데이터의 중복을 찾아낼 수 있는데, 관련 내용은 엑셀 반복 업무, 파이썬으로 10분 만에 끝내기에서 확인하실 수 있습니다.
실무에서 자주 하는 실수
중복 값을 찾을 때 가장 많이 하는 실수는 공백입니다. “홍길동”과 “홍길동 ” (뒤에 공백 하나)은 엑셀이 서로 다른 값으로 인식하기 때문에 중복으로 잡히지 않습니다. 이런 경우에는 TRIM 함수로 먼저 공백을 정리한 뒤 중복을 확인해야 정확한 결과를 얻을 수 있습니다.
또 하나는 대소문자나 전각·반각 문자 차이입니다. 영문 데이터나 코드 값을 다룰 때는 이런 미세한 차이 때문에 실제로는 같은 값인데 중복으로 잡히지 않는 경우가 있으니, 데이터를 취합한 직후에는 형식을 통일하는 정리 작업을 먼저 거치는 것이 안전합니다.
중복 데이터 문제는 결국 데이터가 여러 곳에서 모일 때 생기는 문제이기도 합니다. 명단이나 거래처 정보를 여러 부서, 여러 담당자가 각자 관리하는 구조라면 애초에 중복이 생기지 않도록 입력 규칙을 정하는 것도 방법입니다. 픽셀앤코드는 이런 반복적인 데이터 정리와 검증 업무를 자동화 스크립트로 구축하는 작업을 지원하고 있으며, 매번 수작업으로 중복을 확인하는 데 시간이 많이 든다면 업무 환경에 맞는 자동화 방안을 함께 검토해 드릴 수 있습니다.