엑셀에서 중복 데이터를 처리하는 방법은 크게 두 갈래로 나뉩니다. 조건부 서식으로 중복 값을 눈에 보이게 표시해 검토한 뒤 지울지 결정하는 방식과, '중복된 항목 제거' 기능으로 곧바로 삭제하는 방식입니다. 다만 후자는 데이터가 영구히 사라지므로 반드시 원본을 다른 워크시트나 통합 문서에 복사해 두고 진행해야 합니다. 어떤 열을 '중복 판단의 기준'으로 삼느냐에 따라 남는 행이 달라지기 때문에, 삭제 전에 기준 열을 정하는 것이 사실상 작업의 핵심입니다.
먼저 지울지 말지부터 정해야 하는 이유
중복 데이터가 항상 나쁜 것은 아닙니다. 같은 값이 반복되는 것 자체가 데이터의 성격을 보여주는 경우도 있어서, 무조건 지우기보다 검토가 먼저입니다. 그래서 엑셀도 조건부 서식으로 중복을 강조 표시한 뒤 사용자가 판단하도록 유도합니다.
반면 '중복된 항목 제거' 기능은 이름 그대로 영구 삭제입니다. 실수로 정보가 손실되지 않도록 중복 항목을 삭제하기 전에 원본 데이터를 다른 워크시트로 이동하거나 복사해 두는 것이 권장됩니다. 삭제 직후에는 실행 취소로 되살릴 수 있지만, 그 전에 백업을 만들어 두는 편이 안전합니다.
| 구분 | 조건부 서식 | 중복된 항목 제거 |
|---|---|---|
| 결과 | 강조 표시만 | 영구 삭제 |
| 되돌리기 | 서식 해제 | 실행 취소 가능 |
| 용도 | 검토 후 판단 | 확정된 정리 |
조건부 서식으로 중복 값 찾아 표시하기
단일 열에서 중복을 찾을 때 가장 빠른 방법입니다. 중복 값을 체크할 열 전체를 선택하고, 리본 메뉴에서 [홈] 탭 → [스타일] 그룹 → [조건부 서식] → [셀 강조 규칙] → [중복 값]을 차례로 선택합니다. 대화상자에서 '중복'을 고르고 '진한 빨강 텍스트가 있는 연한 빨강 채우기' 같은 서식을 지정한 뒤 확인을 누르면 해당 셀이 즉시 눈에 띄게 바뀝니다.
여기서 짚고 갈 점이 있습니다. 이 기능은 한 번에 한 열만 검사합니다. 사번과 근무일이 동시에 겹치는 경우처럼 두 개 이상 열의 조합으로 중복을 판별해야 할 때는 '셀 강조 규칙'의 중복 값 표시로는 잡아낼 수 없습니다.
여러 열이 동시에 겹치는 중복은 수식으로 잡기
이럴 때 쓰는 것이 COUNTIFS 함수입니다. 여러 조건을 동시에 만족하는 셀 개수를 세어 주는 함수라, 조건에 해당하는 행이 2개 이상이면 중복으로 판정할 수 있습니다. 사번(B열), 이름(C열), 근무일(D열)이 모두 같은 행을 찾는다면 조건부 서식의 '수식을 사용하여 서식을 지정할 셀 결정' 규칙에 아래 수식을 넣습니다.
=COUNTIFS($B$2:$B$13,$B2,$C$2:$C$13,$C2,$D$2:$D$13,$D2)>1
이렇게 하면 동일 사번·동일 이름·동일 근무일인 행이 모두 강조됩니다. 근무 기록처럼 하루에 한 건만 입력되어야 하는 데이터에서 실수로 두 번 입력된 행을 찾아낼 때 유용합니다.
중복된 항목 제거, 기준 열 선택이 전부다
삭제를 실행할 때는 [데이터] 탭 → [데이터 도구] 그룹 → [중복된 항목 제거]를 누릅니다. 열이 하나만 선택된 상태에서 실행하면 '선택 영역 확장' 여부를 묻는 경고 대화상자가 뜨는데, 여기서 '선택 영역 확장'을 골라야 같은 행의 다른 열 데이터까지 함께 딸려 갑니다.
이어지는 '중복 값 제거' 대화상자에서 어떤 열을 검사할지 체크합니다. 여기서 흔히 오해하는 부분이 있습니다.
- 체크하지 않은 열도 데이터는 함께 삭제됩니다. 예를 들어 Column1과 Column2만 체크하면 그 두 열의 조합이 중복 판단의 '키'가 되고, 중복이 발견되면 Column3을 포함한 행 전체가 지워집니다.
- 상품코드가 같은 행을 지우려 해도, 상품코드·상품명·제조사·단가를 모두 체크하면 네 값이 전부 같은 행만 중복으로 봅니다. 상품코드는 같은데 단가가 다르게 입력된 행은 남는다는 뜻입니다.
- 열이 많을 때는 '모두 선택 취소'를 누른 뒤 필요한 열만 다시 체크하는 편이 빠릅니다.
- 확인을 누르면 제거된 중복 값 수 또는 남은 고유 값 수를 알려주는 메시지가 표시됩니다.
삭제 후에는 목록에서 값의 첫 번째 항목이 남고, 아래쪽에 있던 동일 값이 사라집니다. 상품코드 NP0012와 NP0015가 두 쌍씩 있던 예시에서도 아래에 있던 행이 제거되는 식입니다.
일련번호 열을 넣으면 중복 제거가 안 되는 이유
실무에서 자주 나오는 질문입니다. 삭제 대상을 구별하기 쉽게 No. 열을 일부러 추가해 두면, '선택 영역 확장'으로 그 열까지 포함했을 때 중복 제거가 되지 않는 경우가 생깁니다. 원인은 단순합니다. No. 값은 행마다 다르므로, No.를 판단 기준에 넣는 순간 모든 행이 서로 다른 값이 되어 버립니다.
사번·이름·근무일이 모두 같은 두 행이라도 No.가 3과 4로 다르면 'No, 사번, 이름, 근무일' 조합은 중복이 아니게 됩니다. 그래서 중복 판단에 쓰지 않을 열은 체크에서 빼야 합니다. 참고 자료의 예시에서도 A열(No.)은 실제로 필요 없는 정보인데 삭제 대상을 구별하기 쉽게 일부러 추가한 것이라고 설명합니다.
고유 값 필터링과 중복 제거는 다르다
두 작업 모두 결과적으로 고유 값 목록을 보여주는 것이 목적이라 비슷해 보이지만, 결정적인 차이가 있습니다. 고유 값을 필터링하면 중복 값이 일시적으로만 숨겨집니다. 반면 중복 값을 제거하면 영구적으로 삭제됩니다. 원본을 유지하면서 보기만 하고 싶다면 필터링 쪽을 택해야 합니다.
한 가지 더 주의할 점은 중복 판별이 셀에 저장된 기본 값이 아니라 화면에 나타나는 내용을 기준으로 이뤄진다는 것입니다. 같은 날짜 값이라도 한 셀은 "3/8/2006", 다른 셀은 "Mar 8, 2006"으로 서식이 지정되어 있으면 서로 다른 값으로 취급됩니다.
중복 제거가 막히는 상황들
기능 자체가 작동하지 않는 경우도 있습니다. 개요가 표시되거나 부분합이 설정된 데이터에서는 중복 값을 제거할 수 없으므로, 윤곽선과 부분합을 모두 제거한 뒤 실행해야 합니다. 피벗 테이블 보고서의 값 영역에 있는 필드는 고유 값이나 중복 값을 기준으로 조건부 서식을 지정할 수 없다는 제한도 있습니다.
일본어처럼 더블바이트 문자 집합(DBCS) 인코딩을 쓰는 언어로 Office 작성 언어가 설정된 환경에서는 '중복 값' 서식 규칙을 적용할 때 반자와 전자가 동일하게 처리됩니다. 이 둘을 구분해야 한다면 COUNTIF 수식과 '수식을 사용하여 이 수식에 맞는지 확인' 규칙 유형을 조합하는 방법이 안내되어 있습니다. 예를 들어 A2:A400 범위에서 중복을 식별하려면 =COUNTIF($A$2:$A$400,A2)>1 수식을 조건부 서식에 적용합니다.
Q. 중복 제거 후에 실수로 지운 걸 되살릴 수 있나요?
중복 항목을 제거한 뒤에도 언제든지 실행 취소를 선택하면 데이터를 다시 가져올 수 있습니다. 다만 그 전에 원래 셀 범위나 표를 다른 워크시트나 통합 문서에 복사해 두는 것이 권장됩니다.
Q. 체크하지 않은 열의 데이터는 삭제되지 않나요?
아닙니다. 체크하지 않은 열이라도 중복이 발견되면 해당 행 전체가 삭제됩니다. 체크 여부는 '무엇을 중복으로 볼지'를 정하는 기준일 뿐, 삭제 범위를 정하는 것이 아닙니다.
Q. 두 개 이상 열의 조합으로 중복을 찾으려면 어떻게 하나요?
'셀 강조 규칙'의 중복 값 표시로는 불가능하고, COUNTIFS 함수를 조건부 서식의 수식 규칙으로 적용해야 합니다. 판정에 쓸 열마다 조건을 하나씩 넣고 전체 결과가 1보다 크도록 지정하면 됩니다.