엑셀 중복값 찾기와 삭제하는 방법

엑셀에서 고객 명단이나 주문 내역을 정리하다 보면 같은 데이터가 여러 번 들어간 경우가 있습니다. 일일이 눈으로 확인할 필요는 없어요. 엑셀에는 중복값을 색으로 표시하거나, 중복된 행을 정리하는 기능이 기본으로 들어 있습니다.

먼저 중복된 내용을 확인한 뒤 필요할 때만 삭제하는 게 안전합니다. 아래 방법은 Microsoft 365 및 최근 데스크톱 엑셀을 기준으로 설명합니다. 사용 중인 버전에 따라 메뉴 이름이나 위치는 조금 다를 수 있습니다.

1. 중복값을 색으로 찾아보기

삭제하기 전에 어떤 값이 겹치는지 보고 싶다면 조건부 서식이 가장 간단합니다.

같은 값이 두 번 이상 있으면 해당 셀에 색이 표시됩니다. 처음 입력된 값까지 함께 표시되며, 이 단계에서는 데이터가 삭제되지 않습니다.

색 표시만 없애려면 선택한 범위에서 홈 → 조건부 서식 → 규칙 지우기 → 선택한 셀의 규칙 지우기를 사용하면 됩니다.

중복값을 확인했고 불필요한 기록이라는 게 확실하다면 데이터 → 중복 제거 기능을 사용하면 됩니다.

먼저 원본 시트를 복사해 두세요. 중복 제거는 단순히 색을 지우는 기능이 아니라 행을 삭제하는 작업입니다.

예제의 주문 내역은 12행이고, 주문번호가 반복된 기록이 4행입니다. 주문번호 기준으로 중복을 제거하면 8행이 남습니다. 중복된 값이 여러 개라면 일반적으로 먼저 나오는 기록을 남기고 뒤의 중복 행을 삭제합니다.

주의할 점: 중복 제거 창에서 기준으로 선택하지 않은 열도 해당 중복 행과 함께 삭제됩니다. 예를 들어 주문번호만 선택했다고 해서 주문번호 셀만 지워지는 것은 아닙니다. 한 주문에 상품이 여러 줄로 기록되는 장부라면 주문번호만으로 중복을 제거하면 안 됩니다.

어떤 값이 몇 번 나왔는지 숫자로 확인하려면 COUNTIF 함수를 쓰면 됩니다. 예제 파일에서는 주문번호가 A5부터 A16까지 들어 있습니다.

=COUNTIF($A$5:$A$16,A5)

이 수식을 E5 셀에 입력하고 아래로 복사하면 각 주문번호가 몇 번 등장했는지 표시됩니다. 결과가 1이면 한 번만 등장한 값이고, 2 이상이면 중복된 값입니다.

횟수 대신 ‘중복’ 또는 ‘고유’라고 표시하려면 다음 수식을 사용하면 됩니다.

=IF(COUNTIF($A$5:$A$16,A5)>1,"중복","고유")

이 방식은 같은 값이 있는 모든 행에 중복이라고 표시합니다. 첫 번째 기록은 남겨두고 두 번째부터 ‘제거 후보’로 표시하고 싶다면 범위 시작점만 고정하세요.

=IF(COUNTIF($A$5:A5,A5)=1,"유지","제거 후보")

이 수식을 아래로 복사하면 처음 등장한 주문번호는 ‘유지’, 그다음부터는 ‘제거 후보’로 표시됩니다. 정렬 순서를 바꾸면 어떤 기록이 첫 번째인지도 달라진다는 점은 기억해 두세요.

겉보기에 같은 이름인데 중복으로 표시되지 않는다면 셀 앞뒤에 공백이 들어가 있는지 확인해 보세요. 예를 들어 ‘김민서’와 ‘김민서 ‘는 뒤에 공백이 있는 서로 다른 텍스트입니다.

텍스트의 앞뒤 공백이 의심된다면 다른 열에 =TRIM(B5)를 입력해 결과를 비교해 볼 수 있습니다. 복사해서 붙여넣은 자료라면 눈에 보이지 않는 문자가 섞여 있을 수도 있습니다.

참고로 COUNTIF는 영문 대문자와 소문자를 구별하지 않습니다. 대소문자까지 엄격하게 구분해야 하는 데이터라면 별도의 비교 방법이 필요합니다.

대부분은 조건부 서식으로 확인 → 필요한 경우에만 중복 제거 순서로 진행하면 충분합니다. 주문, 입금, 재고처럼 기록 하나하나가 중요한 표에서는 무조건 삭제하지 말고, 무엇을 중복으로 볼지부터 결정하는 게 좋습니다.

직접 해볼 수 있도록 주문번호가 일부 중복된 엑셀 연습 파일을 준비했습니다. ‘중복 확인 연습’, ‘중복 제거 결과’, ‘사용 방법’ 시트가 들어 있습니다.

아니요. 같은 값 중 첫 번째 기록은 남기고 뒤의 중복 기록을 제거합니다. 다만 정렬 상태에 따라 남는 행이 달라질 수 있습니다.

작업 직후라면 Ctrl+Z로 실행을 취소할 수 있습니다. 다른 작업을 많이 진행했거나 파일을 다시 열었다면 복구가 어려울 수 있으니 원본을 복사해 두는 편이 안전합니다.

아니요. 조건부 서식은 색으로 표시하는 기능입니다. 데이터는 그대로 남아 있습니다.