엑셀 중복값 찾기·제거 완전정복 — COUNTIF부터 여러 열 기준까지, 지우기 전에 반드시 확인할 것

지우기 전에 확인부터, 확인 후에 삭제까지

엑셀에서 데이터 정리하다가 "중복 항목 제거" 버튼부터 누르고 후회한 적 있으신가요? 한 번 지우면 되돌리기 전엔 복구가 안 되는데, 정작 뭐가 왜 중복인지 확인도 안 하고 누르는 경우가 많습니다. 지우기 전에 눈으로 확인하는 법부터, 사번+이름처럼 여러 열을 동시에 봐야 할 때 쓰는 함수까지 순서대로 정리했습니다.

1. 지우기 전에 조건부서식으로 먼저 확인하기

가장 먼저 할 일은 삭제가 아니라 강조입니다. 범위를 선택하고 홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값을 누르면 중복된 셀에 색이 입혀집니다. 이 상태에서 필터를 걸어 눈으로 한 번 걸러보고, 진짜 지워도 되는 데이터인지 판단하세요.

방법	용도	데이터 삭제 여부
조건부 서식	중복 눈으로 확인	삭제 안 됨
COUNTIF	중복 개수까지 확인	삭제 안 됨
데이터 > 중복된 항목 제거	실제 삭제	영구 삭제(주의)
UNIQUE 함수	원본 유지하며 고유값만 추출	원본 유지

2. COUNTIF로 몇 번 중복됐는지 세기

강조만으론 부족하고 몇 번 겹쳤는지 숫자로 보고 싶다면 COUNTIF를 씁니다. B열 옆에 아래 수식을 넣고 채우면 각 행이 전체 범위에서 몇 번 나오는지 나옵니다.

=COUNTIF($A$2:$A$100,A2)

결과가 2 이상이면 중복입니다. "처음 등장한 것만 남기고 나머지만 표시"하려면 이렇게 바꿉니다.

=IF(COUNTIF($A$2:A2,A2)>1,"중복(2회째 이후)","")

범위 끝을 고정($A$2)하지 않고 시작만 고정한 뒤 아래로 채우면, 처음 나온 값은 비워두고 두 번째부터 나온 것만 "중복"으로 표시됩니다. 실무에서 원본을 보존한 채 중복분만 골라낼 때 가장 많이 쓰는 패턴입니다.

확인이 끝난 뒤에만 지운다

3. 데이터 탭 중복 제거 — 실제로 지우는 단계

확인이 끝났다면 이제 지웁니다. 데이터 → 중복된 항목 제거를 누르면 어떤 열을 기준으로 중복을 판단할지 체크박스가 뜹니다. 여기서 실수가 가장 많이 나는데, 기준 열을 잘못 선택하면 살려야 할 행까지 날아갑니다.

체크리스트	이유
원본을 다른 시트에 복사해둔다	삭제는 되돌리기 전까지만 복구 가능
기준 열을 정확히 선택한다	이름만 기준하면 동명이인도 삭제될 수 있음
헤더 포함 여부를 확인한다	헤더가 데이터로 인식되면 열 제목이 사라짐

4. 여러 열을 동시에 봐야 할 때 — COUNTIFS

실무에서 진짜 문제는 "이름"만 같은 경우입니다. 동명이인이 있을 수 있으니 사번+이름처럼 두 열이 모두 같을 때만 중복으로 봐야 합니다. 이럴 땐 COUNTIFS를 씁니다.

=IF(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1,"중복 행","")

A열(사번)과 B열(이름)이 동시에 일치하는 행만 중복으로 표시됩니다. 조건부 서식에도 같은 수식을 "수식을 사용하여 서식을 지정할 셀 결정"에 넣으면 행 전체를 색으로 강조할 수 있습니다.

원본은 남기고 결과만 뽑아내기

5. 원본은 그대로, 고유값만 뽑고 싶다면 — UNIQUE

엑셀 365·2021 이상이라면 원본을 건드리지 않고 고유값만 다른 곳에 뽑아낼 수 있습니다.

=UNIQUE(A2:A100)

딱 한 번만 등장한 값만 보고 싶다면 세 번째 인수를 TRUE로 둡니다.

=UNIQUE(A2:A100,,TRUE)

중복 개수까지 한 번에 정리하려면 UNIQUE와 COUNTIF를 함께 씁니다.

=LET(u,UNIQUE(A2:A100),HSTACK(u,COUNTIF(A2:A100,u)))

6. 분명히 같은데 중복으로 안 잡힐 때 — 숨은 공백·서식 차이

같은 값인데도 COUNTIF나 중복 제거가 중복으로 인식하지 못하는 경우가 있습니다. 대부분 눈에 안 보이는 공백이나 서식 차이 때문입니다. 아래 표에서 원인을 먼저 확인하세요.

증상	원인	해결
똑같아 보이는데 중복 안 잡힘	셀 끝에 공백 포함	=TRIM(A2)로 정리 후 재확인
숫자인데 중복 안 잡힘	한쪽은 텍스트, 한쪽은 숫자 서식	=VALUE(A2)로 숫자 통일
대소문자만 다른 영문	COUNTIF는 대소문자 구분 안 함(정상)	구분하려면 EXACT 함수 사용

정리 팁: TRIM으로 공백을 지우고, VALUE로 텍스트형 숫자를 진짜 숫자로 바꾼 다음에 중복 검사를 돌려야 정확한 결과가 나옵니다. 순서를 바꾸면 "분명 지웠는데 또 나온다"는 상황이 반복됩니다.

오늘 바로 확인할 3가지

순서	할 일
1	조건부 서식으로 중복부터 눈으로 확인
2	기준이 되는 열이 하나인지 여러 개인지 판단(COUNTIF vs COUNTIFS)
3	원본 백업 후에만 데이터 탭에서 실제 삭제

중복 제거는 되돌리기가 까다로운 작업입니다. 지우는 버튼을 누르기 전에 위 세 단계만 거쳐도 "잘못 지운 데이터" 사고는 대부분 막을 수 있습니다.