엑셀 중복값 찾기·표시·삭제 방법, 원본 데이터 보존까지

엑셀에서 고객 명단이나 연락처, 거래내역을 정리하다 보면 같은 데이터가 여러 번 입력된 경우가 있습니다.

예를 들어 동일한 전화번호가 두 번 등록되어 있거나, 같은 상품명이 여러 행에 반복되어 나타나는 상황입니다.

데이터가 몇 개 없다면 직접 확인할 수 있지만 수백 개, 수천 개의 자료가 있다면 중복값을 일일이 찾아내기 어렵습니다.

다행히 엑셀에는 중복된 데이터를 자동으로 찾아 색상을 표시하거나, 중복된 항목을 제거하는 기능이 있습니다.

가장 간단한 방법은 '조건부 서식'으로 중복값을 표시하는 것이고, 실제로 데이터를 정리하려면 '데이터 → 중복 제거' 기능을 이용하면 됩니다.

다만 중복 제거 기능은 데이터를 실제로 삭제하므로 원본을 보존하고 어떤 열을 기준으로 중복을 판단할지 먼저 결정해야 합니다.

엑셀 중복값을 안전하게 찾고 정리하는 방법을 알아보겠습니다.

 

1. 엑셀에서 중복값이란 무엇일까?

중복값은 같은 데이터가 두 번 이상 반복해서 나타나는 것을 말합니다.

예를 들어 A열에 다음과 같은 이름이 있다고 가정하겠습니다.

김민수
이영희
박철수
김민수
최지연
이영희

이 목록에서는 김민수와 이영희가 각각 두 번 나타납니다.

하지만 이름이 같다고 해서 반드시 같은 사람이라고 판단할 수는 없습니다.

고객 명단이라면 이름은 같아도 전화번호가 다를 수 있고, 거래내역이라면 동일한 고객이 여러 번 주문했을 수도 있습니다.

따라서 중복값을 찾는 것과 실제로 삭제해야 할 데이터를 판단하는 것은 서로 다른 작업입니다.

단순한 상품명 목록이라면 상품명을 기준으로 중복을 확인할 수 있지만, 고객정보라면 전화번호나 고객번호 등을 함께 비교하는 것이 좋습니다.

2. 조건부 서식으로 중복값 찾아 색상 표시하기

엑셀에서 중복값을 가장 쉽게 찾는 방법은 조건부 서식을 사용하는 것입니다.

조건부 서식은 특정 조건에 해당하는 셀의 배경색이나 글자색을 자동으로 변경하는 기능입니다.

이 기능을 이용하면 중복된 값을 한눈에 확인할 수 있습니다.

설정 방법은 다음과 같습니다.

① 중복값을 찾으려는 셀 범위를 선택합니다.

② 상단의 '홈' 메뉴를 클릭합니다.

③ '조건부 서식'을 선택합니다.

④ '셀 강조 규칙 → 중복 값'을 클릭합니다.

⑤ 원하는 표시 색상을 선택합니다.

⑥ 확인을 누릅니다.

예를 들어 A2부터 A100까지 이름이 입력되어 있다면 해당 범위를 선택한 뒤 중복값 표시 기능을 적용하면 됩니다.

중복된 이름이 들어 있는 셀에 자동으로 색상이 표시됩니다.

이 방법의 가장 큰 장점은 실제 데이터를 삭제하지 않는다는 것입니다.

중복값이 있는지 확인하고 직접 비교할 수 있으므로 처음 자료를 정리할 때 사용하는 것이 좋습니다.

또한 중복값 표시를 적용한 상태에서 데이터를 수정하면 조건에 따라 색상도 변경됩니다.

중복된 값이 더 이상 존재하지 않으면 해당 강조 표시가 사라질 수 있습니다.

 

3. COUNTIF 함수로 중복값 개수 확인하기

중복값을 색상으로 표시하는 것뿐 아니라 특정 데이터가 몇 번 반복되는지 확인하고 싶을 때도 있습니다.

이럴 때는 COUNTIF 함수를 사용할 수 있습니다.

COUNTIF는 지정한 범위에서 특정 조건과 일치하는 셀의 개수를 계산하는 함수입니다.

예를 들어 A2부터 A100까지 고객 이름이 입력되어 있다고 가정하겠습니다.

B2 셀에 다음 수식을 입력합니다.

=COUNTIF($A$2:$A$100,A2)

