명단을 정리하다 보면 같은 이름이 여러 번 들어가 있거나, 거래처 목록에 중복 항목이 섞여 있는 경우가 있습니다. 수십 개라면 눈으로 찾겠지만 수백, 수천 행이라면 불가능하죠.
엑셀 중복값을 다루는 방법은 여러 가지가 있는데, 목적에 따라 골라 써야 합니다. 눈으로 확인만 하고 싶은지, 아예 지우고 싶은지, 중복이 몇 번씩 나오는지 세고 싶은지에 따라 써야 할 기능이 다릅니다.
1. 엑셀 중복값 조건부 서식으로 표시만 하기
가장 안전한 방법입니다. 데이터를 건드리지 않고 중복된 칸에 색만 입혀줍니다. 지우기 전에 뭐가 중복인지 먼저 확인하고 싶을 때 씁니다.
사용법은 간단합니다. 검사할 범위를 선택한 뒤, 홈 탭의 조건부 서식에서 셀 강조 규칙 안에 있는 중복 값 항목을 고르면 됩니다. 색을 지정하고 확인을 누르면 중복된 값이 전부 칠해집니다.
주의할 점은 선택한 범위 안에서만 비교한다는 것입니다. A열과 B열을 한꺼번에 선택하면 두 열을 합쳐서 중복을 찾습니다. 열별로 따로 보고 싶다면 한 열씩 선택해서 적용하셔야 합니다.
2. 엑셀 중복값 바로 제거하기
중복을 실제로 삭제하는 기능입니다. 데이터 탭의 중복된 항목 제거를 누르면 됩니다.
여기서 핵심은 어떤 열을 기준으로 중복을 판단할지 고르는 상자입니다. 기본은 모든 열이 체크되어 있는데, 이러면 모든 칸이 완전히 똑같은 행만 중복으로 봅니다.
예를 들어 고객 목록에서 이름은 같은데 전화번호가 다르면, 전체 열 기준으로는 중복이 아닙니다. 이름만 기준으로 지우고 싶다면 이름 열만 체크하셔야 합니다.
반드시 원본을 복사해둔 뒤에 쓰세요. 제거된 데이터는 되돌리기 외에는 복구할 수 없고, 파일을 저장하고 닫으면 끝입니다. 시트를 통째로 복사해두고 작업하는 습관을 들이시는 게 좋습니다.
3. COUNTIF 함수 — 몇 번씩 나오는지 세기
앞의 두 방법은 중복이다 아니다만 알려줍니다. 몇 번이나 나오는지 알고 싶다면 함수를 써야 합니다.
A열에 데이터가 있다면, 빈 열에 이렇게 입력합니다.
=COUNTIF(A:A, A2)A2 값이 A열 전체에서 몇 번 나오는지 세어줍니다. 1이면 유일한 값이고, 2 이상이면 중복입니다. 수식을 아래로 끌어내리면 전체 행에 적용됩니다.
중복인 것만 표시하고 싶다면 IF를 씌워서 씁니다.
=IF(COUNTIF(A:A, A2)>1, "중복", "")이러면 중복된 행에만 “중복”이라고 표시됩니다. 그다음 필터를 걸어 중복만 모아보면 정리하기 편합니다.
엑셀 중복값 제거, 어느 방법을 써야 하나
상황별로 정리하면 이렇습니다.
- 중복이 어디 있는지 확인만 — 조건부 서식
- 바로 지우고 깔끔한 목록 만들기 — 중복된 항목 제거
- 몇 번씩 나오는지 세거나 조건을 주고 싶을 때 — COUNTIF
실무에선 조건부 서식으로 먼저 확인하고, 내용을 파악한 뒤에 제거하는 순서를 권합니다. 조건부 서식의 다른 활용법은 조건부 서식으로 데이터 한눈에 보기에서 다뤘습니다. 바로 지우면 “이거 진짜 중복이 맞았나” 하는 순간이 꼭 옵니다.
엑셀 중복값 처리 시 자주 겪는 함정
공백 때문에 중복이 안 잡힐 때가 있습니다. 눈에는 같아 보이는데 한쪽 끝에 공백이 들어 있으면 엑셀은 다른 값으로 봅니다. 외부에서 받은 데이터에서 자주 생기는 문제입니다. TRIM 함수로 공백을 먼저 정리하면 해결됩니다.
숫자와 텍스트가 섞여 있는 경우도 주의해야 합니다. 같은 1234라도 하나는 숫자, 하나는 텍스트로 저장되어 있으면 중복으로 인식되지 않습니다. 셀 왼쪽 위에 녹색 삼각형이 있다면 텍스트로 저장된 숫자라는 신호입니다.
마지막으로, 중복을 지우기 전에 정말 지워도 되는 데이터인지 한 번 더 생각해 보세요. 거래 내역처럼 같은 내용이 여러 번 기록되는 게 정상인 자료도 있습니다. 그런 경우 중복 제거는 오히려 데이터를 망가뜨립니다.
실무 상황별 활용
두 목록을 비교해 겹치는 것 찾기
작년 명단과 올해 명단을 비교해 계속 남아 있는 사람을 찾는 상황입니다. 이럴 때도 COUNTIF를 씁니다.
=IF(COUNTIF($D$2:$D$500, A2)>0, "양쪽 모두", "올해만")A열이 올해 명단, D열이 작년 명단이라고 할 때, A2 값이 작년 명단에도 있으면 “양쪽 모두”라고 표시됩니다.
반대로 작년에 있었는데 올해 없는 사람을 찾고 싶다면 기준을 바꿔서 D열 옆에 같은 방식으로 수식을 넣으면 됩니다.
두 열을 합쳐서 중복 판단하기
이름만으로는 동명이인이 걸리고, 전화번호만으로는 가족이 같은 번호를 쓰는 경우가 있습니다. 두 조건을 함께 봐야 정확합니다.
빈 열에 두 값을 합친 열을 만들어 두면 간단해집니다.
=A2&"-"&B2이렇게 만든 열을 기준으로 중복 검사를 하면 두 조건이 모두 같은 경우만 잡힙니다. 가운데 하이픈을 넣는 이유는 값이 뭉개지는 걸 막기 위해서입니다.
중복 중 첫 번째만 남기기
중복된 항목 제거 기능은 어느 것을 남길지 선택할 수 없습니다. 위에서부터 처음 나온 것을 남기고 나머지를 지웁니다.
특정 조건의 행을 남기고 싶다면 먼저 정렬을 하세요. 예를 들어 가장 최근 거래를 남기고 싶다면 날짜 기준 내림차순으로 정렬한 뒤 중복 제거를 실행하면 됩니다.
중복이 안 잡히는 경우 점검표
분명 같은 값인데 중복으로 인식되지 않는다면 아래를 순서대로 확인하세요.
- 앞뒤 공백 — TRIM 함수로 정리
- 중간 공백 개수 — “홍 길동”과 “홍 길동”은 다릅니다
- 데이터 형식 — 숫자와 텍스트가 섞였는지
- 보이지 않는 문자 — 웹에서 복사한 데이터에 특수 공백이 섞이는 경우가 있습니다. CLEAN 함수로 제거할 수 있습니다
- 대소문자 — 엑셀의 중복 검사는 대소문자를 구분하지 않지만, COUNTIF도 마찬가지입니다. 구분이 필요하면 EXACT 함수를 써야 합니다
1번과 3번이 원인의 대부분을 차지합니다. 외부 시스템에서 내려받은 자료라면 작업 전에 TRIM으로 한 번 정리하고 시작하는 습관을 들이시면 좋습니다.
엑셀 중복값 처리는 기능 자체는 어렵지 않지만, 어떤 기준으로 중복을 판단할지 미리 정하는 게 더 중요합니다. 더 자세한 옵션은 마이크로소프트 엑셀 공식 지원 센터에서 확인할 수 있습니다.
엑셀 중복값을 다루는 세 가지 방법 중 어떤 걸 먼저 써야 할지 헷갈린다면, 지우기 전에 항상 조건부 서식으로 먼저 확인하는 순서를 기본값으로 삼으세요.