엑셀에 같은 이름, 연락처, 주문번호가 여러 번 들어갔다면?
조건부 서식으로 중복값을 먼저 표시하고, 삭제 기준을 확인한 후 데이터 탭의 '중복된 항목 제거'를 실행하세요.
엑셀로 고객 명단, 재고 목록, 주문 내역, 설문 결과를 관리하다 보면 같은 값이 여러 번 입력되는 경우가 있습니다. 이때 중복 데이터를 그대로 두면 사람 수나 주문 건수 계산이 틀어지고, 같은 고객에게 알림을 여러 번 보내거나 재고 수량을 잘못 집계할 수 있습니다.
하지만 이름이 같다고 반드시 중복 고객은 아니고, 주문번호가 같더라도 상품별 행을 구분해 관리하는 데이터라면 삭제하면 안 되는 경우가 있습니다. 따라서 무엇을 중복으로 볼지 먼저 정한 뒤 표시하고 검토하는 순서가 중요합니다.
핵심 순서: 원본 백업 → 데이터 범위 확인 → 중복 기준 열 선택 → 조건부 서식으로 강조 → 중복 행 검토 → 중복된 항목 제거 → 삭제 결과 확인 → 정리된 데이터 저장
조건부 서식으로 같은 값을 색상 강조
이름·전화번호·주문번호 등 비교할 열 선택
첫 번째 항목은 유지하고 중복된 행 삭제
행 수·수식·연락처·주문 정보 대조
엑셀 중복 데이터 정리 기능 비교표
| 기능 | 메뉴 또는 함수 | 실제 동작 |
|---|---|---|
| 중복값 강조 | 홈 → 조건부 서식 → 셀 강조 표시 규칙 → 중복 값 | 중복값에 색상 적용, 데이터는 유지 |
| 중복된 항목 제거 | 데이터 → 중복된 항목 제거 | 중복 기준에 해당하는 행을 실제 삭제 |
| 중복 횟수 계산 | COUNTIF |
같은 값이 몇 번 나타나는지 계산 |
| 복수 열 중복 판정 | COUNTIFS |
여러 조건이 동시에 같은 행 확인 |
| 고유 목록 생성 | UNIQUE |
원본을 삭제하지 않고 중복 없는 결과 반환 |
| 고급 필터 | 데이터 → 고급 → 고유한 레코드만 | 원본 유지 또는 고유 레코드 별도 복사 |
※ 메뉴 명칭은 Excel 버전과 Windows·Mac·웹 환경에 따라 다를 수 있습니다. UNIQUE 함수는 Microsoft 365, Excel 2021·2024 등 지원되는 버전에서 사용할 수 있으며 구형 Excel에서는 제공되지 않을 수 있습니다.
💡 가장 안전한 데이터 정리 방법: 조건부 서식은 표시만 하고, 중복된 항목 제거는 실제로 데이터를 삭제합니다. 따라서 원본을 복사해 둔 뒤 조건부 서식으로 중복 여부를 검토하고 삭제 기준을 결정하는 것이 좋습니다.
🚨 데이터 손실 주의: '중복된 항목 제거'는 중복으로 판정된 행을 삭제합니다. 특히 이름 열만 중복 기준으로 지정하면 동명이인이 삭제될 수 있고, 주문번호만 선택하면 같은 주문의 여러 상품 행이 제거될 수 있습니다. 실행 전에 원본 시트를 복사하고, 중복 판정 열과 유지할 행의 정렬 순서를 확인하세요.
1. 엑셀 중복 데이터의 종류와 판단 기준
엑셀에서 중복 데이터란 같은 값이 두 번 이상 등장하거나, 특정 열들의 조합이 여러 행에서 동일하게 나타나는 데이터를 말합니다.
하지만 실제로 삭제해야 할 중복인지는 데이터의 목적에 따라 달라집니다. 고객 명단에서는 이메일이 중복되면 검토 대상일 수 있지만, 판매 내역에서는 같은 상품명이 여러 번 등장하는 것이 정상일 수 있습니다.
1-1. 단일 열 중복과 여러 열 중복의 차이
단일 열 중복은 하나의 열에 있는 값이 동일한 경우입니다. 예를 들어 회원번호가 같은 행이 여러 개 있다면 회원번호 중복으로 판단할 수 있습니다.
반면 복수 열 중복은 두 개 이상의 열을 함께 비교합니다. 예를 들어 이름과 전화번호가 동시에 같거나, 주문번호와 상품 코드가 동시에 같은 경우를 중복으로 볼 수 있습니다.
여러 열을 비교할 때는 선택한 열들의 값 조합이 동일한 행만 중복으로 판단합니다.
1-2. 실무 예시로 이해하는 중복 기준
| 고객명 | 전화번호 | 가입일 |
|---|---|---|
| 김민수 | 010-1234-5678 | 2026-10-01 |
| 이서연 | 010-2345-6789 | 2026-10-02 |
| 김민수 | 010-1234-5678 | 2026-10-05 |
| 김민수 | 010-3456-7890 | 2026-10-06 |
위 자료에서 고객명만 비교하면 '김민수' 세 행이 중복으로 표시됩니다.
하지만 고객명과 전화번호를 함께 비교하면 첫 번째 행과 세 번째 행만 동일한 조합입니다. 마지막 김민수는 전화번호가 다르므로 다른 고객일 가능성이 있습니다.
또한 첫 번째와 세 번째 행은 가입일이 다릅니다. 두 행 중 어느 기록을 남길지는 최신 가입일인지, 최초 가입일인지 등 관리 기준에 따라 달라집니다.
1-3. 첫 번째 항목을 남긴다는 의미
엑셀의 중복된 항목 제거 기능은 같은 조합이 여러 번 나타나면 일반적으로 목록에서 먼저 등장하는 항목을 남기고 뒤의 중복 행을 제거합니다.
따라서 최신 데이터를 남기고 싶다면 제거하기 전에 날짜를 최신순으로 정렬해야 합니다.
반대로 최초 등록 정보를 남기고 싶다면 오래된 날짜가 먼저 나오도록 정렬한 뒤 중복 제거를 실행할 수 있습니다.
다만 정렬할 때는 특정 열만 따로 움직이지 않도록 전체 표를 함께 정렬해야 합니다. 열별 데이터 관계가 어긋나면 심각한 오류가 발생할 수 있습니다.
1-4. 중복처럼 보여도 삭제하면 안 되는 경우
판매 내역에서 같은 고객이 여러 상품을 구매한 경우에는 고객명이 반복되는 것이 정상입니다.
또한 주문번호 하나에 상품 A와 상품 B가 연결되어 있다면 주문번호를 기준으로 중복 제거할 경우 정상적인 상품 행이 삭제될 수 있습니다.
이런 데이터에서는 주문번호와 상품 코드를 함께 기준으로 선택하거나, 개별 거래를 식별하는 고유 키가 있는지 확인하는 것이 안전합니다.
핵심 정리: 중복 제거는 단순히 같은 글자를 없애는 작업이 아니라, 어떤 열의 조합을 하나의 기록으로 볼지 결정하는 작업입니다. 삭제 전에 업무 기준을 먼저 정하세요.
2. 조건부 서식으로 중복된 항목 색상 표시하기
조건부 서식은 특정 조건을 만족하는 셀에 색상, 글꼴, 테두리 등의 서식을 자동으로 적용하는 기능입니다.
중복값을 강조 표시하면 데이터를 삭제하기 전에 어떤 항목이 겹치는지 눈으로 확인할 수 있습니다. 이 기능은 데이터 자체를 제거하지 않으므로 첫 번째 점검 단계로 적합합니다.
2-1. 중복을 확인할 셀 범위 선택하기
고객명 중복을 찾으려면 이름이 들어 있는 열의 데이터 영역만 선택합니다.
예를 들어 A1 셀에 '고객명'이라는 제목이 있고 A2부터 A101까지 이름이 있다면 A2:A101을 선택합니다.
머리글을 포함하면 제목도 비교 범위에 들어갈 수 있으므로 실제 데이터 행만 선택하는 것이 좋습니다.
2-2. 조건부 서식에서 중복 값 선택하기
상단 메뉴에서 다음 순서로 이동합니다.
중복 값 창이 나타나면 중복을 표시할 색상이나 서식 스타일을 선택합니다.
일반적으로 연한 빨강 채우기와 진한 빨강 글꼴 같은 서식을 사용할 수 있습니다. 확인을 누르면 선택한 범위에서 동일한 값이 두 번 이상 등장하는 셀이 강조됩니다.
2-3. 색상 표시된 항목을 검토하기
조건부 서식의 '중복 값' 규칙은 선택한 범위에서 중복으로 판단된 모든 값을 표시합니다. 첫 번째 등장 항목도 함께 강조될 수 있습니다.
따라서 색이 표시된 항목이 모두 삭제 대상이라는 뜻은 아닙니다. 어떤 행을 유지하고 어떤 행을 제거할지 별도로 판단해야 합니다.
예를 들어 같은 고객명이 여러 개 강조되었다면 연락처나 회원번호도 함께 비교해 실제 중복인지 확인하세요.
2-4. 중복값 대신 고유값 표시하기
조건부 서식의 중복 값 설정 창에서 '중복' 대신 '고유'를 선택할 수 있는 경우에는 한 번만 등장하는 값을 강조할 수 있습니다.
고유값 표시를 활용하면 전체 목록에서 중복되지 않은 항목을 빠르게 파악하는 데 도움이 됩니다.
단, 데이터 전체의 정확성을 판단하려면 고유값의 표시 여부만이 아니라 비교 범위와 데이터 형식을 확인해야 합니다.
2-5. 조건부 서식 규칙 변경과 삭제
중복값 표시 색상을 바꾸거나 범위를 수정하려면 홈 → 조건부 서식 → 규칙 관리로 이동합니다.
규칙 관리 화면에서 현재 워크시트나 선택 영역에 적용된 규칙을 선택해 편집할 수 있습니다.
강조 표시를 더 이상 사용하지 않으려면 조건부 서식 → 규칙 지우기에서 선택한 셀의 규칙 또는 전체 시트의 규칙을 제거합니다.
주의할 점은 규칙 지우기는 색상 규칙을 제거하는 것일 뿐, 실제 중복 데이터를 삭제하는 작업이 아니라는 것입니다.
2-6. 한 열에서 두 번째 등장부터 강조하려면?
기본 중복값 규칙은 첫 번째 등장 항목도 표시합니다. 두 번째 등장부터만 강조하고 싶다면 COUNTIF를 이용한 사용자 지정 조건부 서식을 사용할 수 있습니다.
A2:A101 범위를 선택하고 홈 → 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정으로 이동합니다.
=AND(A2<>"",COUNTIF($A$2:A2,A2)>1)
이 수식은 빈칸이 아닌 값에 대해 첫 번째 등장 이후에 반복되는 셀만 강조합니다.
예를 들어 A2와 A5가 같은 이름이라면 A2는 강조하지 않고 A5만 강조하는 방식입니다.
조건부 서식 핵심: 중복값 전체를 보려면 기본 중복 값 규칙, 첫 번째 기록을 제외한 반복 항목만 표시하려면 COUNTIF 사용자 지정 수식을 사용하세요.
3. 중복된 항목 제거 기능으로 중복 행 삭제하기
중복값을 검토한 뒤 실제로 삭제해야 할 행이 확인되었다면 데이터 탭의 '중복된 항목 제거' 기능을 사용할 수 있습니다.
Microsoft 공식 도움말에 따르면 중복된 항목 제거는 중복으로 판단된 항목 중 처음 나타난 기록을 남기고 나머지를 제거합니다. 제거된 데이터는 원본을 복사하지 않았다면 복구하기 어려울 수 있으므로 반드시 사전에 백업해야 합니다.
3-1. 전체 데이터 범위를 선택하기
예를 들어 A열은 고객명, B열은 전화번호, C열은 가입일로 구성되어 있다면 A:C 전체 표 범위를 선택합니다.
중복 판단에 사용할 열만 지정하더라도 삭제는 선택한 표의 행 단위로 이루어지므로 관련 열이 함께 포함되어 있어야 데이터가 어긋나는 일을 줄일 수 있습니다.
표 내부의 셀 하나를 선택하고 중복 제거를 실행하면 인접한 데이터 범위를 인식할 수 있지만, 빈 행이나 빈 열이 있는 경우 범위가 일부만 잡힐 수 있습니다. 실제 선택 범위를 반드시 확인하세요.
3-2. 데이터 → 중복된 항목 제거 선택
중복 제거 대화상자가 열리면 비교할 열을 선택할 수 있습니다.
상단의 '머리글 포함' 또는 '내 데이터에 머리글 표시' 옵션을 확인하세요. 제목 행이 데이터로 처리되지 않도록 올바르게 설정해야 합니다.
3-3. 비교할 열 하나만 선택하기
고객번호만으로 고유 고객을 식별할 수 있다면 고객번호 열만 중복 기준으로 선택할 수 있습니다.
예를 들어 고객번호가 1001인 행이 세 개 있다면 첫 번째 행을 남기고 뒤에 등장하는 동일 고객번호의 행 두 개를 제거합니다.
단, 서로 다른 거래 기록이 같은 고객번호를 갖는 것은 정상일 수 있으므로 고객번호만으로 판매 내역을 중복 제거해서는 안 됩니다.
3-4. 두 개 이상의 열을 함께 비교하기
고객명과 전화번호를 함께 기준으로 사용하려면 중복 제거 창에서 두 열을 모두 선택합니다.
이 경우 이름만 같거나 전화번호만 같은 행은 두 값이 모두 동일하지 않다면 중복으로 제거되지 않습니다.
여러 열을 선택한다는 것은 각 열을 따로 독립적으로 중복 제거하는 것이 아니라 선택한 열의 값 조합 전체를 비교한다는 뜻입니다.
3-5. 최신 데이터만 남기고 싶은 경우
중복 제거는 일반적으로 먼저 등장한 행을 유지하므로 삭제 전에 정렬 순서를 정해야 합니다.
가장 최근 수정된 고객 정보만 남기려면 수정일 열을 기준으로 최신순 정렬합니다. 그런 다음 고객을 식별하는 열을 기준으로 중복 제거를 실행하면 최신 행을 유지하는 구조를 만들 수 있습니다.
다만 최신 수정일이 같거나 기록의 우선순위가 복잡하다면 단순 정렬만으로 판단하지 말고 별도의 검토 기준을 적용하세요.
3-6. 중복 제거 후 결과 메시지 확인
중복된 항목 제거를 실행하면 엑셀이 삭제된 중복 항목 수와 남아 있는 고유 항목 수를 표시합니다.
이 결과가 예상과 일치하는지 확인하세요. 예를 들어 500행 중 중복이 20행이라고 예상했는데 200행이 삭제되었다면 비교 열을 잘못 선택했을 가능성이 있습니다.
실수로 실행한 경우에는 파일을 저장하기 전에 Ctrl + Z 또는 Mac의 Command + Z로 실행 취소를 시도할 수 있습니다. 하지만 이후 다른 작업이나 파일 종료 등으로 실행 취소가 불가능해질 수 있으므로 원본 백업이 더 안전합니다.
| 데이터 종류 | 중복 판단 열 예시 | 주의할 점 |
|---|---|---|
| 고객 명단 | 고객 ID | 동명이인 구분 |
| 연락처 목록 | 이름 + 연락처 | 번호 변경 이력 확인 |
| 주문 상세 내역 | 주문번호 + 상품코드 + 상세행 ID | 동일 주문의 여러 상품 보존 |
| 재고 목록 | 창고 + 상품코드 | 창고별 재고 구분 |
| 설문 응답 | 응답 ID 또는 정책상 중복 기준 | 재응답 허용 여부 확인 |
중복 제거 핵심: 전체 표를 대상으로 실행하되 비교할 열만 정확히 체크하고, 어떤 행을 남길지 정렬한 다음 제거하세요. 중복 삭제는 되돌리기 어려운 작업이므로 원본 백업이 필수입니다.
4. 엑셀 중복 데이터 정리 실전 8단계
엑셀 데이터를 안전하게 정리하려면 중복값을 먼저 표시한 다음 업무 기준에 따라 실제 삭제할 항목을 선택해야 합니다. 예를 들어 고객 명단의 이름이 중복되더라도 전화번호나 회원번호가 다르면 다른 고객일 수 있습니다.
아래에서는 고객명, 전화번호, 등록일이 포함된 자료를 예시로 설명합니다. 판매 내역이나 재고 목록에도 같은 원리를 적용할 수 있습니다.
4-1. 원본 워크시트 복사하기
엑셀 하단의 시트 이름을 마우스 오른쪽 버튼으로 클릭하고 이동/복사를 선택합니다. 복사본 만들기를 체크한 뒤 확인합니다.
원본 시트는 '원본_백업', 작업용 시트는 '중복정리_작업'과 같이 이름을 구분하세요. 특히 고객 정보나 주문 내역처럼 삭제 후 복구가 어려운 자료에서는 별도 파일로도 백업하는 것이 좋습니다.
4-2. 데이터 범위와 머리글 정리하기
데이터의 첫 번째 행에 고객명, 전화번호, 등록일처럼 각 열의 이름이 있는지 확인합니다.
중간에 완전히 빈 행이나 병합된 셀이 있으면 데이터 범위가 예상과 다르게 잡힐 수 있습니다. 가능하면 하나의 표 안에서 행마다 동일한 열 구조를 사용하세요.
필요하다면 데이터 범위를 선택하고 Ctrl + T로 엑셀 표를 만들어 관리할 수 있습니다. 표를 사용하면 필터와 정렬이 편리해집니다.
4-3. 중복 판단 기준 정하기
고객명만으로 중복 여부를 결정하면 동명이인이 잘못 삭제될 수 있습니다. 고객번호가 있다면 고객번호를 우선 검토하고, 고객번호가 없다면 이름과 전화번호 등 복수 기준을 사용할 수 있습니다.
다만 연락처도 가족이 공유할 수 있으므로 항상 고유한 식별자라고 볼 수는 없습니다. 실제 데이터의 업무 규칙에 맞는 기준을 선택하세요.
4-4. 조건부 서식으로 중복값 강조하기
중복 여부를 확인할 열의 실제 데이터 범위를 선택합니다. 예를 들어 전화번호가 B2:B501에 있다면 이 영역을 선택합니다.
홈 → 조건부 서식 → 셀 강조 표시 규칙 → 중복 값으로 이동한 뒤 원하는 색상을 선택합니다.
같은 값이 두 번 이상 나타나는 셀이 강조됩니다. 이 단계에서는 실제 데이터가 삭제되지 않으므로 중복 여부를 충분히 검토할 수 있습니다.
4-5. 중복된 행을 검토하기
중복으로 표시된 항목의 나머지 열을 함께 살펴봅니다.
예를 들어 같은 전화번호에 서로 다른 고객명이 있다면 가족 공동번호인지, 오입력인지 확인할 필요가 있습니다. 같은 주문번호에 서로 다른 상품 코드가 있다면 삭제 대상이 아닐 수 있습니다.
업무 기준으로 동일한 기록이라고 확신할 수 있는 항목만 제거 대상으로 정하세요.
4-6. 유지할 데이터가 먼저 나오도록 정렬하기
엑셀의 중복 제거 기능은 먼저 등장하는 기록을 남기므로 정렬 순서가 중요합니다.
가장 최근 수정한 고객 정보를 남기려면 수정일 열을 기준으로 내림차순 정렬합니다. 가장 먼저 등록한 정보를 남기려면 등록일을 오름차순으로 정렬합니다.
정렬할 때 일부 열만 움직이지 않도록 전체 표를 선택하고 모든 관련 데이터가 함께 정렬되는지 확인하세요.
4-7. 중복된 항목 제거 실행하기
전체 데이터 영역을 선택한 뒤 데이터 → 중복된 항목 제거를 실행합니다.
머리글 포함 여부가 올바른지 확인하고, 중복 판단에 사용할 열만 체크합니다. 예를 들어 이름과 전화번호가 동시에 같은 행을 제거하려면 두 열을 선택합니다.
확인을 누르면 동일한 조합을 가진 뒤쪽 행이 제거됩니다. 실행 후 표시되는 삭제 건수와 남은 고유 항목 수를 기록해 두세요.
4-8. 제거 결과 검증하고 저장하기
중복 제거 후에는 전체 행 수와 주요 집계 결과를 확인합니다.
특히 매출 데이터라면 총매출액이 달라진 이유를 확인해야 합니다. 주문 내역에서 중복처럼 보이는 정상 거래가 삭제되면 매출 합계가 실제보다 작게 계산될 수 있습니다.
문제가 없다면 정리된 파일을 다른 이름으로 저장하고, 원본과 함께 보관하는 것이 좋습니다.
실전 핵심: 원본 백업 → 중복 강조 → 삭제 기준 검토 → 유지할 행 정렬 → 중복 제거 → 결과 검증 순서로 작업하면 불필요한 데이터 손실을 줄일 수 있습니다.
5. COUNTIF·COUNTIFS·UNIQUE 함수로 중복 데이터 분석하기
중복값을 단순히 색상으로 표시하는 것보다 더 자세히 분석하고 싶다면 엑셀 함수를 사용할 수 있습니다. COUNTIF는 한 가지 조건에 맞는 값의 개수를 계산하고, COUNTIFS는 여러 조건을 동시에 만족하는 행의 수를 계산합니다.
UNIQUE 함수는 지원되는 최신 엑셀에서 원본 데이터를 삭제하지 않고 고유값 목록을 생성할 때 유용합니다.
5-1. COUNTIF로 같은 값이 몇 번 나오는지 계산
A2:A101에 고객명이 있다고 가정합니다. B2 셀에 다음 수식을 입력하면 A2의 고객명이 전체 범위에 몇 번 등장하는지 계산할 수 있습니다.
=COUNTIF($A$2:$A$101,A2)
이 수식을 아래로 복사하면 각 고객명의 등장 횟수를 확인할 수 있습니다.
결과가 1이면 해당 범위에 한 번 등장한 값이고, 2 이상이면 중복으로 등장한 값입니다.
다만 빈칸이나 텍스트 형식, 불필요한 공백이 포함된 자료에서는 계산 결과를 추가로 확인해야 합니다.
5-2. 두 번째 등장부터 '중복'으로 표시하기
처음 나타난 값은 유지하고, 두 번째 등장부터 중복이라고 표시하려면 누적 범위를 사용합니다.
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"중복","첫 등장"))
이 수식은 A2부터 현재 행까지 같은 값이 두 번 이상 나왔는지 확인합니다.
예를 들어 A2에 '김민수'가 처음 나타나면 '첫 등장'으로 표시하고, A8에 같은 이름이 다시 나타나면 '중복'으로 표시합니다.
정렬 순서를 바꾸면 어떤 행이 첫 등장인지도 달라질 수 있으므로 유지할 데이터를 먼저 정렬한 뒤 사용하는 것이 좋습니다.
5-3. COUNTIFS로 이름과 전화번호를 함께 비교하기
A열에 이름, B열에 전화번호가 있을 때 두 값이 동시에 같은 기록의 개수를 계산하려면 다음 수식을 사용할 수 있습니다.
=COUNTIFS($A$2:$A$101,A2,$B$2:$B$101,B2)
이 수식은 이름과 전화번호가 모두 일치하는 행의 개수를 계산합니다.
이름이 같아도 전화번호가 다르면 별개의 조합으로 계산됩니다. 단, 서로 다른 고객이 같은 번호를 쓰는 등 업무상 예외가 있을 수 있으므로 함수 결과가 곧 삭제 허가를 의미하지는 않습니다.
5-4. 여러 열을 기준으로 행 전체를 강조하기
조건부 서식으로 A열 이름과 B열 전화번호가 동시에 중복되는 행을 강조하고 싶다면 A2:C101 같은 전체 행 범위를 선택합니다.
홈 → 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정에서 다음 수식을 입력합니다.
=AND($A2<>"",$B2<>"",COUNTIFS($A$2:$A$101,$A2,$B$2:$B$101,$B2)>1)
이 규칙을 적용하면 이름과 전화번호 조합이 두 번 이상 나오는 행을 강조할 수 있습니다.
열 문자 앞에 붙은 $ 기호는 조건부 서식이 여러 열에 적용될 때 기준 열을 고정하는 역할을 합니다.
5-5. 첫 등장만 남긴 고유 목록을 만들기
Microsoft 365와 지원되는 최신 Excel에서는 UNIQUE 함수로 원본을 삭제하지 않고 고유값 목록을 만들 수 있습니다.
=UNIQUE(A2:A101)
이 수식은 A2:A101 범위에서 중복을 제거한 고유 항목 목록을 반환합니다.
원본 행을 삭제하는 것이 아니라 다른 위치에 결과를 표시하므로 원본 보존이 중요한 작업에 유용합니다.
5-6. 여러 열의 조합을 고유 목록으로 만들기
이름과 전화번호의 조합으로 고유 목록을 만들려면 두 열을 함께 지정할 수 있습니다.
=UNIQUE(A2:B101)
같은 이름이라도 전화번호가 다르면 다른 행으로 남을 수 있습니다.
UNIQUE 함수는 결과가 여러 셀에 자동으로 펼쳐지는 동적 배열 함수입니다. 결과가 표시될 공간에 기존 값이 있으면 오류가 발생할 수 있으므로 충분한 빈 범위를 확보하세요.
5-7. 고유 항목 개수 세기
빈칸을 제외한 고유 고객명 개수를 계산하려면 지원되는 최신 Excel에서 다음 수식을 사용할 수 있습니다.
=ROWS(UNIQUE(FILTER(A2:A101,A2:A101<>"")))
FILTER로 빈칸을 제외한 다음 UNIQUE로 중복 없는 목록을 만들고 ROWS로 항목 수를 계산하는 방식입니다.
단, 범위에 유효한 값이 하나도 없다면 FILTER가 빈 배열을 반환하지 못해 오류가 발생할 수 있습니다. 이런 경우에는 IFERROR로 처리하거나 원본 범위의 상태를 먼저 확인해야 합니다.
| 함수 | 활용 목적 | 원본 삭제 여부 |
|---|---|---|
| COUNTIF | 단일 열 중복 횟수 계산 | 삭제 안 함 |
| COUNTIFS | 복수 열 조건 중복 판정 | 삭제 안 함 |
| UNIQUE | 고유값 또는 고유 행 목록 생성 | 삭제 안 함 |
| FILTER | 조건에 맞는 행 추출 | 삭제 안 함 |
함수 활용 핵심: 중복 횟수는 COUNTIF, 여러 열의 중복은 COUNTIFS, 원본을 유지한 고유 목록은 UNIQUE를 사용하세요. 수식 인수 구분자는 Excel의 지역 설정에 따라 쉼표 대신 세미콜론을 사용할 수 있습니다.
6. 엑셀 중복 제거 시 자주 발생하는 오류 해결 방법
중복 제거를 실행했는데 예상보다 많은 행이 삭제되거나, 같은 값으로 보이는데도 제거되지 않는 경우가 있습니다. 이런 문제는 비교 열 선택, 데이터 형식, 공백, 정렬 방식 등에서 발생할 수 있습니다.
문제 1. 같은 이름인데 중복 제거가 되지 않는 경우
겉으로 같은 이름처럼 보여도 앞뒤 공백이나 보이지 않는 특수문자가 들어 있을 수 있습니다.
예를 들어 '김민수'와 '김민수 '는 문자 끝에 공백이 있는지에 따라 다른 값으로 처리될 수 있습니다.
일반적인 불필요한 공백은 TRIM 함수로 정리할 수 있습니다.
=TRIM(A2)
다만 TRIM은 일반 공백 문자 중심으로 처리하므로 웹사이트에서 복사한 특수 공백이 그대로 남을 수 있습니다.
문제 2. 웹에서 복사한 보이지 않는 공백이 남는 경우
웹페이지에서 복사한 텍스트에는 일반 공백이 아닌 특수 공백 문자가 포함될 수 있습니다.
대표적인 예가 문자 코드 160의 줄 바꿈 없는 공백입니다. 다음 수식은 해당 문자를 일반 공백으로 바꾼 후 TRIM으로 정리합니다.
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
정리한 값을 실제 데이터에 반영하려면 결과를 복사한 뒤 필요한 위치에 값 붙여넣기를 사용하세요. 원본 데이터는 먼저 백업해 두는 것이 좋습니다.
문제 3. 숫자와 문자 형식이 섞인 경우
숫자 1001이 실제 숫자로 저장된 셀과 '1001'이라는 텍스트로 저장된 셀은 겉으로 같아 보일 수 있습니다.
그러나 일부 비교 함수나 데이터 처리 기능에서는 서로 다른 형식이 예상과 다른 결과를 만들 수 있습니다. 따라서 비교 기준 열의 자료형을 일관되게 맞추는 것이 좋습니다.
특히 전화번호와 우편번호, 회원코드처럼 앞자리 0이 중요한 값은 처음부터 텍스트로 관리하세요.
문제 4. 삭제 후 고객 정보가 서로 어긋난 경우
이름 열만 선택해 정렬하거나 데이터 범위 중 일부를 독립적으로 이동했다면 전화번호와 가입일이 잘못 연결될 수 있습니다.
이 문제는 단순한 중복 삭제 오류보다 심각합니다. 고객 이름과 연락처 등 서로 다른 사람의 정보가 한 행에 섞일 수 있기 때문입니다.
해결하려면 원본 백업으로 돌아가 전체 표를 함께 정렬하고, 전체 범위에 대해 중복 제거를 실행하세요.
문제 5. 예상보다 너무 많은 행이 삭제된 경우
중복 제거 대화상자에서 비교할 열을 잘못 선택했을 수 있습니다.
예를 들어 고객명만 기준으로 삼으면 동명이인까지 중복으로 분류될 수 있습니다. 상품명만 선택하면 여러 거래에 등장하는 정상적인 상품 기록이 삭제될 수도 있습니다.
삭제 결과가 예상과 크게 다르면 저장 전에 실행 취소를 시도하고 비교 열을 다시 선택하세요.
문제 6. 조건부 서식 색상이 계속 남아 있는 경우
중복 제거 후에도 일부 셀에 색상이 남아 있다면 조건부 서식 규칙이 남아 있거나 일반 채우기 색상이 적용되어 있을 수 있습니다.
홈 → 조건부 서식 → 규칙 관리에서 적용 범위와 조건을 확인하세요.
조건부 서식을 완전히 제거하고 싶다면 규칙 지우기를 사용할 수 있지만, 해당 규칙만 삭제해야 할 경우 전체 시트 규칙을 한꺼번에 지우지 않도록 주의하세요.
문제 7. UNIQUE 함수에 #SPILL! 오류가 나오는 경우
UNIQUE 결과가 표시될 영역에 다른 값이 들어 있거나 병합된 셀이 있으면 동적 배열이 펼쳐지지 못할 수 있습니다.
결과 영역을 비우고 병합된 셀이 없는 위치에서 수식을 실행해 보세요.
UNIQUE를 지원하지 않는 Excel 버전이라면 함수 자체가 인식되지 않을 수 있으므로 고급 필터 또는 중복 제거 기능을 사용할 수 있습니다.
문제 8. 수식은 다른데 중복으로 삭제된 경우
중복 제거는 수식의 글자 자체보다 셀에 표시되는 계산 결과를 기준으로 중복을 판단할 수 있습니다.
예를 들어 한 셀의 수식이 =2-1, 다른 셀의 수식이 =3-2라면 두 셀의 계산 결과가 모두 1입니다.
수식이 다르다는 이유만으로 반드시 별개 데이터로 유지되지는 않으므로 계산식 자체를 보존해야 하는 자료라면 중복 제거 기능을 신중하게 사용하세요.
| 문제 | 가능한 원인 | 해결 방법 |
|---|---|---|
| 같은 값이 남아 있음 | 공백·특수문자·형식 차이 | TRIM·SUBSTITUTE 등으로 정리 |
| 행이 지나치게 삭제됨 | 비교 열 선택 오류 | 실행 취소·조건 재설정 |
| 데이터 열 관계가 틀어짐 | 부분 정렬·부분 범위 처리 | 원본 복원 후 전체 표 정렬 |
| 중복 색상이 남음 | 조건부 서식 규칙 유지 | 규칙 관리에서 범위·조건 확인 |
| UNIQUE 오류 | 결과 영역에 기존 데이터 | 빈 영역 확보 |
| 수식 행이 삭제됨 | 계산 결과 기준 중복 판단 | 수식 보존 필요성 검토 |
💡 오류 해결 순서: 비교 열 → 전체 표 범위 → 공백·특수문자 → 데이터 형식 → 정렬 순서 → 조건부 서식 규칙을 차례대로 확인하세요. 문제가 심하면 원본 백업에서 다시 작업하는 것이 가장 안전합니다.
7. 엑셀 중복된 항목 제거 및 조건부 서식 자주 묻는 질문 8가지
중복 여부를 확인할 셀 범위를 선택하고 홈 → 조건부 서식 → 셀 강조 표시 규칙 → 중복 값을 사용하면 됩니다. 선택 범위에서 반복되는 값을 색상으로 강조하므로 쉽게 확인할 수 있습니다.
아닙니다. 조건부 서식은 해당 셀에 색상이나 글꼴 등의 시각적 서식을 적용하는 기능입니다. 실제 데이터를 삭제하려면 데이터 → 중복된 항목 제거 기능을 별도로 실행해야 합니다.
가능합니다. 중복 제거 대화상자에서 이름과 전화번호처럼 두 개 이상의 열을 체크하면 선택한 열들의 값 조합을 기준으로 중복을 판단합니다.
일반적으로 선택된 중복 기준에 대해 목록에서 처음 나타난 행이 유지됩니다. 최신 정보를 남기려면 삭제 전에 전체 표를 날짜 기준 최신순으로 정렬하세요.
COUNTIF 함수를 사용하면 됩니다. 예를 들어 =COUNTIF($A$2:$A$101,A2)는 A2 값이 A2:A101 범위에 몇 번 나타나는지 계산합니다.
네. 지원되는 Excel 버전에서는 UNIQUE 함수를 이용할 수 있습니다. 예를 들어 =UNIQUE(A2:A101)은 원본을 유지하면서 고유값 목록을 반환합니다. 구형 버전에서는 고급 필터의 고유 레코드 복사 기능을 검토할 수 있습니다.
삭제 직후에는 실행 취소 기능을 사용할 수 있는 경우가 있습니다. 하지만 파일을 닫거나 후속 작업을 진행하면 복구가 어려울 수 있으므로 원본 시트나 파일을 미리 복사해 두는 것이 좋습니다.
앞뒤 공백, 특수 공백, 보이지 않는 문자, 데이터 형식 차이 등이 원인일 수 있습니다. TRIM이나 SUBSTITUTE 같은 함수로 데이터를 정규화한 뒤 비교하세요. 이름이 같아도 실제로 다른 사람일 수 있으므로 삭제 기준은 별도로 확인해야 합니다.
8. 엑셀 중복 데이터 정리 최종 체크리스트
| 확인 항목 | 최종 점검 내용 |
|---|---|
| 원본 백업 | 삭제 전 원본 시트 또는 파일 복사 |
| 데이터 범위 | 관련 열과 행을 모두 포함했는가? |
| 머리글 확인 | 첫 행을 데이터로 잘못 인식하지 않는가? |
| 중복 기준 | 고객 ID·주문번호 등 적절한 열 선택 |
| 조건부 서식 | 중복값을 색상으로 표시해 검토 |
| 동명이인 검토 | 같은 이름이 다른 사람일 가능성 확인 |
| 정렬 순서 | 최신·최초 중 어떤 행을 남길지 결정 |
| 중복 제거 설정 | 비교 열 체크 상태 재확인 |
| 삭제 건수 | 예상한 중복 행 수와 일치하는가? |
| 데이터 형식 | 공백·숫자·텍스트 형식 확인 |
| 수식·집계 | 합계·참조 수식 정상 여부 검토 |
| 최종 저장 | 정리본을 별도 이름으로 저장 |
중복 제거와 조건부 서식, 언제 어떤 기능을 선택할까요?
데이터를 삭제하지 않고 문제 항목만 확인하려면 조건부 서식을 사용하세요. 중복된 값이 어디에 있는지 색으로 강조해 주므로 검토 작업에 적합합니다.
반면 중복된 행을 실제로 없애야 한다면 데이터 탭의 중복된 항목 제거 기능을 사용할 수 있습니다. 다만 삭제 기준이 명확해야 하고 원본 복사본을 준비해야 합니다.
원본을 유지하면서 다른 곳에 중복 없는 목록을 만들고 싶다면 UNIQUE 함수나 고급 필터의 고유 레코드 복사 기능을 사용하는 것이 좋습니다.
반복해서 입력되는 데이터의 중복 관리 요령
고객 명단이나 재고 목록처럼 지속적으로 데이터가 추가되는 파일이라면 중복 제거를 한 번 실행하는 것으로 끝나지 않습니다.
입력 기준 열을 정하고 표 형식으로 관리하며, 필요한 경우 조건부 서식이나 COUNTIF 함수를 설정해 새로운 중복 항목이 추가될 때 쉽게 확인할 수 있도록 하세요.
다만 자동 강조는 검토를 돕는 기능이며, 서로 다른 거래를 중복으로 잘못 판단하지 않도록 실제 업무 규칙과 함께 사용해야 합니다.
중복된 항목 제거 전 꼭 확인할 세 가지
첫째, 중복 기준입니다. 이름만 같은 것인지, 이름과 전화번호가 모두 같은 것인지, 주문번호와 상품 코드까지 같은 것인지 정해야 합니다.
둘째, 남길 기록의 우선순위입니다. 최신 등록 정보인지 최초 정보인지 결정하고 전체 표를 올바르게 정렬해야 합니다.
셋째, 결과 검증입니다. 제거된 행의 수가 예상 범위인지 확인하고, 총매출이나 고객 수처럼 중요한 집계가 의도대로 변경되었는지 확인해야 합니다.
최종 요약
엑셀에서 중복된 항목을 정리하려면 먼저 원본 데이터를 복사하고 조건부 서식으로 중복값을 강조하는 것이 좋습니다.
그다음 중복의 기준이 되는 열을 결정하고, 남길 행의 우선순위에 따라 전체 표를 정렬합니다. 데이터 → 중복된 항목 제거에서 비교 열을 정확하게 선택하면 중복으로 판단된 뒤쪽 행을 삭제할 수 있습니다.
중복 데이터를 삭제하지 않고 분석하고 싶다면 COUNTIF와 COUNTIFS로 발생 횟수를 계산하거나 UNIQUE로 고유 목록을 만들 수 있습니다.
최종 결론: 엑셀 중복 데이터는 조건부 서식으로 먼저 찾고, 여러 열의 조합과 유지할 기록을 검토한 뒤 중복된 항목 제거 기능으로 정리하세요. 원본 백업과 결과 검증까지 완료해야 안전하고 정확한 데이터 정리가 됩니다.
Microsoft Excel 중복 데이터 정리 공식 참고 자료
아래 자료는 Microsoft 공식 Excel 도움말입니다. 조건부 서식으로 중복을 강조하는 방법, 실제 중복 행 제거, 고유값 필터링 및 함수 활용 원리를 확인할 수 있습니다.
중복값 찾기와 제거
Microsoft 지원 — 중복 값 찾아 제거하기: 조건부 서식과 데이터 중복 제거
Microsoft 지원 — 고유 값 필터링 또는 중복 값 제거: 삭제 범위와 비교 열 선택
Microsoft 지원 — Mac Excel 중복값 필터링·제거 안내
조건부 서식과 데이터 표시
Microsoft 지원 — Excel에서 조건부 서식으로 정보 강조 표시하기
중복 관리와 함수 관련 공식 자료
Microsoft 365 고객 지원 — Excel 함수와 데이터 관리 도움말
※ 2026년 11월 기준 Microsoft 공식 도움말을 참고한 일반적인 사용 방법입니다. Excel의 Windows, Mac, 웹 버전과 라이선스에 따라 메뉴 및 지원 함수가 다를 수 있습니다. '중복된 항목 제거'는 원본 데이터를 실제로 삭제하며, 조건부 서식은 표시만 변경합니다. 실무 데이터에서는 고유 식별자와 기록 보존 정책을 먼저 확인한 뒤 중복 제거 작업을 진행하세요.
댓글
댓글 쓰기