엑셀 중복값 찾기·제거 어떻게 할까? 중복 데이터 표시·삭제·원본 보존방법
엑셀에서 고객명단이나 주문 내역을 정리하다 보면 같은 값이 여러 번 입력된 경우가 있습니다. 이때 무작정 삭제하면 정상적인 재구매 기록이나 동명이인 정보까지 없어질 수 있습니다.
중복 데이터를 정리할 때는 중복을 판단할 기준을 정하고, 표시해서 확인한 뒤, 복사본에서 제거하는 순서로 진행하면 실수를 줄일 수 있습니다.
이번 글에서는 조건부 서식과 COUNTIF로 중복을 찾는 방법, 여러 열을 기준으로 제거하는 방법, 원본을 유지한 채 고유 목록을 만드는 방법을 정리합니다.
- 색으로 확인: 조건부 서식의 ‘중복 값’을 사용합니다.
- 수식으로 구분: COUNTIF·COUNTIFS를 사용합니다.
- 실제 데이터 제거: 데이터 탭의 ‘중복된 항목 제거’를 사용합니다.
- 원본을 유지한 목록: UNIQUE 함수나 고급 필터를 활용합니다.
- 삭제 기준: 이름 하나보다 고객번호·주문번호 등 업무에 맞는 기준을 선택합니다.
1. 중복값 표시와 제거는 무엇이 다를까?
| 방법 | 결과 | 원본 데이터 |
|---|---|---|
| 조건부 서식 | 중복 셀에 색 표시 | 유지 |
| COUNTIF·COUNTIFS | 중복 여부나 횟수 표시 | 유지 |
| 중복된 항목 제거 | 중복 기록을 실제로 제거 | 선택 범위의 데이터 변경 |
| UNIQUE | 별도 위치에 고유 목록 생성 | 유지 |
| 고급 필터의 다른 장소 복사 | 별도 위치에 고유 목록 복사 | 유지 |
중복인지 확인하는 작업과 데이터를 삭제하는 작업을 구분하세요. 같은 고객이 여러 번 주문한 내역이라면 고객번호가 반복되는 것이 정상일 수 있습니다.
2. 연습용 데이터 준비하기
빈 시트의 A1:D7에 아래 데이터를 입력합니다.
| 행 | A열: 주문번호 | B열: 고객명 | C열: 상품 | D열: 금액 |
|---|---|---|---|---|
| 2 | O001 | 김민수 | 생수 | 7000 |
| 3 | O002 | 이지영 | 콜라 | 9000 |
| 4 | O001 | 김민수 | 생수 | 7000 |
| 5 | O003 | 김민수 | 이온음료 | 7500 |
| 6 | O004 | 박현우 | 생수 | 5600 |
| 7 | O005 | 이지영 | 콜라 | 6000 |
1행에는 열 제목을 입력합니다. 예제에서 2행과 4행은 모든 항목이 같습니다. 하지만 김민수 고객의 5행 주문은 주문번호와 상품이 다른 별도 기록입니다.
고객명만 기준으로 제거하면 정상 주문까지 없어질 수 있다는 점을 먼저 이해해야 합니다.
3. 조건부 서식으로 중복값 찾기
주문번호가 반복되는 셀을 색으로 표시해 보겠습니다.
- 주문번호 범위인 A2:A7을 선택합니다.
- 홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값을 선택합니다.
- 중복 값에 적용할 색을 선택합니다.
- 확인을 누릅니다.
예제에서는 O001이 입력된 A2와 A4가 모두 강조됩니다. 이 기능은 첫 번째 항목도 포함해 같은 값이 반복되는 셀을 표시합니다.
여러 열 전체를 선택하면 행 중복을 찾을 수 있나요?
A2:D7 전체에 기본 ‘중복 값’ 규칙을 적용하면 선택 범위 안에서 반복되는 개별 셀 값을 찾습니다. 네 개 열의 조합이 같은 행을 판정하는 기능은 아닙니다.
행 전체가 같은지 확인하려면 COUNTIFS 등의 수식으로 별도 기준을 만들어야 합니다.
4. COUNTIF로 중복 여부 표시하기
E1에 ‘중복 확인’을 입력하고 E2에 아래 수식을 넣은 뒤 E7까지 복사합니다.
=IF(COUNTIF($A$2:$A$7,A2)>1,"중복","")전체 주문번호 범위에서 현재 주문번호가 두 번 이상 나타나면 ‘중복’을 표시합니다. 예제에서는 2행과 4행에 표시됩니다.
빈칸은 중복 검사에서 제외하기
=IF(A2="","",IF(COUNTIF($A$2:$A$7,A2)>1,"중복",""))주문번호가 비어 있으면 아무것도 표시하지 않습니다.
첫 번째는 남기고 두 번째부터 표시하기
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"추가 중복","유지"))검사 범위의 시작은 고정하고 끝은 현재 행까지 늘립니다. 예제에서는 2행이 ‘유지’, 4행이 ‘추가 중복’입니다.
이 수식은 현재 정렬 순서를 기준으로 첫 번째 기록을 판단합니다. 어떤 기록을 남겨야 하는지 확인한 뒤 사용하세요.
5. 여러 열이 모두 같은 행 찾기
주문번호·고객명·상품·금액이 모두 같은 행을 찾으려면 COUNTIFS를 사용합니다. 아래 수식을 빈 보조 열의 2행에 입력하고 아래로 복사합니다.
=IF(COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,B2,$C$2:$C$7,C2,$D$2:$D$7,D2)>1,"동일 행","")예제에서는 2행과 4행이 ‘동일 행’으로 표시됩니다. 모든 열이 같아도 실제로 두 번 발생한 정상 거래일 수 있으므로, 원본 자료와 함께 판단하세요.
주문번호 기준으로 행 전체 색칠하기
A2:D7을 선택하고 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정에서 다음 수식을 입력합니다.
=AND($A2<>"",COUNTIF($A$2:$A$7,$A2)>1)적용 대상이 A2:D7이면 중복 주문번호가 있는 2행과 4행 전체가 강조됩니다. 이는 주문번호 중복을 표시하는 규칙이며, 모든 열이 같은 행을 검사하는 규칙과는 다릅니다.
6. 중복된 항목 제거 기능 사용하기
아래 작업은 원본을 복사한 시트에서 연습하세요.
- 관련 열을 모두 포함한 A1:D7을 선택합니다.
- 데이터 → 중복된 항목 제거를 선택합니다.
- 제목 행이 있다면 ‘내 데이터에 머리글 표시’ 등 머리글 옵션을 확인합니다.
- 중복 판단에 사용할 열을 선택합니다.
- 확인을 누르고 제거된 수와 남은 데이터를 점검합니다.
① 모든 열을 선택한 경우
주문번호·고객명·상품·금액을 모두 선택하면 네 항목의 조합이 같은 기록을 중복으로 봅니다. 예제에서는 4행에 해당하는 중복 기록 하나가 제거되고 5개 기록이 남습니다.
② 주문번호만 선택한 경우
전체 표를 선택한 상태에서 판단 기준 열로 주문번호만 체크하면, 같은 주문번호 중 첫 번째 기록을 남기고 나머지 기록을 제거합니다. 선택 범위 안의 다른 열 값도 해당 기록과 함께 제거됩니다.
한 주문번호에 여러 상품이 들어가는 주문 상세표라면 주문번호 반복은 정상입니다. 이 경우 주문번호 하나만으로 제거하면 주문 항목이 누락될 수 있습니다.
주문번호 열만 따로 선택해 데이터를 제거하면 옆 열과의 대응이 어긋날 수 있습니다. 관련 표 전체를 선택하고, 대화상자에서 중복 판단에 사용할 열을 지정하는 방식이 안전합니다. 범위 확장 안내가 나오면 선택 범위를 확인하세요.
7. 원본을 보존하면서 중복 제거하기
방법 ① 시트를 복사한 뒤 정리
- 시트 탭을 마우스 오른쪽 버튼으로 클릭합니다.
- 이동/복사에서 ‘복사본 만들기’를 선택합니다.
- 시트 이름을 ‘원본’과 ‘중복정리’처럼 구분합니다.
- 복사본에서 중복 제거를 진행합니다.
정리 전후 행 수와 금액 합계를 비교하고, 삭제된 기록이 업무 기준에 맞는지 확인합니다. 예제에서 동일 행 하나를 제거하면 금액 합계는 42100에서 35100으로 줄어듭니다.
방법 ② UNIQUE 함수로 별도 목록 생성
UNIQUE를 지원하는 Microsoft 365용 Excel, Excel 2021·2024 등의 환경에서는 원본을 바꾸지 않고 고유 목록을 만들 수 있습니다. Excel 2016·2019에서는 지원하지 않습니다.
고객명 목록을 중복 없이 가져오려면 빈 셀에 입력합니다.
=UNIQUE(B2:B7)김민수·이지영·박현우가 각각 한 번씩 표시됩니다. 네 개 열의 행 조합을 중복 없이 가져오려면 다음 수식을 사용합니다.
=UNIQUE(A2:D7)예제에서는 동일한 행이 한 번만 표시되어 5개 기록이 반환됩니다. 결과가 펼쳐질 공간을 비워두고, 수식은 엑셀 표의 본문 영역 밖에 입력하세요.
방법 ③ 고급 필터로 다른 위치에 복사
UNIQUE를 사용할 수 없는 환경에서는 고급 필터를 활용할 수 있습니다.
- 제목을 포함한 원본 범위를 선택합니다.
- 데이터 → 고급을 선택합니다.
- 다른 장소에 복사를 선택합니다.
- 복사 위치를 같은 시트의 빈 영역으로 지정합니다.
- 동일한 레코드는 하나만 옵션을 선택합니다.
- 확인을 누릅니다.
고객명만 필요하면 고객명 열을, 전체 행 조합을 비교하려면 관련 열 전체를 목록 범위로 지정합니다. 고급 필터로 복사한 결과는 원본이 바뀌어도 자동 갱신되지 않으므로 다시 실행해야 합니다.
8. UNIQUE의 ‘중복 없이’와 ‘한 번만 등장’ 차이
다음 두 수식은 결과가 다릅니다.
=UNIQUE(B2:B7)
=UNIQUE(B2:B7,FALSE,TRUE)| 수식 | 의미 | 예제 결과 |
|---|---|---|
| UNIQUE(B2:B7) | 각 이름을 한 번씩 표시 | 김민수, 이지영, 박현우 |
| UNIQUE(B2:B7,FALSE,TRUE) | 원본에서 딱 한 번 등장한 이름만 표시 | 박현우 |
세 번째 인수 TRUE는 반복되는 이름을 한 개 남기는 옵션이 아닙니다. 반복된 이름을 결과에서 제외하고, 한 번만 나온 이름을 가져옵니다.
9. 중복인데 다르게 인식될 때 확인할 사항
① 앞뒤 공백
‘김민수’와 ‘김민수 ’처럼 공백이 붙은 값은 다르게 취급될 수 있습니다. 보조 열에 다음 수식을 사용해 일반 공백을 정리할 수 있습니다.
=TRIM(B2)웹에서 복사한 일부 특수 공백은 TRIM만으로 제거되지 않을 수 있습니다. 줄바꿈 없는 공백을 정리할 때는 다음 방식도 활용합니다.
=TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," ")))이 수식이 모든 종류의 특수문자를 제거하는 것은 아닙니다. 정리 결과를 확인한 뒤 중복 판단에 사용하세요.
② 숫자와 문자의 혼용
코드가 숫자 또는 문자로 섞여 있으면 기능별 비교 결과가 예상과 달라질 수 있습니다. 업무 기준에 맞게 형식을 통일하세요.
특히 00123처럼 앞자리 0이 중요한 코드는 숫자로 변환하면 의미가 달라집니다. 고객번호·상품코드는 문자로 관리해야 하는지 먼저 확인합니다.
③ 이름만으로 사람을 구분한 경우
동명이인이 있을 수 있으므로 고객명만으로 삭제하지 마세요. 고객번호 등 식별 기준을 우선 사용하고, 필요한 경우 여러 열을 함께 비교합니다.
④ 날짜에 시간이 포함된 경우
같은 날짜로 보이더라도 실제 값에 서로 다른 시간이 들어 있을 수 있습니다. 날짜 단위로 비교하려는지, 시간까지 포함한 거래 단위로 비교하려는지 기준을 정해야 합니다.
10. 최신 기록 하나만 남기려면?
고객번호별 최신 정보만 남기는 목적이라면, 먼저 관련 데이터 전체를 수정일시 기준 내림차순으로 정렬한 뒤 복사본에서 고객번호 기준 중복 제거를 적용할 수 있습니다.
중복 제거는 정렬된 범위에서 첫 번째 기록을 남기므로 최신 기록이 위에 오도록 정렬해야 합니다. 단, 수정일시가 같거나 빈값이면 추가 판단 기준이 필요합니다.
고객의 최신 연락처를 남기는 작업과 거래 내역을 정리하는 작업은 기준이 다릅니다. 거래 기록은 같은 고객의 과거 주문도 필요할 수 있으므로 목적에 맞게 처리하세요.
11. 자주 묻는 질문 FAQ
Q1. 중복값을 표시하면 데이터가 삭제되나요?
조건부 서식은 색만 적용하므로 데이터는 그대로 남습니다. 수식으로 중복 여부를 표시하는 것도 원본 기록을 삭제하지 않습니다.
Q2. 중복된 항목 제거는 중복값을 전부 없애나요?
일반적으로 선택한 기준에서 첫 번째 기록을 남기고 나머지 중복 기록을 제거합니다. 중복에 해당하는 모든 기록을 없애는 기능과는 다릅니다.
Q3. 삭제한 데이터를 복구할 수 있나요?
작업 직후에는 실행 취소를 시도할 수 있습니다. 파일을 닫았거나 이후 작업이 진행되면 실행 취소에 의존하기 어렵기 때문에 작업 전 복사본을 만드는 것이 좋습니다.
Q4. UNIQUE에서 #SPILL! 오류가 나면 어떻게 하나요?
결과가 펼쳐질 위치에 값이나 병합 셀이 있는지 확인하세요. 출력 공간을 확보하고, 엑셀 표 안에 수식을 넣었다면 표 밖의 빈 영역으로 옮깁니다.
Q5. 중복 제거 후 원본 합계와 달라져도 정상인가요?
금액이 있는 기록을 제거하면 합계도 줄어듭니다. 차액이 실제 중복 입력분인지 확인해야 합니다. 예제에서는 7000짜리 동일 기록 하나를 제거하므로 합계가 7000 줄어듭니다.
12. 요약
- 먼저 확인: 조건부 서식으로 중복 셀을 표시합니다.
- 기준을 명확히: 이름·주문번호·여러 열 조합 중 목적에 맞게 선택합니다.
- 복사본에서 제거: 관련 표 전체를 선택하고 판단 기준 열을 지정합니다.
- 원본 유지: UNIQUE 또는 고급 필터로 별도 목록을 만듭니다.
- 결과 검증: 남은 행 수와 금액 합계, 정상 기록 누락 여부를 확인합니다.
중복 정리에서 가장 중요한 것은 같은 값이 반복됐다는 사실보다, 그 반복이 불필요한 입력인지 정상 기록인지 판단하는 일입니다. 표시와 검토를 거친 뒤 정리하면 원본 손실을 줄일 수 있습니다.
※ 참조사항
본문은 일반적인 데스크톱 Excel 사용 환경을 중심으로 작성했습니다. 버전과 운영체제에 따라 메뉴 명칭·위치가 다를 수 있습니다. 주문번호·고객명·금액은 설명을 위한 가상 데이터이며, 실제 업무에서는 주문 구조와 식별 기준을 확인한 뒤 중복을 판단하세요.