엑셀 중복 데이터 찾기 제거와 고유값 추출 방법
회원 명부나 주문 내역을 정리하다 보면 같은 고객이 두 번 신청해 겹치거나, 여러 파일에서 복사해 합치는 과정에서 동일한 행이 중복으로 들어가곤 합니다. 이때 급한 마음에 중복 제거 버튼을 바로 눌렀다가, 동명이인이거나 날짜가 다른 별개 주문까지 통째로 날아가 버려 당황한 적이 한 번쯤 있으실 겁니다.
엑셀 중복 데이터는 무작정 지우기보다 ‘먼저 눈으로 확인하고, 원본을 보존한 채 고유값만 뽑거나, 필요한 열 조합을 정확히 지정해 지우는’ 단계적 접근이 중요합니다. 상황에 따라 가장 안전한 4가지 처리 방법과 함정을 정리해 드리겠습니다.
실수로 지워선 안 될 주문 내역까지 한꺼번에 삭제될 때
중복 정리에서 가장 위험한 사고는 ‘이름’ 열 하나만 기준으로 삼아 중복을 제거하는 경우입니다. 서로 다른 날에 같은 고객이 다른 상품을 주문했는데 이름만 보고 겹치는 행을 지워 버리면 매출 집계가 틀어지게 되죠.
또한 원본 시트를 백업하지 않은 채 ‘중복된 항목 제거’를 누르고 파일을 저장해 버리면 나중에 지워진 행이 무엇이었는지 되돌릴 길이 없습니다. 삭제 작업 전에는 반드시 시트 복사본을 만들거나 비파괴 방식(색상 강조·UNIQUE 추출)을 우선 고려해야 합니다.
엑셀 중복 데이터 제거와 추출의 핵심 원리
엑셀 중복 데이터를 다루는 원리는 우편물 분류 작업으로 생각하면 쉽습니다. 첫째는 겹치는 봉투에 빨간 스티커만 먼저 붙여 육안으로 확인하는 방식(조건부 서식·COUNTIF), 둘째는 원본 우편함은 그대로 두고 종류별로 하나씩만 새 상자에 복사해 담는 방식(고급 필터·UNIQUE 함수), 셋째는 먼저 발견한 첫 번째 봉투만 남기고 나머지 똑같은 봉투를 파쇄하는 방식(중복된 항목 제거)입니다.
실제 엑셀 엔진에서 ‘중복된 항목 제거’ 기능은 위에서 아래로 읽으면서 가장 먼저 등장한 첫 번째 행만 보존하고, 그 아래에 나오는 동일한 행을 영구 삭제합니다. 이때 기준 열을 1개만 체크하면 그 열만 같아도 삭제되고, 여러 열을 함께 체크하면 선택한 모든 열 값이 동시에 같은 행만 중복으로 판정합니다.
눈으로 확인·원본 유지 추출·일괄 삭제 단계별 실행법
안전한 순서대로 중복을 찾고 정리하는 실전 단계입니다.
상황별 중복 데이터 확인 및 제거 단계
- 1단계 — 색상으로 눈 확인(조건부 서식): 검사할 열을 드래그한 뒤 [홈] > [조건부 서식] > [셀 강조 규칙] > [중복 값]을 클릭하면 겹치는 셀만 즉시 색이 칠해집니다. 필터에서 ‘색 기준 필터’로 겹친 항목만 모아 검토할 수 있습니다.
- 2단계 — 원본 지키며 고유 목록 뽑기(UNIQUE): Microsoft 365나 Excel 2021 이상이라면 빈 열에
=UNIQUE(A2:B100)을 입력해 원본을 건드리지 않고 중복 없는 목록만 실시간으로 추출합니다. - 3단계 — 구버전 원본 유지 추출(고급 필터): [데이터] > [고급]을 눌러 ‘다른 장소에 복사’를 고르고 아래쪽 ‘고유한 레코드만’ 체크박스를 켠 뒤 복사 위치를 지정해 중복 없는 새 표를 만듭니다.
- 4단계 — 원본 직접 정리(중복된 항목 제거): 먼저 시트를 복제한 뒤, 표 안을 클릭하고 [데이터] > [중복된 항목 제거]를 선택해 ‘내 데이터에 머리글 표시’를 확인하고 기준이 될 열 조합(예: 이름+전화번호)을 체크해 실행합니다.
만약 전체 중복을 다 지우기 전에 “첫 번째로 나온 행은 남기고, 두 번째 이상 등장한 행만 표시”하고 싶다면 보조 열에 =COUNTIF($A$2:A2, A2)처럼 시작점만 F4로 고정한 누적 카운트 수식을 채워 2 이상인 행만 필터로 걸러 삭제하는 방법도 매우 유용합니다.
눈에 똑같아 보이는데 중복으로 안 잡힐 때 대안
분명 똑같은 거래처 이름인데도 중복 제거를 눌렀더니 하나도 지워지지 않고 남아 있는 경우가 있습니다. 이는 글자 맨 뒤에 눈에는 보이지 않는 띄어쓰기 공백이 하나 더 붙어 있거나, 한쪽은 숫자이고 다른 한쪽은 텍스트 서식의 숫자로 저장되어 있기 때문입니다.
이럴 때는 눈으로 대충 훑어보며 수작업으로 지우려 하지 말고, 보조 열에 =TRIM(CLEAN(A2))를 적용해 앞뒤 공백과 줄 바꿈 제어 문자를 먼저 깨끗이 걷어낸 뒤 값만 붙여넣기로 정리하고 다시 중복 검사를 돌려야 합니다. 또한 UNIQUE 함수를 썼는데 #SPILL! 오류가 난다면 결과가 펼쳐질 아래쪽 셀에 다른 글자나 병합 셀이 막고 있다는 뜻이므로 빈 공간을 비워 주면 즉시 해결됩니다.
엑셀 중복 데이터 처리 방법 4가지 비교
원본 데이터 보존 여부와 작업 목적에 따른 네 가지 방법의 차이를 비교했습니다.
| 구분 | 원본 데이터 변경 | 다중 열 기준 조합 | 추천 활용 상황 |
|---|---|---|---|
| 조건부 서식 (중복 값) | 없음 (색상만 표시) | 단일 열 기준에 적합 | 삭제 전 중복 여부만 빠르게 눈으로 점검할 때 |
| COUNTIF 누적 수식 | 없음 (보조 열에 숫자 표시) | COUNTIFS 확장 가능 | 첫 행과 중복 행을 구분해 선별 검토할 때 |
| UNIQUE 함수 / 고급 필터 | 없음 (별도 위치에 고유값 생성) | 가능 (범위 지정) | 원본 거래 내역을 유지하면서 고유 목록만 뽑을 때 |
| 중복된 항목 제거 | 있음 (원본 행 영구 삭제) | 가능 (열 체크박스 조합) | 사본 시트에서 깔끔하게 중복 행을 지우고 싶을 때 |
핵심 정리
엑셀 중복 데이터를 다룰 때 가장 중요한 원칙은 원본을 보호하면서 목적에 맞는 도구를 선택하는 것입니다. 지우기 전에는 조건부 서식이나 COUNTIF로 먼저 확인하고, 원본을 그대로 두어야 하는 집계표라면 UNIQUE 함수나 고급 필터로 고유값만 따로 뽑아 쓰는 편이 안전하죠.
직접 삭제하는 ‘중복된 항목 제거’ 기능을 써야 할 때는 반드시 시트 복사본에서 작업하고, 동명이인이나 다른 거래가 지워지지 않도록 2개 이상의 조합 열을 기준으로 체크하시기 바랍니다. 공백 제거와 시트 백업 습관이 데이터 손실 사고를 막아 줍니다.
재발 방지 체크리스트
재발 방지 체크리스트
- ‘중복된 항목 제거’를 실행하기 전 반드시 원본 시트를 복제해 백업을 남깁니다.
- 단일 열(이름 등)만 고르지 않고 고유 식별자(사번·연락처·주문번호) 조합을 기준으로 지정합니다.
- 중복이 안 잡힐 때는
TRIM함수로 숨은 공백을 먼저 정리한 뒤 다시 검사합니다. - 원본 보존이 필요한 보고서에는
UNIQUE함수나 고급 필터(다른 장소에 복사)를 활용합니다.