이 수식은 A2에 입력된 이름이 A2부터 A100까지 몇 번 나타나는지 계산합니다.

결과가 1이라면 해당 이름이 한 번만 등장한다는 의미입니다.

결과가 2라면 같은 이름이 두 번 나타난다는 의미입니다.

결과가 3이라면 세 번 나타난 것입니다.

B2에 수식을 입력한 다음 아래쪽으로 복사하면 다른 이름들도 같은 방식으로 확인할 수 있습니다.

이때 수식에 사용된 $ 기호는 검색 범위를 고정하는 역할을 합니다.

수식을 아래로 복사해도 A2부터 A100까지의 범위가 바뀌지 않도록 하는 것입니다.

단, 이름이 비어 있는 셀까지 계산하면 빈 셀의 개수가 표시될 수 있습니다.

빈 셀을 제외하려면 다음 수식을 사용할 수 있습니다.

=IF(A2="","",COUNTIF($A$2:$A$100,A2))

이렇게 하면 이름이 없는 행에는 결과를 표시하지 않습니다.

4. 중복된 데이터에만 '중복' 표시하기

COUNTIF 함수의 결과를 숫자 대신 '중복'이라는 글자로 표시하는 방법도 있습니다.

예를 들어 A열에 전화번호가 입력되어 있고 B열에서 중복 여부를 표시하려는 경우입니다.

B2 셀에 다음 수식을 입력합니다.

=IF(A2="","",IF(COUNTIF($A$2:$A$100,A2)>1,"중복","정상"))

이 수식은 해당 전화번호가 두 번 이상 나타나면 '중복'이라고 표시합니다.

한 번만 나타나는 전화번호는 '정상'이라고 표시합니다.

이렇게 하면 목록에서 어떤 데이터가 중복되었는지 쉽게 확인할 수 있습니다.

하지만 여기서 알아둘 점이 있습니다.

위 수식은 같은 값이 두 번 이상 나타나면 첫 번째 항목과 두 번째 항목을 모두 중복으로 표시합니다.

첫 번째 항목은 유지하고 두 번째부터 표시하고 싶다면 다음 수식을 사용할 수 있습니다.

=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"중복","첫 등장"))

이 수식은 위쪽 행부터 차례대로 확인하기 때문에 같은 값이 처음 나타난 행에는 '첫 등장'을 표시합니다.

두 번째부터는 '중복'이라고 표시합니다.

따라서 기존 자료를 유지하면서 이후에 추가된 중복 항목을 확인할 때 유용합니다.

다만 이 수식은 현재 행 순서를 기준으로 판단합니다. 데이터를 다시 정렬하면 첫 등장으로 표시되는 항목이 달라질 수 있습니다.

 

5. 엑셀 중복값을 한 번에 삭제하는 방법

중복된 데이터를 확인했고 실제로 제거해도 문제가 없다면 엑셀의 중복 제거 기능을 사용할 수 있습니다.

사용 방법은 다음과 같습니다.

① 중복값을 정리할 표 전체를 선택합니다.

② 상단의 '데이터' 메뉴를 클릭합니다.

③ '중복 제거'를 선택합니다.

④ 중복을 판단할 기준 열을 선택합니다.

⑤ 확인을 누릅니다.

작업이 완료되면 엑셀에서 제거한 중복 항목의 개수와 남아 있는 고유 항목의 개수를 안내합니다.

예를 들어 전화번호가 100개 입력되어 있는데 같은 전화번호가 반복되어 있다면 중복 제거 후 중복되지 않는 전화번호만 남길 수 있습니다.

엑셀의 중복 제거 기능은 중복으로 판단된 항목 중 첫 번째 항목을 유지하고 이후의 중복 항목을 제거합니다.

하지만 중복 제거 기능은 실제 데이터를 삭제한다는 점을 반드시 기억해야 합니다.

작업 전에 원본 파일을 별도로 저장하는 것이 좋습니다.

또한 고객명, 전화번호, 주소 등이 여러 열로 구성된 표라면 전화번호 열만 따로 선택하지 말고 관련 열을 포함한 표 전체를 선택하는 것이 안전합니다.

한 열의 데이터만 삭제하거나 변경하면 다른 열의 정보와 서로 어긋나는 문제가 발생할 수 있기 때문입니다.

6. 이름만 같고 전화번호가 다르면 어떻게 해야 할까?

