엑셀 중복값 찾기 3가지 방법 – 조건부 서식부터 COUNTIF 함수까지
엑셀에서 데이터가 수백, 수천 행이 넘어가면 중복된 값을 눈으로 찾는 건 사실상 불가능하죠. 거래처 목록에서 같은 업체가 두 번 들어갔는지, 회원 명단에서 동일 인물이 중복 등록됐는지 – 이런 걸 하나하나 확인하다가는 하루가 끝납니다. 결론부터 말하면 엑셀 중복값 찾기는 조건부 서식이 가장 빠르고, COUNTIF 함수가 가장 정밀하며, 중복 제거 기능으로 마무리하면 됩니다.
조건부 서식으로 중복 시각화하기 – 3초면 끝
엑셀 중복값 찾기 방법 중 가장 직관적인 건 조건부 서식입니다. 중복된 셀에 자동으로 배경색이 칠해지니까 한눈에 어디가 겹치는지 확인할 수 있죠. 함수를 전혀 몰라도 상관없어요.
범위 선택하기
중복을 확인할 열이나 범위를 마우스로 드래그합니다. 열 전체를 선택하려면 열 머리글(A, B, C 등)을 클릭하면 됩니다. 데이터가 있는 범위만 정확하게 잡아주는 게 좋습니다.
조건부 서식 메뉴 들어가기
상단 리본 메뉴에서 [홈] 탭 → [조건부 서식] → [셀 강조 규칙] → [중복 값]을 차례로 클릭합니다. 메뉴 위치가 헷갈리면 검색(Ctrl+F)으로 ‘조건부’를 치셔도 됩니다.
서식 색상 선택 후 확인
팝업 창에서 ‘중복’이 선택된 상태로 원하는 강조 색상을 고르고 확인을 누릅니다. 즉시 중복값에 색이 칠해집니다.
이 방법의 장점은 순수한 속도입니다. 문자 그대로 3초면 끝나요. 다만 단점도 확실한데, 어떤 값이 몇 번 중복됐는지는 보여주지 않습니다. “어딘가에 겹치는 게 있다”는 것까지만 알려주는 거죠. 데이터 전체적인 중복 현황을 파악하는 첫 번째 스크리닝 용도로 쓰면 됩니다.
(*저도 회사에서 엑셀 데이터 정리할 때 조건부 서식부터 걸어보고 시작하는 편인데요. 빨간색으로 칠해진 셀이 화면 가득 뜨면 그날 야근 확정이라는 슬픈 징조이기도 합니다.*)
조건부 서식을 해제하고 싶으면 [홈] → [조건부 서식] → [규칙 관리]에서 해당 규칙을 삭제하면 됩니다. 셀의 서식만 사라지고 데이터는 그대로 남으니 안심하세요.
COUNTIF 함수 – 중복 횟수까지 정밀하게 파악
조건부 서식이 “있다/없다”만 보여준다면, COUNTIF는 “정확히 몇 번 중복됐는지”까지 숫자로 알려줍니다. 실무에서 엑셀 중복값 찾기를 제대로 하려면 이 함수를 반드시 알아야 해요.
기본 수식부터 보겠습니다.
COUNTIF 기본 수식
=COUNTIF($A$1:$A$1000, A2) 형태로 씁니다. A열의 1행부터 1000행까지 중에서 A2 셀의 값이 몇 번 등장하는지 세어줍니다. 결과가 2 이상이면 중복이 존재한다는 뜻이죠.
예를 들어 B열에 이 수식을 넣고 아래로 드래그(또는 더블클릭으로 자동 채우기)하면, 각 행의 값이 A열에서 몇 번 나타나는지 숫자가 표시됩니다. 1이면 유일값, 2면 한 번 중복, 3이면 두 번 중복이에요.
여기서 한 단계 더 나가면 IF 함수와 결합해서 텍스트로 표시할 수도 있습니다. =IF(COUNTIF($A$1:$A$1000, A2)>1, “중복”, “고유”) 이렇게 쓰면 한눈에 어느 행이 중복인지 파악됩니다. 엑셀 중복값 찾기를 하면서 동시에 분류 작업까지 하고 싶을 때 유용한 방식이죠.
그런데 주의할 점이 하나 있어요. 범위를 $A:$A처럼 열 전체로 잡으면 데이터 행 수가 많을 때 계산 속도가 상당히 느려집니다. 실제 데이터가 1,000행이면 $A$1:$A$1000처럼 딱 필요한 만큼만 잡아야 파일이 버벅거리지 않습니다. 데이터가 10만 행이 넘으면 이 차이가 체감될 정도로 크더라고요.
또 하나, COUNTIF는 대소문자를 구분하지 않습니다. “Apple”과 “apple”을 같은 값으로 인식하죠. 영문 데이터에서 대소문자를 구분해야 하는 경우라면 =SUMPRODUCT((EXACT($A$1:$A$1000,A2))*1) 수식을 사용해야 하는데, 이건 좀 복잡한 영역이에요.
중복 제거 기능 – 클릭 몇 번으로 정리 완료
엑셀 중복값 찾기를 해서 어떤 데이터가 겹치는지 확인했다면, 다음 단계는 당연히 정리이겠죠. 엑셀에는 자체 중복 제거 기능이 내장되어 있어서 별도 함수 없이도 일괄 처리가 가능합니다.
방법은 [데이터] 탭 → [중복된 항목 제거]를 클릭하면 팝업이 뜨는데요. 여기서 중복 기준이 될 열을 선택합니다. 이름만 기준으로 할 건지, 이름 + 전화번호를 동시에 기준으로 할 건지 선택할 수 있어요. 확인을 누르면 중복 행이 삭제되고 결과 메시지가 표시됩니다.
작업 전 반드시 백업하세요
중복 제거는 Ctrl+Z로 되돌릴 수 있지만, 파일을 저장하고 닫으면 복구가 불가능합니다. 원본 시트를 복사해두거나 별도 파일로 저장해놓는 습관을 들이세요.
▲ 여러 열을 기준으로 잡을 때 특히 신중해야 합니다. “이름”만 기준으로 걸면 동명이인 데이터까지 날아갈 수 있으니까요. 이름 + 생년월일 + 연락처 정도는 함께 체크하는 게 안전합니다.
완료 후 팝업에 “중복된 값 N개를 발견하여 제거했습니다. 고유한 값 M개가 남았습니다”라는 메시지가 뜨는데, 이 숫자를 반드시 확인하세요. 예상보다 많이 삭제됐다면 기준 설정이 잘못됐거나 데이터 형식에 문제가 있을 수 있습니다.
참고로 마이크로소프트 공식 지원 페이지에서 중복 관련 함수의 상세 사용법을 확인할 수 있으니, 응용 수식을 만들 때 참고하시면 좋겠습니다.
실무 응용 수식 모음 – 이것만 알면 웬만한 건 커버
기본적인 세 가지 방법을 알았으니, 실무에서 바로 쓸 수 있는 응용 수식도 정리합니다. 현장에서는 단순 중복보다 조건을 걸어서 찾는 경우가 훨씬 많더라고요.
엑셀 중복값 찾기 응용 수식
두 열 동시 비교
=COUNTIFS($A:$A,A2,$B:$B,B2) 두 조건을 동시에 만족하는 중복만 카운트
첫 번째만 남기기
=COUNTIF($A$1:A2,A2) 결과 1이면 첫 등장, 2 이상이면 중복 등장분
시트 간 비교
=COUNTIF(Sheet2!$A:$A,A2) 다른 시트의 데이터와 비교하여 중복 여부 확인
COUNTIFS는 COUNTIF의 확장판입니다. 여러 조건을 동시에 걸 수 있어서 “같은 이름이면서 같은 부서”인 경우만 중복으로 잡고 싶을 때 쓰면 됩니다. 실무에서 가장 많이 쓰는 수식 중 하나이죠.
첫 번째만 남기는 수식도 유용해요. =COUNTIF($A$1:A2,A2)에서 범위 시작은 고정($A$1)하고 끝은 현재 행(A2)으로 잡으면, 해당 값이 위에서부터 몇 번째로 나타나는지 카운트합니다. 결과가 1이면 최초 등장이니까 남기고, 2 이상이면 중복이니까 삭제 대상으로 분류하는 식이에요.
| 방법 | 난이도 | 용도 |
|---|---|---|
| 조건부 서식 | 쉬움 | 빠른 시각적 확인 |
| COUNTIF | 중간 | 횟수 파악과 조건 분류 |
| 중복 제거 | 쉬움 | 중복 데이터 일괄 삭제 |
| COUNTIFS | 중상 | 다중 조건 중복 확인 |
흔히 저지르는 실수 3가지 – 결과가 이상하면 이것부터 확인
엑셀 중복값 찾기를 하다 보면 결과가 예상과 다르게 나올 때가 있습니다. 분명 중복인데 못 잡거나, 중복이 아닌 걸 중복이라고 표시하거나. 대부분 아래 세 가지 원인 중 하나예요.
첫 번째 – 보이지 않는 공백. “홍길동”과 “홍길동 “은 눈에는 같아 보이지만 엑셀 내부에서는 완전히 다른 값입니다. 셀 끝에 스페이스가 하나 들어가 있으면 카운트에서 빠져요. 이럴 때는 TRIM 함수를 사용합니다. =TRIM(A2)로 양쪽 공백을 제거한 뒤 비교하면 정확한 결과를 얻을 수 있죠. 실제로 외부에서 복사해온 데이터에 이런 공백이 섞여 있는 경우가 상당히 많습니다.
두 번째 – 데이터 형식 불일치. 겉보기에 같은 “1234”라도 한쪽은 숫자(1234), 한쪽은 텍스트(“1234”)로 저장되어 있으면 다른 값으로 인식됩니다. 셀 왼쪽 정렬이면 텍스트, 오른쪽 정렬이면 숫자인데, 두 형식이 섞여 있으면 중복을 제대로 못 잡아요. [데이터] → [텍스트 나누기] 기능으로 형식을 통일시킬 수 있습니다.
세 번째 – 범위 설정 오류. 헤더 행까지 범위에 포함시키면 “이름”이라는 헤더가 본문의 “이름”과 중복으로 잡힙니다. 또 중간에 빈 행이 섞여 있으면 범위가 끊겨서 아래쪽 데이터가 누락될 수 있어요. 범위는 항상 데이터만 정확하게 잡아주세요.
70%
공백 문제가 원인인 비율
TRIM
공백 제거 필수 함수
Ctrl+Z
실수 시 즉시 되돌리기
왜 이렇게 간단한 기능인데 매번 찾아보게 되는 건지 모르겠지만 – 아마 엑셀을 매일 쓰는 게 아니라 필요할 때만 열다 보니 그런 것 같습니다. 이참에 이 페이지를 북마크해두시면 다음에 시간 아낄 수 있을 거예요.
자주 묻는 질문 FAQ
Q. 여러 열에서 동시에 중복을 찾을 수 있나요?
A. 네, COUNTIFS 함수를 쓰면 됩니다. =COUNTIFS(A:A,A2,B:B,B2) 형태로 여러 열 조건을 동시에 걸 수 있어요. 두 열의 값이 모두 같은 행만 중복으로 카운트합니다.
Q. 조건부 서식의 중복 강조를 해제하려면 어떻게 하나요?
A. [홈] → [조건부 서식] → [규칙 관리]에서 해당 규칙을 선택하고 삭제하면 됩니다. 서식만 사라지고 셀 데이터는 그대로 남으니 걱정하지 않으셔도 돼요.
Q. 대소문자를 구분해서 찾으려면 어떤 함수를 써야 하나요?
A. COUNTIF는 대소문자를 무시합니다. 구분이 필요하면 =SUMPRODUCT((EXACT($A$1:$A$1000,A2))*1) 수식을 사용하세요. EXACT 함수가 대소문자를 정확하게 비교해줍니다.
Q. 중복값만 따로 다른 시트로 복사하고 싶은데 방법이 있나요?
A. COUNTIF 결과가 2 이상인 행만 필터링합니다. 그 후 보이는 셀만 선택(Alt+;)해서 복사하고 다른 시트에 붙여넣기 하면 중복 데이터만 깔끔하게 추출됩니다.
Q. 구글 스프레드시트에서도 같은 방법이 되나요?
A. COUNTIF 함수와 조건부 서식 모두 구글 스프레드시트에서도 동일하게 작동합니다. 메뉴 위치가 약간 다를 뿐 원리는 완전히 같아요.
결국 세 가지만 기억하면 됩니다. 빠르게 확인은 조건부 서식, 정밀한 분석은 COUNTIF, 한 번에 정리는 중복 제거. 이 조합이면 실무에서 중복 데이터 때문에 골치 아플 일은 거의 없을 겁니다.
