대량의 고객 명단이나 판매 데이터를 정리하다 보면 같은 데이터가 여러 번 들어가 있는 경우가 흔합니다. 수천 줄을 눈으로 일일이 대조하자니 누락이 걱정되고, 무작정 지우자니 필요한 데이터까지 날아갈까 불안하죠. 원본을 안전하게 지키면서 중복값만 골라내는 방법은 무엇일까요?
한눈에 보기
- 시각적 확인: [홈] > [조건부 서식] > [셀 강조 규칙] > [중복 값]으로 색상 표시
- 영구 삭제: [데이터] > [중복된 항목 제거] 실행 전 반드시 다른 시트에 원본 복사 백업
- 서식 일치 주의: 날짜(3/8/2006 vs Mar 8, 2006) 등 서식이 다르면 중복으로 인식되지 않음
- 함수 추출: Excel 2021·M365는 =UNIQUE(범위) 함수로 원본 보존 추출 가능
삭제 전 눈으로 먼저 확인: 조건부 서식 활용법
데이터를 바로 삭제하지 않고 어디가 겹치는지 먼저 확인하고 싶다면 ‘조건부 서식’을 쓰는 것이 좋습니다. 서류를 버리기 전에 형광펜으로 표시해 두는 것과 같습니다.
확인할 셀 범위를 마우스로 드래그한 뒤 [홈] 탭 > [스타일] 그룹 > [조건부 서식] > [셀 강조 규칙] > [중복 값]을 차례로 누릅니다. 대화상자에서 원하는 강조 색상을 선택하고 확인을 누르면 중복된 셀만 색으로 채워집니다.
단, 피벗 테이블 보고서의 ‘값’ 영역에 포함된 데이터에는 조건부 서식 중복값 강조가 적용되지 않으므로 일반 데이터 표 범위에서 실행해야 합니다.
중복된 항목 영구 제거: 실행 전 백업은 필수
겹치는 행을 완전히 지우려면 엑셀 내장 제거 도구를 사용합니다. 다만 필터와 달리 화면에서 숨기는 것이 아니라 워크시트에서 행을 영구 삭제하므로, 실행 전에 시트 전체를 복사해 다른 시트에 백업해 두는 과정이 안전합니다.
데이터 표 내부의 셀을 하나 클릭한 뒤 [데이터] 탭 > [데이터 도구] 그룹 > [중복된 항목 제거]를 누릅니다. 팝업창에서 중복 여부를 판정할 열(체크박스)을 선택하고 확인을 누르면 일치하는 행이 삭제됩니다.
시트에 부분합이나 윤곽선이 적용되어 있다면 기능이 정상 동작하지 않으므로, 사전에 부분합과 윤곽선을 모두 제거한 상태에서 진행해야 합니다.
왜 같은 값인데 중복으로 안 잡힐까? 서식 판정 기준
엑셀의 중복 비교는 셀에 입력된 원시 데이터뿐 아니라 화면에 보이는 ‘표시 형식(서식)’의 영향도 함께 받습니다. 눈에 보이는 글자 모양이 다르면 엑셀은 서로 다른 고유값으로 인식합니다.
| 원본 1 | 원본 2 | 판정 결과 | 이유 |
|---|---|---|---|
3/8/2006 | Mar 8, 2006 | 고유값 (제거 안 됨) | 날짜는 같으나 표시 서식이 다름 |
1.00 | 1 | 고유값 (제거 안 됨) | 숫자는 같으나 소수점 표시 서식이 다름 |
=2-1 | =3-2 | 중복값 (제거 대상) | 수식은 다르나 결과값과 서식이 같음 |
중복 제거를 돌리기 전에 열 전체의 셀 서식을 동일한 형식(예: 일반 또는 표준 날짜)으로 통일해 두어야 누락 없이 정리됩니다.
원본 보존 대안: 고급 필터와 UNIQUE 함수
원본 데이터를 건드리지 않고 고유한 목록만 따로 뽑아내고 싶다면 다른 기능을 쓸 수 있습니다.
[데이터] 탭 > [정렬 및 필터] > [고급] 메뉴에서 ‘고유 레코드만’ 항목을 선택하면 중복 행을 일시적으로 숨기거나 다른 셀 영역으로 고유값만 복사할 수 있습니다.
Excel 2021 및 Microsoft 365 버전을 사용 중이라면 =UNIQUE(참조범위) 함수를 빈 셀에 입력하는 것만으로 원본 훼손 없이 고유값 목록을 동적 배열로 자동 추출할 수 있습니다.
작업 전 시트를 다른 시트에 복사해 두고, 조건부 서식으로 겹치는 항목을 먼저 확인한 뒤 정리를 시작하세요.