엑셀 중복값 찾기와 제거 — 조건부 서식·중복 항목 제거·COUNTIF 비교
엑셀은 어떤 값을 중복으로 볼까?
엑셀은 셀에 표시된 값이 같으면 중복으로 봅니다. 셀에 저장된 값이 같은지는 기준이 아닙니다. Microsoft 문서에는 날짜 예가 나옵니다. 같은 날짜라도 한 셀은 「12/8/2017」, 다른 셀은 「Dec 8, 2017」로 표시되면 서로 다른 값으로 취급됩니다. 반대로 셀 A1에 =2-1, A2에 =3-2가 있고 표시 형식이 같으면 결과값이 같아 중복으로 간주됩니다. (Microsoft 지원 — 중복 값 필터링 또는 제거)
「중복 제거」 기능에서 중복 행은 행의 모든 값이 다른 행의 모든 값과 정확히 일치하는 행입니다. 선택한 열 기준으로 판단하므로, 어느 열을 고르느냐에 따라 결과가 달라집니다.
이 글은 아래 예제 표(A1:D9)를 끝까지 사용합니다. 주문 목록이고, 8개 데이터 행에 중복이 섞여 있습니다.
| 행 | A (주문번호) | B (고객) | C (상품) | D (수량) |
|---|---|---|---|---|
| 1 | 주문번호 | 고객 | 상품 | 수량 |
| 2 | A-101 | 김민수 | 볼펜 | 10 |
| 3 | A-102 | 이서연 | 노트 | 5 |
| 4 | A-103 | 박지후 | 볼펜 | 10 |
| 5 | A-102 | 이서연 | 노트 | 5 |
| 6 | A-104 | 김민수 | 파일 | 3 |
| 7 | A-103 | 박지후 | 노트 | 2 |
| 8 | A-105 | 최유나 | 볼펜 | 10 |
| 9 | A-101 | 김민수 | 볼펜 | 10 |
행 5는 행 3과, 행 9는 행 2와 네 열이 모두 같습니다. 행 7은 주문번호(A-103)만 행 4와 같고 상품·수량이 다릅니다. 이 차이가 이후 결과를 가릅니다.
조건부 서식으로 중복값 색칠하기
중복값을 지우지 않고 눈으로 확인하려면 조건부 서식을 씁니다. Microsoft 문서의 순서는 다음과 같습니다.
- 중복을 확인할 셀을 선택합니다. 예제에서는 A2:A9입니다.
- 홈 → 조건부 서식 → 셀 강조 표시 규칙 → 중복 값을 선택합니다.
- 중복 값에 적용할 서식을 고르고 확인을 누릅니다.
A2:A9에 적용하면 A-101(행 2·9), A-102(행 3·5), A-103(행 4·7)이 칠해져 6칸이 강조됩니다. A-104, A-105는 한 번뿐이라 칠해지지 않습니다.
피벗 테이블 보고서의 값 영역에 있는 중복 항목은 엑셀에서 강조 표시할 수 없다고 문서에 안내되어 있습니다. 또 A2:D9처럼 여러 열을 한꺼번에 선택하면 「볼펜」「10」처럼 열이 다른 같은 값도 모두 칠해지므로, 기준 열 하나만 선택하는 편이 안전합니다.
행 전체를 칠하고 싶을 때
기본 메뉴는 셀 단위로 칠합니다. 행 전체를 칠하려면 조건부 서식의 새 규칙에서 수식으로 서식을 정하는 유형을 고르고(메뉴 이름은 버전에 따라 다를 수 있습니다) A2:D9를 선택한 뒤 아래 수식을 넣습니다.
=COUNTIF($A$2:$A$9,$A2)>1A열 값이 두 번 이상 나오는 행 2·3·4·5·7·9가 칠해집니다. 네 열이 모두 같은 행만 찾으려면 COUNTIFS를 씁니다.
=COUNTIFS($A$2:$A$9,$A2,$B$2:$B$9,$B2,$C$2:$C$9,$C2,$D$2:$D$9,$D2)>1이 수식은 행 2·3·5·9를 칠합니다. 행 4와 7은 상품·수량이 달라 제외됩니다. 사용 중인 엑셀의 지역 설정에 따라 인수 구분자가 쉼표(,) 대신 세미콜론(;)일 수 있습니다.
중복 제거 기능으로 삭제하기
중복 행을 실제로 지우려면 데이터 → 중복 제거를 씁니다. 이 기능은 중복 데이터를 영구 삭제하므로, Microsoft 문서는 실행 전에 원본 데이터를 다른 워크시트로 옮기거나 복사해 두라고 안내합니다.
- 제거할 범위를 선택합니다(예제는 A1:D9). 윤곽선이나 부분합이 있으면 먼저 제거하라고 문서에 안내되어 있습니다.
- 데이터 → 중복 제거를 선택합니다.
- 대화상자에서 중복 판단 기준으로 삼을 열을 선택하고 머리글 관련 확인란이 맞는지 본 뒤 확인합니다.
중복이 제거되면 목록에서 처음 나온 값은 남고 나머지 같은 값이 삭제됩니다. 영향은 선택한 범위(또는 표) 안에만 미치고 범위 밖의 값은 바뀌거나 이동하지 않습니다.
| 선택한 열 (예제 A1:D9) | 삭제되는 행 | 남는 데이터 행 수 |
|---|---|---|
| A열(주문번호)만 | 행 5, 7, 9 | 5 |
| A·B·C·D열 모두 | 행 5, 9 | 6 |
단위: 데이터 행(머리글 제외). 처음 나온 행을 남기는 기준으로 직접 따져 본 결과입니다. A열만 고르면 행 7(A-103, 노트 2)처럼 내용이 다른 주문도 지워집니다. 같은 주문번호가 정말 한 건이어야 하는지 먼저 판단해야 합니다.
COUNTIF로 중복 표시하기
COUNTIF는 범위에서 조건과 같은 칸의 개수를 세는 함수입니다. 사용법은 엑셀 COUNTIF·COUNTIFS 사용법에 정리되어 있습니다. 중복 찾기에서는 범위를 어떻게 잡느냐가 핵심입니다.
| 목적 | 수식 (E2에 입력 후 아래로 복사) | E2:E9 결과 |
|---|---|---|
| 같은 값이 전체에 몇 개인가 | =COUNTIF($A$2:$A$9,A2) | 2, 2, 2, 2, 1, 2, 1, 2 |
| 그 값이 몇 번째인가(누적) | =COUNTIF($A$2:A2,A2) | 1, 1, 1, 2, 1, 2, 1, 2 |
| 두 번째 이후만 「중복」 표시 | =IF(COUNTIF($A$2:A2,A2)>1,"중복","") | 행 5·7·9만 「중복」 |
| 네 열이 모두 같은 행의 누적 횟수 | =COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2,$D$2:D2,D2) | 1, 1, 1, 2, 1, 1, 1, 2 |
결과는 E2부터 E9까지 순서대로 적었습니다. 행 2(A-101)는 전체에 2번 있지만 누적 횟수로는 첫 등장이라 1입니다.
핵심은 $A$2:A2입니다. 시작 칸은 고정하고 끝 칸만 따라 내려가므로, 위쪽에서 이미 나온 값만 셉니다. 그래서 「처음 값은 남기고 두 번째부터 표시」가 됩니다. 표시한 뒤 필터로 「중복」만 보이게 하고 그 행들을 지우면, 중복 제거 기능과 같은 결과를 눈으로 확인하며 얻을 수 있습니다.
다른 목록과 비교할 때
두 목록을 비교할 때도 COUNTIF를 씁니다. F2:F4에 발송이 끝난 주문번호(A-101, A-104, A-109)가 있다고 하면 아래 수식으로 예제 표의 발송 여부를 표시합니다.
=IF(COUNTIF($F$2:$F$4,A2)>0,"발송됨","미발송")행 2·6·9(A-101, A-104, A-101)는 「발송됨」, 나머지 5행은 「미발송」입니다. A-109는 예제 표에 없으므로 어느 행에도 나타나지 않습니다.
세 방법 중 무엇을 고를까?
지우기 전에 확인만 하려면 조건부 서식, 데이터 자체를 정리하려면 중복 제거, 몇 번째 중복인지 열로 남기려면 COUNTIF가 맞습니다.
| 방법 | 데이터 변경 | 알 수 있는 것 | 알맞은 경우 |
|---|---|---|---|
| 조건부 서식 | 없음(색만 입힘) | 중복 여부 | 먼저 눈으로 확인할 때 |
| 중복 제거 | 있음(영구 삭제) | 삭제 결과 행 수 | 정리가 끝난 목록을 만들 때 |
| COUNTIF·COUNTIFS | 없음(수식 열 추가) | 중복 횟수·순번 | 필터링, 다른 목록과 비교, 기록을 남길 때 |
| 고급 필터 | 없음(숨김) 또는 다른 위치에 복사 | 고유 값 목록 | 원본은 두고 고유 목록만 뽑을 때 |
고급 필터는 데이터 → 고급에서 Unique records only(고유 레코드) 확인란을 선택해 씁니다. 같은 자리에서 필터하면 중복이 일시적으로 숨겨지고, 「다른 장소에 복사」를 고르면 고유 값만 지정한 위치로 복사됩니다. 반면 중복 제거는 영구 삭제입니다. 이 문서들은 Excel 2016 이후와 Microsoft 365를 대상으로 표시되어 있습니다.
중복인데 안 잡힐 때 확인할 것
| 증상 | 원인 | 확인 방법 |
|---|---|---|
| 눈에는 같은데 중복으로 안 잡힘 | 글자 끝 공백 등 눈에 안 보이는 차이 | =LEN(A2)로 글자 수 비교. 다르면 =TRIM(A2)로 정리한 열을 만들어 비교 |
| 같은 날짜인데 중복으로 안 잡힘 | 표시 형식이 다름(엑셀은 표시된 값으로 비교) | 두 셀의 표시 형식을 같게 맞춘 뒤 다시 확인. 날짜가 엉뚱하게 바뀌는 경우는 숫자가 날짜로 바뀔 때 막는 법 참고 |
| 수식 결과가 같은데 중복 처리됨 | 수식이 달라도 결과 표시가 같으면 중복으로 간주 | 값만 복사해 붙여 넣은 사본으로 확인 |
| 중복 제거 후 예상보다 많이/적게 지워짐 | 선택한 열 조합이 의도와 다름 | 실행 전에 COUNTIFS 열로 대상 행을 먼저 표시 |
| 피벗 테이블에서 색이 안 입혀짐 | 값 영역은 중복 강조 표시 미지원 | 피벗 원본 데이터 범위에 적용 |
| 중복 제거가 제대로 안 됨 | 윤곽선·부분합이 있음 | 윤곽선·부분합을 먼저 제거(문서 안내) |
위 표의 LEN·TRIM 점검은 문서가 설명한 「표시된 값 기준 비교」 원리를 적용한 확인 방법입니다. 엑셀 버전과 설정에 따라 화면 표기는 다를 수 있습니다.
삭제 전 확인 순서
- 원본 시트를 복사해 둡니다. 중복 제거는 영구 삭제입니다.
- 기준 열 하나에 조건부 서식 「중복 값」을 적용해 어디가 겹치는지 봅니다.
- 같은 주문번호라도 나머지 열이 다른 행(예제의 행 7)이 있는지 COUNTIFS 열로 확인합니다.
- 중복 제거 대화상자에서 기준 열을 의도대로 선택합니다.
- 삭제 후 남은 행 수를 위 표의 예상과 비교합니다. 예제 기준으로 A열만 고르면 5행, 네 열을 모두 고르면 6행입니다.