수백 행 데이터에서 중복된 항목을 눈으로 찾아 지우려다 결국 포기한 경험, 한 번쯤 있으실 겁니다. 결론부터 말씀드리면, 엑셀 2007 이상 버전이라면 '데이터' 탭의 '중복된 항목 제거' 버튼 하나로 30초 안에 끝납니다. 그런데 이 버튼이 왜 가끔 엉뚱한 결과를 내는지, 어떤 상황에서 수식을 써야 하는지를 모르면 나중에 낭패를 봅니다.
✅ 버전별·상황별 방법 3가지 완벽 정리
✅ 삭제 전 원본 백업이 유일한 예방책
1중복값이 생기는 이유, 어디서부터 꼬이는 걸까요?
첫 번째 원인은 서로 다른 시스템에서 데이터를 합칠 때입니다. ERP, CRM, 엑셀 시트 세 곳에서 각각 내보낸 파일을 붙여넣으면 동일한 거래처나 이름이 여러 번 들어오는 건 당연한 일입니다.
이때 '눈에 보이는 값이 같다'고 해서 엑셀이 자동으로 제거하지 않습니다.
두 번째 원인은 셀 서식 차이입니다. '001'이라고 입력된 셀과 숫자 1로 인식된 셀은 화면에서 같아 보여도 엑셀 내부에서는 다른 값입니다.
마찬가지로 앞뒤에 보이지 않는 공백이 끼어 있는 경우, '홍길동'과 '홍길동 '(공백 포함)은 서로 다른 값으로 처리됩니다.
세 번째 원인은 대소문자 구분 문제입니다. 엑셀의 중복 제거 기능은 기본적으로 대소문자를 구분하지 않습니다.
'Apple'과 'apple'은 같은 값으로 보고 제거합니다. 하지만 코드나 제품 ID처럼 대소문자가 의미를 가지는 데이터라면 이 점을 반드시 알고 있어야 합니다.
방법 1. 가장 빠른 정석 — 중복된 항목 제거 버튼 (Excel 2007 이상, 소요 30초)
1단계: 데이터 범위 안에 아무 셀이나 클릭합니다. 굳이 전체 선택할 필요 없이 표 안에 커서만 있으면 됩니다.
2단계: 상단 메뉴에서 '데이터' 탭을 클릭합니다.
3단계: '데이터 도구' 그룹에서 '중복된 항목 제거' 버튼을 클릭합니다. 아이콘은 빨간 X 표시가 있는 작은 표 모양입니다.
4단계: 팝업 창이 열리면 중복 여부를 판단할 열을 체크합니다. 예를 들어 이름과 전화번호 두 열이 모두 같을 때만 중복으로 처리하고 싶다면 두 열 모두 체크하면 됩니다.
특정 열 하나만 기준으로 삼고 싶으면 나머지는 체크 해제합니다.
5단계: 확인 버튼을 누르면 '몇 개의 중복 값이 제거되었으며 몇 개의 고유 값이 남아 있습니다'라는 메시지가 표시됩니다.
방법 2. 원본은 유지하고 고유 값만 따로 뽑고 싶을 때 — UNIQUE 함수 (Excel 365·Excel 2021 전용)
Excel 365 또는 Excel 2021을 쓰신다면 UNIQUE 함수로 원본 데이터를 건드리지 않고 다른 위치에 중복 없는 목록을 뽑을 수 있습니다.
빈 셀에 =UNIQUE(A2:A100) 을 입력하면 A2부터 A100 범위에서 중복을 제외한 고유 값 목록이 자동으로 아래로 펼쳐집니다. 데이터가 바뀌면 결과도 자동 갱신됩니다.
Excel 2019 이하, 또는 Microsoft 365가 아닌 영구 라이선스 2019 버전에서는 UNIQUE 함수가 없습니다. 이 경우 방법 3을 사용해야 합니다.
방법 3. 구버전에서 수식으로 중복 없는 목록 만들기 (Excel 2016·2019 이하)
COUNTIF 함수와 보조열을 조합하는 방식입니다. 먼저 B열에 =COUNTIF($A$2:A2,A2) 수식을 입력하고 아래로 복사합니다.
결과값이 1인 행만 고유 첫 등장 값이고 2 이상이면 중복입니다. 이후 자동 필터로 B열이 1인 행만 필터링해 복사하면 중복 없는 목록이 완성됩니다.
이 방법은 손이 많이 가지만 '어느 열에서 몇 번 중복됐는지' 눈으로 확인하면서 작업할 수 있어 데이터 검증 단계에서 유용합니다.
방법 4. 중복값을 지우기 전에 먼저 눈으로 확인하고 싶을 때
삭제 전에 어떤 값이 중복인지 색으로 표시하는 방법이 있습니다. 범위를 선택한 뒤 '홈' 탭 → '조건부 서식' → '셀 강조 규칙' → '중복 값'을 클릭하면 중복된 셀이 모두 빨간색 등으로 강조됩니다.
이 상태에서 내용을 확인한 뒤 실제 삭제는 방법 1로 진행하는 순서가 가장 안전합니다.
그래도 중복이 제거되지 않는다면 이 세 가지를 점검하세요
첫째, 공백 문제입니다. 값이 같아 보이는데 중복으로 인식하지 않는다면 TRIM 함수를 먼저 적용해 앞뒤 공백을 제거해야 합니다.
보조열에 =TRIM(A2)를 입력하고 그 결과를 복사 → 값 붙여넣기로 원본과 교체한 뒤 다시 중복 제거를 시도하세요.
둘째, 숫자와 텍스트 혼재 문제입니다. 같은 숫자인데 한 셀은 텍스트 형식, 다른 셀은 숫자 형식이면 다른 값으로 인식합니다.
셀 왼쪽 상단 초록 삼각형 경고 표시를 확인하고, '숫자로 변환' 옵션을 눌러 형식을 통일한 뒤 다시 시도하세요.
셋째, 병합된 셀이 포함된 경우입니다. 병합 셀이 범위 안에 있으면 중복 제거 기능이 오작동하거나 오류 메시지가 뜹니다.
'홈' 탭 → '병합하고 가운데 맞춤' 드롭다운 → '셀 병합 취소'로 모두 해제한 뒤 진행하세요.
초보가 자주 하는 실수, 이것만 조심하면 됩니다
가장 흔한 실수는 '모든 열 체크' 상태로 중복 제거를 실행하는 겁니다. 예를 들어 이름 열 기준으로만 중복을 제거하고 싶은데 전화번호, 이메일 열까지 모두 체크하면, 이름은 같지만 전화번호가 다른 행은 중복으로 처리하지 않습니다.
팝업 창에서 기준 열을 정확히 선택하는 게 핵심입니다.
두 번째 실수는 헤더(첫 행)를 데이터로 포함시키는 겁니다. 범위를 수동으로 지정할 때 헤더 행까지 끌어 선택하면 엑셀이 헤더를 데이터로 인식해 제거해 버릴 수 있습니다.
팝업 창 우측 상단에 '내 데이터에 머리글 표시' 체크박스가 있는지 반드시 확인하고, 첫 행이 헤더라면 체크 상태로 두세요.
데이터를 정기적으로 합치는 업무라면, 합치기 전 단계에서 각 파일에 TRIM과 UPPER(또는 LOWER) 함수로 공백·대소문자를 먼저 통일해 두는 습관이 중복 오류 자체를 줄이는 가장 확실한 예방책입니다.
답글 남기기