엑셀에서 주문번호, 상품코드, 고객 이메일을 정리하다 보면 같은 값이 여러 번 입력된 행을 발견하게 됩니다.
중복값을 처리할 때 가장 중요한 것은 바로 삭제하지 않는 것입니다. 같은 고객의 여러 주문처럼 반복되어도 정상인 데이터가 있고, 거래번호처럼 한 번만 존재해야 하는 데이터도 있기 때문입니다.
이 글에서는 중복값을 표시만 하는 방법, 반복 횟수를 확인하는 방법, 실제 중복 행을 삭제하는 방법, 원본을 유지한 고유 목록을 만드는 방법을 서로 구분합니다.
 |
| 중복값을 확인하는 작업, 중복 행을 삭제하는 작업, 원본을 유지하면서 고유 목록을 만드는 작업은 서로 다릅니다. 위 이미지는 세 가지 처리 결과의 차이를 설명하기 위한 대표 개념 이미지입니다. |
중복값을 처리하기 전에 목적부터 구분합니다
| 필요한 결과 | 사용할 기능 | 원본 데이터 |
|---|---|---|
| 중복된 위치만 확인 | 조건부 서식 | 변경되지 않음 |
| 같은 값의 반복 횟수 확인 | COUNTIF | 보조 열만 추가 |
| 중복된 행을 실제로 삭제 | 중복 제거 | 중복 행이 삭제됨 |
| 원본을 유지한 고유 목록 생성 | UNIQUE | 변경되지 않음 |
고객명이나 상품명은 정상적으로 여러 번 나타날 수 있습니다. 반면 주문번호, 거래번호 또는 사번처럼 고유해야 하는 값은 중복 여부를 별도로 확인해야 합니다.
중복값을 삭제하지 않고 색으로 표시하기
어떤 값이 반복되는지 먼저 검토하려면 조건부 서식을 사용합니다. 이 기능은 값을 삭제하지 않고 선택한 범위에서 중복된 셀을 표시합니다.
- 머리글을 제외한 데이터 범위를 선택합니다.
- 홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값을 선택합니다.
- 중복값에 사용할 표시 형식을 선택합니다.
- 확인을 선택합니다.
예를 들어 주문번호가 A열에 있다면
A2:A1000 범위만 선택합니다. 표 전체를 선택하면 고객명이나
금액처럼 정상적으로 반복되는 값도 함께 표시될 수 있습니다.
빈 셀을 제외하고 중복값만 표시하기
선택 범위에 빈 셀이 많다면 조건부 서식의 수식 규칙을 사용할 수 있습니다.
A2:A1000을 선택하고 다음 수식을 지정합니다.
=AND(A2<>"",COUNTIF($A$2:$A$1000,A2)>1)현재 셀이 비어 있지 않고 같은 값이 선택 범위에서 두 번 이상 나타날 때만 서식이 적용됩니다.
첫 번째 값은 남기고 두 번째부터 표시하기
최초 입력은 정상으로 두고 이후에 다시 입력된 행만 표시하려면 누적 범위를 사용합니다.
=AND(A2<>"",COUNTIF($A$2:A2,A2)>1)
범위의 시작점인 $A$2는 고정되고 마지막 셀은 A2, A3, A4처럼
이동합니다. 따라서 같은 값이 두 번째로 나타나는 행부터 표시됩니다.
COUNTIF로 같은 값이 몇 번 있는지 확인하기
색상 표시 대신 반복 횟수를 숫자로 확인하려면 보조 열을 추가합니다. 주문번호가 A열에 있을 때 다음 수식을 입력하고 아래로 복사합니다.
=IF(A2="","",COUNTIF($A$2:$A$1000,A2))결과가 1이면 해당 값은 한 번만 나타나고, 2 이상이면 같은 값이 여러 행에 존재합니다.
주문번호만 같다고 모든 행을 바로 삭제해서는 안 됩니다. 날짜, 금액 또는 상태가 서로 다른 행이라면 정상적인 수정 이력이나 분할 처리 기록일 수 있습니다.
중복 제거로 실제 행을 삭제하기
중복 제거는 표시만 바꾸는 기능이 아니라 데이터 행을 실제로 삭제합니다. 원본 범위나 통합 문서를 다른 위치에 복사한 뒤 진행합니다.
- 머리글을 포함한 데이터 표 안의 셀을 선택합니다.
- 데이터 → 중복 제거를 선택합니다.
- 머리글 포함 여부를 확인합니다.
- 중복을 판단할 열만 선택합니다.
- 확인을 선택합니다.
중복 제거 창에서 선택한 열은 삭제할 열이 아니라 중복 여부를 비교할 기준입니다.
예를 들어 주문번호 열만 선택하면 주문번호가 같은 행을 중복으로 판단합니다. 고객명과 주문일을 함께 선택하면 두 값이 모두 같은 행만 중복으로 판단합니다.
중복이 발견되면 선택하지 않은 다른 열의 값도 해당 행과 함께 삭제됩니다. 금액이나 비고가 다른 행을 유지해야 한다면 기준 열을 확정하기 전에 전체 행을 먼저 비교해야 합니다.
최신 행을 남기려면 정렬을 먼저 합니다
중복 제거는 현재 목록에서 처음 나타난 항목을 남기고 이후 같은 항목을 삭제합니다.
가장 최근 수정 기록을 남겨야 한다면 날짜 열을 최신순으로 정렬한 뒤 중복 제거를 실행합니다. 최초 등록 기록을 남겨야 한다면 오래된 날짜가 위쪽에 오도록 정렬합니다.
날짜가 실제 Excel 날짜가 아니라 문자로 저장돼 있다면 정렬 순서가 예상과 다를 수 있으므로 날짜 형식도 확인합니다.
원본을 유지하려면 UNIQUE로 고유 목록 만들기
중복 행을 삭제하지 않고 별도의 고유 목록만 필요하다면
UNIQUE 함수를 사용할 수 있습니다.
A열 주문번호에서 중복 없이 서로 다른 값만 반환하는 수식입니다.
=UNIQUE(A2:A1000)고유 목록을 오름차순으로 정렬하려면 다음처럼 작성합니다.
=SORT(UNIQUE(A2:A1000))주문번호뿐 아니라 A열부터 D열까지 모든 값이 같은 중복 행을 하나로 줄이려면 여러 열을 함께 지정합니다.
=UNIQUE(A2:D1000)한 열이라도 값이 다르면 서로 다른 행으로 유지됩니다.
UNIQUE는 여러 결과를 주변 셀에 펼치는 동적 배열 함수입니다. 결과가 표시될
아래쪽이나 오른쪽 셀에 다른 값 또는 병합 셀이 있으면
#SPILL! 오류가 발생할 수 있습니다.
수식은 결과가 펼쳐질 공간이 비어 있는 일반 셀에 입력합니다. Excel 표 내부에서는 동적 배열 결과가 펼쳐지지 않으므로 표 밖의 영역이나 다른 워크시트를 사용합니다.
겉보기에는 같은데 중복으로 인식되지 않는 경우
화면에서는 같은 코드로 보이지만 한쪽 값 뒤에 공백이 있거나, 한쪽은 숫자이고 다른 쪽은 문자로 저장돼 있으면 서로 다른 값으로 처리될 수 있습니다.
문자 수를 비교하면 숨은 공백 여부를 확인하는 데 도움이 됩니다.
=LEN(A2)
원본을 바로 바꾸지 말고 보조 열에서 TRIM이나 데이터 형식 변환
결과를 확인한 뒤 중복 검사를 다시 실행합니다.
세 가지 판단만 지키면 데이터 손실을 줄일 수 있습니다
첫째, 반복값이 오류인지 정상 기록인지 먼저 판단합니다. 둘째, 삭제 전에는 조건부 서식이나 COUNTIF로 대상 행을 확인합니다. 셋째, 원본을 유지해야 한다면 중복 제거가 아니라 UNIQUE를 사용합니다.
내용 확인에 사용한 Microsoft 공식 자료
공식 문서 확인일: 2026년 7월 31일
댓글 쓰기