중복 제거 과정에서 가장 주의해야 할 부분입니다.

예를 들어 다음과 같은 고객 명단이 있다고 가정하겠습니다.

김민수 / 010-1111-2222

김민수 / 010-3333-4444

이영희 / 010-5555-6666

이 경우 이름만 기준으로 중복 제거를 실행하면 김민수라는 이름이 하나만 남을 수 있습니다.

하지만 두 사람의 전화번호가 다르므로 실제로는 서로 다른 고객일 가능성이 있습니다.

이런 문제를 예방하려면 두 개 이상의 열을 기준으로 중복 여부를 판단해야 합니다.

① 이름과 전화번호가 포함된 표 전체를 선택합니다.

② '데이터 → 중복 제거'를 클릭합니다.

③ 기준 열에서 '이름'과 '전화번호'를 모두 선택합니다.

④ 확인을 누릅니다.

이렇게 설정하면 이름과 전화번호가 모두 같은 행을 중복으로 판단합니다.

즉, 이름이 같아도 전화번호가 다르다면 별개의 데이터로 유지할 수 있습니다.

다만 전화번호가 같다고 해서 반드시 동일한 사람이라고 단정할 수도 없습니다.

가족이 같은 연락처를 사용하거나 회사 대표번호를 함께 사용하는 경우도 있기 때문입니다.

따라서 중요한 고객정보라면 고객번호나 회원번호처럼 실제로 사람을 구분할 수 있는 기준을 사용하는 것이 좋습니다.

 

7. 가장 최근에 입력한 데이터만 남기는 방법

같은 고객정보가 여러 번 입력되어 있을 때 오래된 자료는 삭제하고 최신 정보만 남기고 싶은 경우도 있습니다.

하지만 엑셀의 중복 제거 기능이 자동으로 가장 최근 자료를 선택해 주는 것은 아닙니다.

기본적으로 현재 표에서 먼저 나타나는 데이터를 유지합니다.

따라서 최신 자료를 남기고 싶다면 중복 제거 전에 날짜순으로 정렬해야 합니다.

예를 들어 고객명, 전화번호, 수정일이 입력된 표가 있다고 가정하겠습니다.

① 먼저 원본 파일을 복사합니다.

② 고객명, 전화번호, 수정일이 포함된 표 전체를 선택합니다.

③ '데이터 → 정렬'을 선택합니다.

④ 수정일을 기준으로 최신 날짜가 위에 오도록 정렬합니다.

⑤ 정렬된 표에서 중복 제거 기능을 실행합니다.

⑥ 고객을 구분할 기준 열을 선택하고 중복을 제거합니다.

이렇게 하면 같은 고객으로 판단된 항목 중 가장 위에 있는 최신 자료를 유지할 수 있습니다.

다만 수정일이 실제로 최신 정보가 입력된 날짜인지 확인해야 합니다.

또한 정렬할 때는 반드시 관련 데이터가 들어 있는 행 전체가 함께 이동하도록 설정해야 합니다.

이름 열만 따로 정렬하면 전화번호나 수정일 정보가 서로 뒤섞일 수 있습니다.

8. 중복값은 제거하되 원본 데이터는 남기는 방법

중복값을 삭제하는 것이 부담스럽다면 원본을 그대로 보존하고 중복되지 않는 목록을 별도로 만드는 방법을 사용할 수 있습니다.

대표적인 방법이 엑셀의 고급 필터 기능입니다.

사용 방법은 다음과 같습니다.

① 중복값이 포함된 데이터 범위를 선택합니다.

② '데이터' 메뉴에서 '고급'을 선택합니다.

③ '다른 장소에 복사'를 선택합니다.

④ 결과를 표시할 위치를 지정합니다.

⑤ '고유 레코드만'에 체크합니다.

⑥ 확인을 누릅니다.

이렇게 하면 원본을 변경하지 않고 중복되지 않는 데이터 목록을 다른 위치에 만들 수 있습니다.

단, 고급 필터에서도 어떤 열을 선택했는지에 따라 고유 레코드를 판단하는 기준이 달라집니다.

이름과 전화번호를 함께 선택했다면 두 값의 조합이 같은 행을 중복으로 판단합니다.

단순히 중복되지 않는 전화번호 목록만 필요하다면 전화번호 열만 대상으로 작업할 수도 있습니다.

원본을 보존하면서 별도 목록을 만들어야 하는 경우에 유용한 방법입니다.

 

