엑셀 중복 데이터 찾는 법 — 지우기 전에 확인부터 (방법 4가지)

"중복된 항목 제거"를 먼저 누르면 안 됩니다. 몇 건이 왜 중복인지 확인하지 않고 지우면, 지워진 게 진짜 중복이었는지 확인할 방법이 없습니다. 순서는 COUNTIF로 세기 → 조건부 서식으로 보기 → 검토 → 그다음 제거입니다. 첨부한 25건짜리 실습 파일로 같이 해 보겠습니다.

실습 데이터 — 25건 중 몇 건이 중복일까

고객 명단 25건입니다. 이메일 기준으로 중복을 찾습니다.

항목	값
전체 행 수	25
중복으로 표시되는 행	5
고유 이메일 개수	22
중복 제거 후 남는 행	22
실제로 지워지는 행	3

"중복 표시 5건"과 "지워지는 3건"이 다른 게 핵심입니다.

같은 이메일이 2번 나오면 두 행 다 "중복"으로 표시되지만, 지워지는 건 1건입니다. 3번 나오면 세 행이 표시되고 2건이 지워집니다. 이 실습 파일은 2회 중복 1건 + 3회 중복 1건이라 표시 5행 / 삭제 3행이 됩니다.

이걸 모르면 "5건 중복이라더니 3건만 지워졌다"고 당황하게 됩니다.

방법 1. COUNTIF로 몇 번 나오는지 세기 (가장 먼저)

보조열을 하나 만들고 이 수식을 넣습니다.

=COUNTIF($C$5:$C$29, C5)

• 결과가 1이면 그 값은 하나뿐
• 2 이상이면 그만큼 반복됨

범위는 $ 로 고정하고, 비교 대상만 상대참조로 두는 게 핵심입니다. 고정하지 않으면 아래로 끌 때 범위가 밀려 결과가 엉망이 됩니다.

바로 옆에 판정 열도 만들어 두면 읽기 편합니다.

=IF(E5>1, "중복", "고유")

요약 숫자 뽑는 수식

전체 현황을 한눈에 보려면 이 셋을 씁니다.

전체 행 수      =COUNTA($C$5:$C$29)
중복 표시 행 수  =SUMPRODUCT((COUNTIF($C$5:$C$29,$C$5:$C$29)>1)*1)
고유 값 개수    =SUMPRODUCT(1/COUNTIF($C$5:$C$29,$C$5:$C$29))

세 번째 수식이 유용합니다. 1/COUNTIF를 전부 더하면 고유 개수가 나옵니다. 같은 값이 3번 나오면 1/3이 세 번 더해져 1이 되는 원리입니다.

빈 칸이 있으면 #DIV/0!가 납니다. 범위 안에 빈 셀이 없어야 합니다.

엑셀 COUNTIF로 중복 횟수를 세고 조건부 서식으로 표시한 화면

방법 2. 조건부 서식으로 눈에 보이게

숫자로 확인했으면 이제 색으로 봅니다.

1. 검사할 열 선택
2. 홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값
3. 색 선택 → 확인

데이터는 전혀 건드리지 않습니다. 색만 입힙니다. 가장 안전한 방법입니다.

행 전체에 색을 칠하려면

기본 기능은 그 열만 칠합니다. 행 전체를 칠하려면 수식 규칙을 씁니다.

1. 표 전체 선택 (머리글 제외)
2. 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정
3. 수식 입력
4. =COUNTIF($C$5:$C$29, $C5)>1

$C5처럼 열만 고정하는 게 핵심입니다. 행 번호에 $를 붙이면 전부 같은 색이 되거나 아무것도 안 칠해집니다.

방법 3. 중복된 항목 제거 (되돌리기 어려움)

확인이 끝났으면 이제 지웁니다.

1. 반드시 파일 복사본을 먼저 저장하세요
2. 표 안 아무 셀 클릭
3. 데이터 → 중복된 항목 제거
4. 기준으로 삼을 열을 선택 (여기가 중요합니다)
5. 확인

"OO개의 중복된 값이 검색되어 제거되었습니다. OO개의 고유한 값이 남아 있습니다"라는 메시지가 뜹니다. 숫자가 예상과 맞는지 꼭 확인하세요.

열 선택이 결과를 완전히 바꿉니다

체크한 열	판정 기준
이메일만	이메일이 같으면 삭제
이름 + 지역	둘 다 같아야 삭제
전체 열 (기본값)	모든 칸이 똑같아야 삭제

기본값은 모든 열이 체크된 상태입니다. 이 경우 한 칸이라도 다르면 중복으로 안 봅니다. 이메일 기준으로 지우고 싶다면 이메일만 체크해야 합니다.

남는 건 항상 위쪽 행입니다. 최신 데이터를 남기고 싶다면 미리 정렬해서 최신이 위로 오게 해 두세요.

되돌리기

Ctrl+Z는 됩니다. 다만 저장 후 닫으면 끝입니다. 그래서 3번 앞에 "복사본 저장"을 둔 것입니다.

방법 4. 고급 필터 — 원본을 지키면서 고유 값만 뽑기

