엑셀 중복값 찾기 방법 – 조건부 서식부터 함수까지
엑셀 파일을 열었더니 수백 행짜리 데이터에 중복이 섞여 있던 경험, 한 번쯤 있으시죠. 거래처 목록인데 같은 회사가 두 번 들어가 있거나, 이메일 수신자 명단에 동일인이 여럿 있거나. 그냥 눈으로 훑다가는 결국 놓치게 됩니다.
엑셀 중복값 찾기는 생각보다 방법이 여러 갈래인데, 상황에 따라 맞는 도구가 달라서 한 번 정리해두면 두고두고 씁니다.
조건부 서식으로 중복값 한눈에 표시하기
가장 직관적인 방법은 조건부 서식입니다. 셀에 색을 입혀 시각적으로 바로 확인할 수 있어서, 데이터를 수정하면서 실시간으로 체크할 때 편하더라고요.
순서는 이렇습니다. 중복을 확인하려는 범위를 선택하고, 상단 메뉴에서 [홈] → [조건부 서식] → [셀 강조 규칙] → [중복 값]을 클릭합니다. 그러면 바로 색상 선택 창이 뜨고, 확인을 누르면 중복된 셀이 즉시 강조되죠.
단, 이 방법은 어디가 중복인지 보여줄 뿐, 목록을 추출하거나 자동으로 삭제하진 않습니다. 눈으로 확인하고 직접 처리해야 합니다.
① 범위 선택 (예: A2:A500)
② 홈 탭 → 조건부 서식 → 셀 강조 규칙
③ “중복 값” 클릭
④ 색상 선택 후 확인
중복이 있으면 선택한 색으로 바로 강조됩니다. 범위를 여러 열로 넓히면 각 열 내 중복만 체크하므로, 행 전체 기준 중복을 찾으려면 함수 방식이 낫습니다.
COUNTIF 함수로 중복 개수 세기
함수 방식이 훨씬 유연합니다. COUNTIF는 특정 값이 범위 안에 몇 번 등장하는지 세는 함수인데, 이걸 활용하면 중복 여부를 숫자로 확인할 수 있습니다.
B열에 아래 수식을 넣어보세요.
=COUNTIF($A$2:$A$500,A2)이 수식은 A2 값이 A2:A500 범위에서 몇 번 나오는지 반환합니다. 결과가 2 이상이면 중복이죠. 그런 다음 B열 기준으로 필터를 걸어 “2 이상”만 추출하면, 중복 항목만 골라볼 수 있습니다.
행 전체가 같은 경우를 찾으려면 COUNTIFS를 씁니다. 열이 A, B, C라면 이렇게 됩니다.
=COUNTIFS($A$2:$A$500,A2,$B$2:$B$500,B2,$C$2:$C$500,C2)세 열이 모두 일치하는 행만 중복으로 잡아줍니다. 거래처명 + 담당자명 + 연락처가 모두 같은 경우를 찾을 때 써보니, 생각보다 꽤 많이 걸리더라고요.
중복 제거 기능 – 원클릭 삭제
이미 중복이 있다는 걸 알고, 그냥 지워버리면 되는 상황이라면 중복 제거 기능을 씁니다. 데이터를 분석하는 게 아니라 정리하는 용도죠.
[데이터] 탭 → [중복 항목 제거]를 클릭하면, 어느 열을 기준으로 중복을 제거할지 선택하는 창이 뜹니다. 원하는 열만 체크하고 확인을 누르면 두 번째 이후 중복 행이 자동으로 삭제됩니다.
– 원본 데이터가 사라지므로 반드시 백업 후 사용
– 첫 번째로 나온 행만 남기고 나머지를 지우는 방식
– 대소문자·공백은 같은 값으로 처리하지 않을 수 있어 전처리 필요
– 실행 취소(Ctrl+Z)로 되돌릴 수 있지만, 저장 후에는 불가
VLOOKUP / MATCH로 다른 시트와 비교하기
시트 두 개를 비교해서 중복을 찾아야 할 때가 있습니다. 예를 들어 신규 고객 명단과 기존 고객 명단을 맞춰볼 때요. 이럴 때는 VLOOKUP이나 MATCH가 잘 맞습니다.
Sheet1의 A열 값이 Sheet2의 A열에 있는지 확인하려면 이렇게 씁니다.
=IFERROR(MATCH(A2,Sheet2!$A:$A,0),"없음")일치하는 행 번호를 반환하고, 없으면 “없음”을 표시합니다. 숫자가 뜨면 Sheet2에 존재하는 값, “없음”이 뜨면 신규 항목이죠.
VLOOKUP 방식도 비슷합니다. 다만 MATCH는 위치를 반환하고, VLOOKUP은 해당 행의 특정 열 값을 가져온다는 차이가 있습니다. 단순 존재 여부 확인이면 MATCH가 더 가볍습니다.
| 방법 | 용도 | 결과 형태 | 삭제 가능 |
|---|---|---|---|
| 조건부 서식 | 시각적 확인 | 색상 강조 | 수동 |
| COUNTIF | 개수 확인·추출 | 숫자(필터 활용) | 필터 후 수동 |
| 중복 제거 | 즉시 정리 | 행 자동 삭제 | 자동 |
| MATCH/VLOOKUP | 시트 간 비교 | 위치·값 반환 | 수동 |
고급 활용 – 피벗 테이블과 UNIQUE 함수
엑셀 365 또는 2021 이상 버전을 쓰고 있다면 UNIQUE 함수가 있습니다. 범위를 입력하면 중복을 제거한 고유 목록을 자동으로 뽑아주죠.
=UNIQUE(A2:A500)결과가 아래 셀로 자동 확장(스필)되어 채워집니다. 원본은 그대로 두고 고유 목록만 별도로 뽑을 때 아주 편리합니다. 단, 구버전 엑셀에는 이 함수가 없어요.
피벗 테이블도 의외로 유용합니다. 특정 열을 행 영역에 넣으면 자동으로 고유 값 목록이 만들어지고, 각 값의 등장 횟수도 바로 집계됩니다. 데이터 정제와 분석을 동시에 해야 할 때 피벗이 꽤 깔끔하더라고요.
서식
제거
자주 하는 실수와 주의사항
엑셀 중복값 찾기에서 빠지기 쉬운 함정이 있습니다. 가장 흔한 건 공백 문자 차이입니다. “홍길동”과 “홍길동 “은 눈으로 보면 같지만 엑셀은 다른 값으로 인식합니다. TRIM 함수로 앞뒤 공백을 제거한 뒤 비교하는 습관을 들여야 하죠.
대소문자도 마찬가지입니다. COUNTIF는 기본적으로 대소문자를 구분하지 않지만, EXACT 함수는 구분합니다. 영문 데이터라면 어떤 함수를 쓰느냐에 따라 결과가 달라질 수 있습니다.
셀 형식도 체크해야 합니다. 숫자 “001”과 텍스트 “001”은 다르게 취급될 수 있어요. 특히 코드 번호나 우편번호처럼 앞자리 0이 들어가는 데이터는 서식 통일이 선행되어야 합니다.
마이크로소프트 공식 지원 페이지에서 중복 값 필터링 또는 제거 방법을 공식 문서로도 확인해볼 수 있습니다.
자주 묻는 질문 FAQ
Q. 조건부 서식으로 중복을 강조했는데, 나중에 지우려면 어떻게 하나요?
A. [홈] → [조건부 서식] → [규칙 지우기] → [선택한 셀의 규칙 지우기]를 선택하면 됩니다. 데이터는 그대로 남고 색상 강조만 제거됩니다.
Q. COUNTIF로 중복을 확인한 뒤 중복 행만 한꺼번에 삭제할 수 있나요?
A. 네. COUNTIF 결과가 2 이상인 행에 필터를 적용하고, 필터된 행 전체를 선택 후 삭제하면 됩니다. 다만 첫 번째 등장한 행도 함께 지워지므로 원하는 결과와 다를 수 있으니 주의하세요.
Q. 여러 열을 동시에 기준으로 중복 찾기가 가능한가요?
A. 가능합니다. COUNTIFS 함수를 쓰거나, 중복 제거 기능에서 여러 열을 체크하면 됩니다. 예를 들어 이름과 전화번호가 모두 같을 때만 중복으로 처리하고 싶다면 두 열 모두 선택하면 되죠.
Q. 엑셀이 아닌 구글 스프레드시트에서도 같은 방법을 쓸 수 있나요?
A. 조건부 서식과 COUNTIF는 구글 스프레드시트에서도 거의 동일하게 동작합니다. UNIQUE 함수도 지원하고요. 다만 메뉴 위치는 조금 다르고, “중복 제거” 기능은 [데이터] 탭에서 찾을 수 있습니다.
Q. 수천 행짜리 데이터에서 중복을 찾으면 엑셀이 느려지는데 해결 방법이 있나요?
A. COUNTIF 수식을 전체 열에 걸지 말고, 실제 데이터가 있는 범위만 정확히 지정하세요. $A:$A 대신 $A$2:$A$5000처럼 범위를 한정하면 계산 속도가 눈에 띄게 빨라집니다.
