엑셀에서 중복값 찾다가 시간 다 날린 적 있으세요? 수백 행짜리 데이터에서 눈으로 찾는 건 한계가 있고, 막상 함수 쓰려니 어디서부터 시작해야 할지 막막하죠. 상황별로 가장 빠른 방법을 정리해 드립니다.
가장 빠른 방법 — 조건부 서식으로 중복값 색 표시
엑셀 중복값 찾기에서 가장 간단하고 직관적인 방법은 조건부 서식입니다. 함수 하나 쓸 줄 몰라도 메뉴 클릭 몇 번으로 중복값이 색깔로 표시됩니다.
범위 선택
중복값을 찾고 싶은 열 전체 또는 특정 범위를 드래그해서 선택합니다. 예를 들어 A열 전체라면 A 열 머리글 클릭.
조건부 서식 열기
상단 메뉴 “홈” 탭 – “조건부 서식” – “셀 강조 규칙” – “중복 값” 클릭.
서식 지정 후 확인
“중복” 선택, 강조 색상 선택 후 확인. 중복된 값들이 즉시 지정한 색으로 표시됩니다.
이 방법의 단점은 어떤 행이 중복인지 색으로만 알 수 있고, 몇 번 중복인지는 확인이 안 된다는 겁니다. 단순히 “이 열에 겹치는 값이 있다”는 걸 빠르게 확인할 때 씁니다.
COUNTIF 함수로 중복 횟수 세기
조건부 서식으로는 부족하고 중복이 몇 번 있는지 정확히 알고 싶다면 COUNTIF 함수를 씁니다. 엑셀 중복값 찾기에서 가장 많이 쓰이는 함수 중 하나죠.
기본 문법은 =COUNTIF(범위, 찾을 값)입니다. A열에 이름 목록이 있고 A2셀부터 시작한다면, B2셀에 =COUNTIF($A$2:$A$100, A2) 를 입력하면 A2의 값이 A2~A100 범위에 몇 번 나오는지 숫자로 표시됩니다.
이 숫자가 2 이상이면 중복이 있다는 뜻입니다. 범위 앞에 $ 기호를 붙이는 건 절대참조로 고정하는 거라서, 수식을 아래 셀에 복사해도 범위가 바뀌지 않아야 할 때 씁니다. 처음에 $ 기호를 빠뜨려서 이상한 결과가 나오는 경우가 많습니다.
COUNTIF 응용 – 중복 여부 텍스트로 표시
IF와 COUNTIF를 조합하면 더 편합니다. =IF(COUNTIF($A$2:$A$100, A2)>1, “중복”, “”) 이렇게 쓰면 중복인 셀에는 “중복”이라고 표시되고, 아닌 셀은 빈칸입니다. 필터로 “중복” 행만 뽑아서 정리할 때 편하죠.
여러 열 조합으로 중복 찾기 — COUNTIFS 활용
이름과 전화번호 두 가지가 모두 같은 경우만 중복으로 봐야 할 때는 COUNTIF 하나로는 부족합니다. 여러 조건을 동시에 체크하려면 COUNTIFS를 씁니다.
문법은 =COUNTIFS(범위1, 조건1, 범위2, 조건2) 형태입니다. 예를 들어 A열이 이름, B열이 전화번호라면 =COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2) 를 씁니다. 이름도 같고 전화번호도 같은 행이 몇 개인지 세는 거죠.
처음 이걸 알았을 때 왜 진작 몰랐나 싶었습니다. 그전까지는 이름으로만 걸러내다가 동명이인 때문에 데이터를 잘못 처리한 적이 있었거든요. 그때 꽤 일을 다시 해야 했습니다.
엑셀 중복값 제거하는 법 — 찾은 다음 처리
중복값을 찾은 후 하나만 남기고 나머지를 지우려면 엑셀 기본 기능인 “중복된 항목 제거”를 쓰면 됩니다.
- 데이터 탭 – “중복된 항목 제거” 클릭
- 기준으로 삼을 열 체크 (여러 열 동시 선택 가능)
- 확인 누르면 자동으로 중복 행 삭제, 고유값만 남음
단, 이 기능은 첫 번째 값을 남기고 이후 중복을 지우는 방식입니다. 어느 행을 남길지 선택이 안 되니, 특정 행을 보존해야 한다면 먼저 정렬해서 원하는 행이 위로 오게 한 뒤 제거하는 게 낫습니다.
| 방법 | 난이도 | 적합한 상황 |
|---|---|---|
| 조건부 서식 | 쉬움 | 눈으로 위치 파악, 빠른 확인 |
| COUNTIF | 보통 | 중복 횟수 확인, 필터 연계 |
| COUNTIFS | 보통 | 여러 열 조합 중복 탐색 |
| 중복 항목 제거 | 쉬움 | 중복 직접 삭제, 데이터 정리 |
피벗 테이블로 중복값 그룹화 확인
데이터가 많고 중복값이 어느 정도 규모로 있는지 파악하고 싶다면 피벗 테이블이 유용합니다. 엑셀 중복값 찾기에서 피벗까지 생각하는 분은 많지 않지만, 같은 값이 몇 번씩 나오는지 한눈에 보는 데는 피벗이 제일 좋습니다.
삽입 탭 – 피벗 테이블 선택, 확인하고 싶은 열을 “행” 필드에, 값 필드에 “개수”를 넣으면 됩니다. 2 이상인 항목이 중복이 있는 값입니다. ▲ 수백 개 항목에서 중복 규모를 빠르게 파악할 때 피벗이 훨씬 빠릅니다.
Microsoft 엑셀 공식 도움말에서 COUNTIF, COUNTIFS 함수 사용법을 더 자세하게 확인할 수 있습니다.
“엑셀 중복값 찾기는 눈으로 보고 싶으면 조건부 서식, 정확히 세고 싶으면 COUNTIF — 이 두 가지만 알아도 됩니다.”
자주 묻는 질문 FAQ
Q. 조건부 서식으로 표시된 중복값을 한 번에 삭제할 수 있나요?
A. 직접은 안 됩니다. 색 표시 후 필터 기능에서 “색으로 필터”를 쓰면 색 있는 셀만 모아볼 수 있고, 그 상태에서 행을 선택해 삭제하면 됩니다. 아니면 COUNTIF로 중복 여부 열을 만든 후 필터로 처리하는 게 더 편합니다.
Q. COUNTIF에서 대소문자를 구분해서 중복을 찾을 수 있나요?
A. COUNTIF는 기본적으로 대소문자를 구분하지 않습니다. “Apple”과 “apple”을 같은 값으로 처리합니다. 대소문자까지 구분하려면 EXACT 함수를 배열 수식으로 써야 해서 복잡해집니다.
Q. 두 개의 시트 간에 중복값을 찾는 방법이 있나요?
A. COUNTIF 범위에 다른 시트를 참조하면 됩니다. 예를 들어 =COUNTIF(Sheet2!$A:$A, A2) 이렇게 시트 이름을 앞에 붙이면 Sheet2의 A열에서 현재 셀 값을 찾습니다.
Q. 중복 제거 후 원본을 복원할 수 있나요?
A. 중복 항목 제거 기능은 되돌리기(Ctrl+Z)로 바로 취소할 수 있습니다. 단, 파일 저장 후에는 복원이 어려우니 작업 전에 다른 이름으로 원본을 백업해두는 게 안전합니다.
Q. 엑셀 말고 구글 스프레드시트에서도 같은 방법을 쓸 수 있나요?
A. COUNTIF 함수는 구글 스프레드시트에서도 동일하게 작동합니다. 조건부 서식도 비슷한 메뉴 구조로 지원합니다. 중복 항목 제거는 “데이터” 메뉴에서 “중복 항목 삭제”로 찾으면 됩니다.
엑셀 중복값 찾기 하나 때문에 고생하는 분들이 생각보다 많습니다. 저도 처음에는 직접 눈으로 비교하다가 한 시간 넘게 쓴 경험이 있는데, 지금은 COUNTIF 한 줄로 5초면 끝냅니다. 함수가 어렵게 느껴지지만 COUNTIF는 그 중에서도 가장 배우기 쉬운 편이라 한 번만 써보면 금방 익숙해집니다.