원본은 그대로 두고 고유 값 목록만 따로 만들고 싶을 때 씁니다.

1. 데이터 → 고급 (정렬 및 필터 그룹)
2. 다른 장소에 복사 선택
3. 목록 범위: 원본 / 복사 위치: 빈 셀 지정
4. 동일한 레코드는 하나만 체크
5. 확인

원본이 전혀 손상되지 않습니다. 실무에서는 이 방법이 가장 안전합니다.

UNIQUE 함수 (Microsoft 365 · Excel 2021 이상)

최신 버전이라면 수식 한 줄이면 됩니다.

=UNIQUE(C5:C29)

원본이 바뀌면 결과도 자동으로 따라 바뀝니다.

구버전에서 열면 #NAME?가 뜹니다. 다른 사람에게 보낼 파일이라면 고급 필터를 쓰세요. 이 문제는 엑셀 #NAME? 오류 뜰 때에 정리해 두었습니다.

방법 비교 — 언제 무엇을 쓰나

방법	결과	원본	추천 상황
조건부 서식	색으로 표시	보존	눈으로 검토할 때
COUNTIF 보조열	횟수를 숫자로	보존	몇 건인지 셀 때
중복된 항목 제거	실제 삭제	변경됨	확인이 끝났을 때
고급 필터	다른 곳에 복사	보존	원본을 남겨야 할 때

위 두 개로 확인하고, 아래 두 개로 처리하는 순서입니다.

두 열을 합쳐야 진짜 중복인 경우

이름만 같고 지역이 다르면 동명이인일 수 있습니다. 이럴 땐 보조열에 두 값을 합칩니다.

=B5 & "|" & D5

그리고 이 보조열에 COUNTIF를 겁니다.

=COUNTIF($E$6:$E$30, E6)

구분 기호(|)를 반드시 넣으세요. 없이 붙이면 "김이"+"수서울"과 "김이수"+"서울"이 같은 값이 되어 잘못된 중복이 생깁니다.

첨부 파일의 2_복합키중복 시트에 이 방식을 만들어 두었습니다.

중복인데 안 잡힐 때 — 원인 4가지

눈으로는 똑같은데 COUNTIF가 1로 나온다면 아래를 확인하세요.

원인	확인 방법	해결
앞뒤 공백	=LEN(C5) 로 길이 비교	=TRIM(C5)
숫자 vs 텍스트	왼쪽 정렬이면 텍스트	노란 느낌표 → 숫자로 변환
보이지 않는 특수문자	LEN 값이 이상하게 큼	=TRIM(SUBSTITUTE(C5,CHAR(160)," "))
일부 열만 같음	다른 열도 확인	보조열로 합쳐서 비교

웹이나 시스템에서 내려받은 데이터는 거의 항상 공백 문제가 있습니다. CHAR(160)은 웹에서 흔한 특수 공백이라 TRIM만으로는 안 지워집니다.

대소문자는 구분하지 않습니다. COUNTIF와 "중복된 항목 제거" 모두 ABC와 abc를 같은 값으로 봅니다. 구분해야 한다면 EXACT 함수를 써야 합니다.

실무 순서 정리

1) 파일 복사본 저장
2) COUNTIF로 몇 건인지 확인
3) 조건부 서식으로 눈으로 검토
4) 지울 기준 열 확정
5) 그다음에 중복된 항목 제거

2번과 3번을 건너뛰지 마세요. 명단에서 동명이인을 지워 버리는 사고가 여기서 납니다.

마무리

세 줄로 정리합니다.

• 지우기 전에 COUNTIF로 세세요. 표시 5건과 삭제 3건은 다른 숫자입니다.
• "중복된 항목 제거"는 체크한 열이 결과를 바꿉니다. 기본값은 전체 열입니다.
• 원본을 남겨야 한다면 고급 필터를 쓰세요. 되돌릴 수 없는 삭제보다 안전합니다.

첨부한 실습 파일에 25건 데이터와 네 가지 방법을 전부 만들어 두었습니다. 요약 숫자가 25 / 5 / 22 / 3으로 나오는지 확인해 보세요.

관련 글

• 엑셀 #NAME? 오류 뜰 때 — 원인 6가지와 3초 만에 찾는 법
• 엑셀 정렬했더니 데이터가 어긋났을 때 — 되돌리는 법과 원인 6가지
• 엑셀 숨김행 보기 — 숨긴 행·열 다시 나타내는 법
• VLOOKUP 대신 INDEX MATCH를 써야 하는 이유 (실무 예제로 비교)
• 엑셀 셀 왼쪽 위 초록색 삼각형 없애는 법 (숫자가 텍스트로 저장됨)

참고

• Microsoft 지원 — 중복된 값 찾기 및 제거: https://support.microsoft.com/ko-kr/office/중복-값-필터링-또는-제거-7ccb72df-3766-4cd3-a340-47a20eb9cbd6

메뉴 위치는 엑셀 버전에 따라 조금 다를 수 있습니다. 위 설명은 Microsoft 365와 Excel 2016 이후 기준입니다.