회원 명단을 정리하다 보면 같은 사람이 두세 번씩 들어가 있거나, 주문 내역에 똑같은 건이 중복으로 잡히는 일이 자주 생기죠. 그런데 "중복 처리"라고 뭉뚱그려 말하지만, 실제로 하고 싶은 일은 상황마다 다릅니다. 어떤 때는 그냥 어디가 겹치는지 눈으로 보고 싶고, 어떤 때는 몇 번씩 겹쳤는지 개수를 세고 싶고, 또 어떤 때는 중복을 아예 지워 버리고 싶어요. 이 세 가지는 방법이 완전히 다릅니다. 그래서 이번 글은 목적별로 나눠서 정리했습니다. 자기 상황에 맞는 항목부터 읽으셔도 됩니다.
방법 1. 조건부 서식 — 중복을 눈에 띄게 표시만
지우기 전에 "일단 어디가 겹치는지 색으로 좀 보자" 싶을 때 가장 빠른 방법입니다. 데이터를 건드리지 않고 색칠만 하기 때문에 부담이 없어요.
순서는 이렇습니다.
- 중복을 확인할 셀 범위를 마우스로 드래그해서 선택합니다.
- 상단 리본에서 홈 탭을 클릭합니다.
- 조건부 서식 > 셀 강조 규칙 > 중복 값을 차례로 선택합니다.
- 작은 창이 뜨면 강조할 색을 고르고 확인을 누릅니다.
그러면 값이 두 번 이상 나온 셀에 전부 색이 칠해집니다. 저는 정리 작업을 시작하기 전에 항상 이걸 먼저 켜 놓고 전체 상황을 눈으로 확인하는 편이에요. 다만 여기서 끝나면 색만 칠해질 뿐 데이터가 삭제되지는 않는다는 점을 꼭 기억하세요. 말 그대로 "찾기 전용"입니다. 색을 없애고 싶으면 홈 > 조건부 서식 > 규칙 지우기로 해제하면 됩니다.
방법 2. COUNTIF — 각 값이 몇 번 나왔는지 세기
단순히 겹치는지 여부만이 아니라, "이 값이 정확히 몇 번 등장했는지" 숫자로 알고 싶을 때 쓰는 방법입니다. 예를 들어 3번 이상 중복된 것만 따로 걸러 내고 싶을 때 유용해요.
데이터가 A열(A2부터 A100까지)에 있다고 하면, 옆의 빈 열 B2에 이 수식을 넣습니다.
이건 "A2:A100 범위 안에서 A2와 같은 값이 몇 개인지 세라"는 뜻입니다. 결과가 1이면 유일한 값, 2 이상이면 그만큼 중복됐다는 뜻이에요. 범위는 $A$2:$A$100처럼 절대참조로 고정하고, 비교 대상인 A2는 상대참조로 둔 다음 아래로 쭉 드래그하면 각 행마다 개수가 나옵니다. 그다음 이 B열에 필터를 걸어 "2 이상"만 보면 중복된 값만 골라낼 수 있죠. 개수 기준으로 세밀하게 다뤄야 할 때 이만한 게 없습니다.
응용 팁을 하나 더 드릴게요. 만약 "처음 나온 값은 그대로 두고, 두 번째부터 나온 중복에만 표시"하고 싶다면 범위 시작만 고정해서 =COUNTIF($A$2:A2, A2)로 씁니다. 이렇게 하면 처음 등장한 값은 1, 두 번째부터는 2·3으로 누적돼서, 나중에 "2 이상"만 골라 지우면 각 값의 첫 번째 것만 남길 수 있어요. 중복을 지우되 원본 순서를 유지하고 싶을 때 유용한 방식입니다.
방법 3. 중복된 항목 제거 — 실제로 삭제
확인이 끝났고 이제 진짜로 중복을 지워야 한다면 이 기능을 씁니다. 엑셀에 내장된 전용 기능이라 클릭 몇 번이면 끝나요.
- 데이터가 있는 표 안 아무 셀이나 클릭합니다(보통 자동으로 전체 표를 인식합니다).
- 상단 리본에서 데이터 탭을 클릭합니다.
- 중복된 항목 제거 버튼을 누릅니다.
- 중복 판단 기준으로 삼을 열에 체크한 뒤 확인을 누릅니다.
처리가 끝나면 "몇 개의 중복 값이 제거되고 몇 개의 고유 값이 남았습니다"라는 안내가 뜹니다. 여기서 기준 열 선택이 중요해요. 이름 열만 체크하면 이름이 같은 사람은 동명이인이라도 하나로 합쳐져 지워지고, 이름과 전화번호를 함께 체크하면 둘 다 같은 경우에만 중복으로 봅니다. 기준을 잘못 잡으면 멀쩡한 데이터가 날아갈 수 있으니 신중하게 고르세요.
되돌리기 주의
이 기능은 원본에서 데이터를 바로 삭제합니다. 실수로 잘못 지웠다면 즉시 Ctrl+Z(맥은 Cmd+Z)로 되돌릴 수 있지만, 저장하고 파일을 닫아 버리면 그 뒤엔 복구가 어려워요. 그래서 저는 중복 제거 전에 시트를 통째로 복사해서 백업 사본을 하나 만들어 두는 습관이 있습니다. 딱 10초면 되는데 사고를 막아 줍니다.
분명 중복인데 안 잡힐 때
세 방법 모두 "값이 완전히 똑같아야" 중복으로 인식합니다. 그래서 눈으로는 같아 보여도 안 걸리는 경우가 종종 있어요. 대표적인 게 값 앞뒤에 붙은 보이지 않는 공백입니다. "홍길동"과 "홍길동 "(뒤에 공백 한 칸)은 엑셀 입장에서 서로 다른 값이거든요. 이럴 땐 =TRIM(A2)로 불필요한 공백을 먼저 정리한 뒤에 처리하면 깔끔합니다. 또 하나 알아 둘 점은, COUNTIF와 중복된 항목 제거 기능은 영어 대소문자를 구분하지 않는다는 거예요. "APPLE"과 "apple"을 같은 값으로 봅니다. 대소문자까지 구분해야 하는 데이터라면 EXACT 함수 같은 다른 방법을 함께 써야 해요. 저는 예전에 이걸 모르고 정리했다가 서로 다른 값이 하나로 합쳐져서 한참 원인을 찾았던 적이 있습니다.
세 방법 한눈에 비교
| 방법 | 하는 일 | 데이터 변경 | 언제 쓰나 |
|---|---|---|---|
| 조건부 서식 | 중복 셀 색칠 | 없음(표시만) | 어디가 겹치는지 눈으로 확인 |
| COUNTIF | 등장 횟수 계산 | 없음(옆 열에 계산) | 몇 번 겹쳤는지 개수가 필요 |
| 중복된 항목 제거 | 중복 행 삭제 | 있음(실제 삭제) | 겹치는 값을 최종적으로 정리 |