9. 중복값이 있는데도 중복으로 표시되지 않는 이유

같은 데이터처럼 보이는데 조건부 서식이나 중복 제거 기능에서 서로 다른 값으로 인식하는 경우가 있습니다.

대표적인 원인은 공백이나 문자 형식의 차이입니다.

예를 들어 다음 두 값은 화면에서 비슷하게 보일 수 있습니다.

김민수

김민수 (뒤에 공백 포함)

이런 경우 불필요한 공백 때문에 예상과 다른 결과가 나올 수 있습니다.

또한 전화번호에 하이픈이 있는 경우와 없는 경우도 주의해야 합니다.

010-1234-5678

01012345678

두 전화번호는 같은 번호를 의미하지만 엑셀에서는 서로 다른 문자열로 인식할 수 있습니다.

따라서 전화번호를 기준으로 중복값을 정리한다면 하이픈과 공백 등을 일정한 기준으로 통일하는 것이 좋습니다.

공백을 정리할 때는 TRIM 함수를 활용할 수 있고, 특정 문자를 제거할 때는 SUBSTITUTE 함수를 사용할 수 있습니다.

다만 원본 데이터를 직접 변경하기보다는 별도의 열에서 정리한 값을 만들어 비교하는 편이 안전합니다.

특히 고객번호나 전화번호처럼 앞자리 0이 중요한 자료는 숫자로 변환되면서 0이 사라지지 않도록 텍스트 형식으로 관리해야 합니다.

10. 자주 묻는 질문

Q. 엑셀에서 중복값만 빨간색으로 표시할 수 있나요?

A. 가능합니다. 중복값을 찾을 범위를 선택한 다음 '홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값'을 선택하면 원하는 색상으로 표시할 수 있습니다.

Q. 중복값을 표시하면 원본 데이터가 삭제되나요?

A. 아닙니다. 조건부 서식은 중복된 셀을 눈에 띄게 표시하는 기능이므로 실제 데이터는 삭제되지 않습니다.

Q. 중복 제거를 실행하면 어떤 데이터가 남나요?

A. 선택한 기준 열의 값이 같은 경우 일반적으로 현재 목록에서 첫 번째로 나타나는 항목을 유지합니다. 따라서 최신 자료를 남기려면 작업 전에 날짜순 정렬 등을 검토해야 합니다.

Q. 이름과 전화번호가 모두 같은 데이터만 삭제할 수 있나요?

A. 가능합니다. 중복 제거 기능에서 이름과 전화번호 열을 모두 선택하면 두 값이 일치하는 행을 중복으로 판단할 수 있습니다.

Q. 중복 제거를 잘못 실행했다면 되돌릴 수 있나요?

A. 작업 직후라면 Ctrl + Z로 실행 취소가 가능한 경우가 있습니다. 하지만 이후 작업이나 파일 저장 상태 등에 따라 되돌리기 어려울 수 있으므로 원본 파일을 별도로 보관하는 것이 가장 안전합니다.

Q. 중복값을 삭제하지 않고 따로 추출할 수 있나요?

A. 가능합니다. COUNTIF 함수로 중복 여부를 표시한 뒤 필터를 이용하면 중복으로 표시된 항목만 확인할 수 있습니다. 고급 필터를 이용하면 중복을 제외한 고유 항목을 다른 위치에 복사할 수도 있습니다.

11. 마무리

엑셀에서 중복값을 찾고 정리하는 방법은 어렵지 않습니다.

중복된 데이터를 눈으로 확인하고 싶다면 조건부 서식을 사용하면 됩니다.

같은 값이 몇 번 반복되는지 알고 싶다면 COUNTIF 함수를 활용할 수 있습니다.

중복된 행을 실제로 제거하고 싶다면 '데이터 → 중복 제거' 기능이 편리합니다.

하지만 이름이나 전화번호처럼 한 가지 항목만으로 동일한 데이터를 판단하기 어려운 경우에는 여러 열을 함께 확인해야 합니다.

무엇보다 중요한 것은 중복된 값이 있다고 해서 무조건 불필요한 데이터는 아니라는 사실입니다.

중복 제거를 실행하기 전에 원본 파일을 복사하고, 삭제 기준을 정확하게 정한 뒤 작업하면 중요한 정보를 잃지 않으면서 엑셀 자료를 효율적으로 관리할 수 있습니다.