2026 엑셀 중복값 찾기·제거 방법: 조건부 서식·COUNTIF·여러 열 기준과 삭제 전 확인
엑셀에서 고객 명단, 주문 내역, 거래처 목록을 정리하다 보면 같은 이름이나 번호가 여러 번 나타납니다. 이때 중복값을 찾는 것과 중복 행을 삭제하는 것은 완전히 다른 작업입니다. 예를 들어 같은 고객이 두 차례 주문했다면 고객명은 중복이지만 주문 내역은 모두 보존해야
엑셀에서 고객 명단, 주문 내역, 거래처 목록을 정리하다 보면 같은 이름이나 번호가 여러 번 나타납니다. 이때 중복값을 찾는 것과 중복 행을 삭제하는 것은 완전히 다른 작업입니다. 예를 들어 같은 고객이 두 차례 주문했다면 고객명은 중복이지만 주문 내역은 모두 보존해야 합니다.
이 글에서는 Microsoft의 공식 Excel 도움말을 기준으로 조건부 서식으로 중복값 표시 → COUNTIF로 중복 횟수 확인 → 여러 열을 묶어 판정 → 필요한 경우에만 중복 제거 순서로 설명합니다. 예시는 Windows용 Microsoft 365/Excel 2024를 기준으로 하며, Excel 버전과 화면 언어에 따라 메뉴명이 조금 다를 수 있습니다.
1. 내 상황에 맞는 중복값 처리 방법부터 고르기
| 하고 싶은 일 | 추천 기능 | 기존 데이터 삭제 |
|---|---|---|
| 같은 값이 어디 있는지 눈으로 확인 | 홈 → 조건부 서식 → 중복 값 | 없음 |
| 동일한 값이 총 몇 개인지 확인 | COUNTIF 함수 | 없음 |
| 첫 번째 기록은 남기고 그다음 반복만 표시 | 누적 COUNTIF 함수 | 없음 |
| 주문번호와 상품코드가 모두 같은 행만 찾기 | COUNTIFS 함수 | 없음 |
| 중복 행을 실제로 정리 | 데이터 → 중복 제거 | 있음 |
| 원본을 유지하며 고유 목록을 따로 만들기 | UNIQUE 함수 또는 고급 필터 | 원본 삭제 없음 |
가장 안전한 원칙: 중복된 값을 먼저 색으로 확인한 뒤 삭제 여부를 결정하세요. 실제 삭제는 원본 파일을 복사한 다음 진행하는 편이 좋습니다.
2. 예제: 고객명은 중복이어도 주문을 지우면 안 되는 경우
다음 예제처럼 A열에는 주문번호, B열에는 고객명, C열에는 상품코드가 있다고 가정합니다.
| 행 | A: 주문번호 | B: 고객명 | C: 상품코드 |
|---|---|---|---|
| 2 | 1001 | 김하늘 | P01 |
| 3 | 1002 | 박다온 | P02 |
| 4 | 1001 | 김하늘 | P01 |
| 5 | 1003 | 김하늘 | P03 |
| 6 | 1004 | 이서준 | P04 |
- B열 고객명 기준: 김하늘이 세 번 등장합니다. 하지만 5행은 주문번호가 다르므로 무조건 삭제하면 안 됩니다.
- A열 주문번호 기준: 2행과 4행에서 1001이 중복됩니다.
- A열+C열을 함께 기준으로: 2행과 4행은 주문번호와 상품코드 조합이 같습니다.
어떤 열을 중복 판단 기준으로 삼느냐에 따라 삭제 대상이 달라집니다. 업무용 명단에서는 고객명이 아니라 회원 ID·주문번호·사업자등록번호 등 업무상 식별자를 우선 확인해야 합니다. 다만 한 식별자에 여러 정상 기록이 허용되는 업무라면 그것만으로 삭제를 결정하면 안 됩니다.
3. 가장 쉬운 방법: 조건부 서식으로 중복값에 색 표시
- 중복값을 찾을 범위를 선택합니다. 예를 들어 주문번호가 A2부터 A100까지라면 A2:A100을 선택합니다.
- 상단 홈 탭에서 조건부 서식을 엽니다.
- 셀 강조 표시 규칙 → 중복 값을 선택합니다.
- 원하는 강조 색을 선택하고 확인합니다.
- 같은 값이 반복되는 셀에 색이 들어왔는지 확인합니다.
이 기능은 중복을 보여줄 뿐 값을 삭제하지 않습니다. 단, 선택한 범위의 동일 값이 강조되므로 전체 고객명 열을 선택하면 같은 고객의 서로 다른 주문도 함께 표시됩니다.
공식 근거: Microsoft — 중복 값 찾아 제거하기
색을 없애려면?
중복 확인을 마친 뒤 해당 범위를 선택하고 홈 → 조건부 서식 → 규칙 관리에서 관련 규칙을 삭제하거나 수정할 수 있습니다. 단순히 셀 채우기 색만 바꾸면 조건부 서식 규칙이 남아 다시 색이 나타날 수 있습니다.
4. COUNTIF로 중복 횟수 확인하기
A열에 주문번호가 있고 D열을 비워 두었다면 D2 셀에 다음 수식을 넣고 아래로 채웁니다.
=COUNTIF($A$2:$A$100,A2)
이 수식은 A2에 있는 주문번호가 전체 검사 범위 A2:A100에 몇 번 등장하는지 반환합니다.
- 결과가 1이면 검사 범위에서 한 번만 등장한 값입니다.
- 결과가 2 이상이면 반복된 값입니다.
- 아직 입력하지 않은 빈칸까지 포함했다면 빈칸이 중복으로 계산될 수 있습니다.
빈칸을 결과에서 제외하고 싶다면 다음과 같이 작성합니다.
=IF(A2="","",COUNTIF($A$2:$A$100,A2))
주의: 실제 자료의 마지막 행이 100보다 크면 검사 범위를 반드시 늘려야 합니다. 범위를 짧게 잡으면 뒤쪽에서 생긴 중복을 놓칩니다.
공식 근거: Microsoft — COUNTIF 함수 사용
5. 첫 번째 기록만 남기고 두 번째부터 '중복' 표시
삭제하지 않고 추가로 반복된 행만 확인하고 싶을 때 유용한 수식입니다.
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"중복","첫 기록"))
이 수식을 D2에 입력하고 아래로 복사하면 같은 주문번호의 첫 등장에는 '첫 기록', 두 번째 등장부터는 '중복'이 표시됩니다.
앞서 예제라면 1001이 처음 등장하는 2행은 '첫 기록', 다시 등장하는 4행은 '중복'입니다. 이 방법은 목록의 위쪽 행을 우선으로 간주합니다. 가장 최신 기록을 남기려면 날짜·시간 기준으로 전체 표를 정렬한 다음 적용해야 하지만, 실제 어떤 기록을 유지해야 하는지는 업무 기준으로 결정해야 합니다.
6. 이름 하나가 아니라 여러 열 조합으로 중복 확인
주문번호와 상품코드가 모두 같을 때만 반복이라고 판정하려면 두 열을 함께 비교합니다. 다음 수식을 D2에 입력하고 아래로 채우세요.
=IF(OR(A2="",C2=""),"",IF(COUNTIFS($A$2:A2,A2,$C$2:C2,C2)>1,"중복","첫 기록"))
- 주문번호가 같아도 상품코드가 다르면 서로 다른 조합으로 취급됩니다.
- 주문번호와 상품코드가 모두 같은 조합이 두 번째로 나타날 때 '중복'이 출력됩니다.
- A열 또는 C열이 빈 경우에는 수식 결과를 공백으로 둡니다.
이 예제는 두 열 조합의 중복 여부를 확인하는 방식입니다. 같은 주문번호를 기준으로 모든 상품 행을 하나의 주문으로 묶으려는 경우에는 다른 분석 방법이 필요합니다.
공식 근거: Microsoft — COUNTIFS 함수
7. 중복값을 실제로 제거하는 방법: 삭제 전 백업 필수
Microsoft는 중복 제거가 데이터를 삭제하는 작업이므로 원본 복사본을 먼저 보관할 것을 권고합니다.
- 원본 Excel 파일을 다른 이름으로 저장하거나 원본 워크시트를 복제합니다.
- 한 열만 선택하기보다 제목 행을 포함한 전체 데이터 표 안의 셀을 클릭합니다. 필요하면 전체 표 범위를 직접 지정합니다.
- 데이터 → 중복 제거를 클릭합니다.
- 표의 첫 행이 열 제목이라면 '내 데이터에 머리글 표시'를 알맞게 설정합니다.
- 중복 판단에 사용할 열을 선택합니다.
- 주문번호만 같으면 중복으로 볼 경우: 주문번호만 체크
- 주문번호와 상품코드 조합이 같아야 중복으로 볼 경우: 주문번호와 상품코드를 모두 체크
- 확인 후 표시되는 제거된 중복 개수와 남은 고유 값 개수를 확인합니다.
- 원본과 비교해 행 수, 주문금액 합계, 남아 있는 주요 기록이 의도대로 유지됐는지 점검합니다.
꼭 알아둘 점: '중복 제거'의 열 선택은 어떤 열을 삭제할지가 아니라 어떤 열들의 값으로 동일한 기록을 판단할지를 정하는 것입니다. 중복으로 판단되면 선택 범위의 해당 행에 있는 다른 열 데이터도 함께 제거됩니다. 또한 Excel은 원래 목록의 첫 번째 일치 항목을 남기고 나중에 나타난 중복 항목을 제거합니다.
8. 원본을 삭제하지 않고 고유값 목록만 만들기
Microsoft 365 또는 Excel 2021/2024 등 UNIQUE 함수를 지원하는 Excel이라면 별도 셀에 다음 수식을 사용할 수 있습니다.
=UNIQUE(A2:A100)
이 수식은 A2:A100 범위에서 중복을 제거한 고유 목록을 동적 배열로 보여줍니다. 고유한 전체 행 조합을 보고 싶다면 다음처럼 여러 열을 지정합니다.
=UNIQUE(A2:C100)
단, 이것은 행의 A·B·C 세 값이 모두 같은 경우를 하나로 보는 방식입니다. 주문번호만 고유해야 한다면 주문번호 열만 선택하는 편이 목적에 맞습니다.
UNIQUE 함수가 없는 버전에서는 데이터 → 고급 필터 → 고유 레코드만을 사용하고, 필요하다면 다른 위치에 복사하도록 설정할 수 있습니다. 함수가 지원되지 않을 때 #NAME? 오류가 나타날 수 있습니다.
공식 근거: Microsoft — UNIQUE 함수
9. 중복인데 다르게 보이거나, 다른 값인데 중복으로 잡히는 이유
① 앞뒤 공백 또는 보이지 않는 문자
'김하늘'과 '김하늘 '은 눈으로는 같아 보여도 저장된 문자열이 다를 수 있습니다. 복사·붙여넣기한 자료라면 공백과 숨은 문자를 먼저 정리해야 합니다. TRIM·CLEAN 함수를 이용해 정리한 보조 열을 만들고 결과를 눈으로 검증하는 방법이 있습니다. 단, 일부 특수 공백은 TRIM만으로 정리되지 않을 수 있습니다.
② 숫자처럼 보이지만 실제로는 텍스트
주문번호 '00123'을 숫자 123으로 바꾸면 앞의 0이 없어집니다. 전화번호, 코드, 제품 번호와 같이 앞자리 0이 의미가 있는 값은 텍스트로 보관하세요. 일부 열에 숫자와 텍스트가 섞여 있으면 중복 판정 전에 데이터 형식을 통일할 필요가 있습니다.
③ 대소문자를 엄격하게 구분해야 하는 코드
COUNTIF는 텍스트 비교 시 일반적으로 영문 대소문자를 구분하지 않습니다. 예를 들어 업무상 Ab1과 ab1을 다른 코드로 봐야 한다면 단순 COUNTIF 중복 판정만으로는 적합하지 않습니다. 별도 대소문자 구분 비교가 필요합니다.
④ 중복된 고객명과 중복된 거래는 다릅니다
같은 고객이 구매한 두 행을 고객명 기준으로 지우면 정상 주문이 사라질 수 있습니다. 삭제 전에는 중복 판단 기준 열, 유지해야 할 행, 제거할 행을 표본으로 직접 검증해야 합니다.
10. 자주 묻는 질문
중복 제거 버튼을 누르면 나중에 복구할 수 있나요?
실행 직후에는 Ctrl+Z로 되돌릴 수 있는 경우가 있습니다. 다만 파일을 닫거나 후속 작업을 수행하면 복구 가능 여부가 달라질 수 있으므로 실행 취소에 의존하지 말고 별도 복사본을 준비하는 것이 안전합니다.
중복값에 색만 표시하고 싶어요.
홈 → 조건부 서식 → 중복 값 기능을 사용하면 됩니다. 값 자체는 삭제되지 않습니다.
두 개 이상의 열이 모두 일치하는 경우만 지우려면?
데이터 → 중복 제거에서 두 열을 모두 선택합니다. 미리 COUNTIFS로 반복 여부를 검사한 다음 제거하면 더 안전합니다.
첫 행이 아니라 가장 최신 행을 남기고 싶어요.
날짜·시간 열로 표 전체를 정렬하고 보존할 행이 첫 번째에 오도록 만든 뒤 중복 제거를 고려합니다. 날짜가 같거나 보존 기준이 복잡한 자료는 자동 삭제보다 검토용 표시 열을 만드는 편이 안전합니다.
중복값을 삭제하지 않고 개수만 확인하려면?
=COUNTIF($A$2:$A$100,A2)를 사용하세요. 두 열 이상의 조건을 동시에 적용하려면 COUNTIFS 함수를 이용할 수 있습니다.
11. 핵심 체크리스트
- ☐ 중복 기준이 고객명인지, 주문번호인지, 여러 열 조합인지 정했다.
- ☐ 원본 파일을 별도로 복사했다.
- ☐ 조건부 서식이나 COUNTIF로 먼저 중복 후보를 확인했다.
- ☐ 머리글과 전체 데이터 범위를 올바르게 선택했다.
- ☐ 삭제 후 남은 건수와 중요한 합계·기록을 다시 확인했다.
결론: 중복값을 찾는 작업은 조건부 서식이나 COUNTIF로 시작하세요. 삭제가 꼭 필요한 때만 전체 표와 중복 판단 열을 정확히 지정한 다음 데이터 → 중복 제거를 사용하면 불필요한 데이터 손실을 줄일 수 있습니다.
공식 참고자료
함께 읽으면 좋은 같은 분야의 글
정보 출처와 확인 안내
작성·갱신일과 적용 범위는 본문에서 확인하세요. 정책·행사 일정·혜택·제품 정보는 변경될 수 있으므로 중요한 판단 전 공식 기관 및 판매처의 최신 안내를 확인하시기 바랍니다.