거래처 명단을 정리하다 보면 분명 한 번만 입력한 것 같은 회사 이름이 두 번씩 들어가 있을 때가 있습니다. 직원 명단이나 고객 목록을 관리할 때도 비슷한 일이 생기죠.
처음에는 몇 줄 안 되니까 눈으로 확인할 수 있습니다. 그런데 데이터가 100개, 500개씩 쌓이면 이야기가 달라집니다. 같은 이름을 찾아보겠다고 화면을 위아래로 계속 움직이다 보면 시간이 오래 걸리고, 중간에 하나씩 놓치기도 쉽습니다.
이럴 때 알아두면 편리한 기능이 엑셀 중복값 찾기입니다.
가장 빠른 방법은 중복 여부를 확인할 셀을 선택한 뒤 홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값을 누르는 것입니다. 원하는 색상을 지정하면 같은 값이 반복되는 셀을 한눈에 확인할 수 있습니다.
별도의 프로그램을 설치할 필요도 없습니다. 엑셀에 기본으로 들어 있는 기능이기 때문에 메뉴 위치만 알아두면 바로 사용할 수 있습니다.
다만 중복값을 찾는 것과 삭제하는 것은 다릅니다. 특히 거래처나 고객 정보를 관리하는 파일이라면 중복된 항목이라고 해서 무조건 지우면 안 됩니다.
이번 글에서는 엑셀에서 중복값을 찾아 표시하는 방법부터 같은 값이 몇 번 들어갔는지 계산하는 방법, 실제 업무에서 데이터를 잘못 삭제하지 않도록 확인하는 방법까지 살펴보겠습니다.
엑셀에서 중복값을 찾을 때 가장 먼저 확인할 것
먼저 어떤 데이터를 중복으로 판단할지 정해야 합니다.
예를 들어 다음과 같은 거래처 목록이 있다고 해보겠습니다.
| 행 | 거래처명 | 담당자 | 연락처 |
|---|---|---|---|
| 2 | 한빛상사 | 김민수 | 010-1234-5678 |
| 3 | 새봄유통 | 이지연 | 010-2345-6789 |
| 4 | 한빛상사 | 박지훈 | 010-3456-7890 |
| 5 | 다온물산 | 최수진 | 010-4567-8901 |
| 6 | 새봄유통 | 이지연 | 010-2345-6789 |
거래처명만 보면 한빛상사와 새봄유통이 각각 두 번 등장합니다.
하지만 두 회사의 상황은 다릅니다.
한빛상사는 담당자와 연락처가 서로 다릅니다. 같은 회사에 담당자가 두 명일 수도 있으므로 무조건 잘못 입력했다고 볼 수 없습니다.
반면 새봄유통은 거래처명, 담당자, 연락처가 모두 같습니다. 동일한 정보가 중복 등록되었을 가능성이 있습니다.
이처럼 어떤 열을 기준으로 비교하느냐에 따라 중복값의 의미가 달라집니다.
따라서 엑셀 기능을 사용하기 전에 거래처명만 비교할지, 연락처까지 함께 확인할지 먼저 결정하는 것이 좋습니다.
조건부 서식으로 중복값에 색 표시하기
엑셀 중복값 찾기에서 가장 간단한 방법은 조건부 서식을 이용하는 것입니다.
엑셀 중복값 찾기를 처음 사용하는 사람이라면 복잡한 함수보다 조건부 서식부터 익히는 것이 편리합니다.
조건부 서식은 특정 조건에 해당하는 셀의 글자색이나 배경색을 자동으로 바꿔주는 기능입니다.
같은 이름이 여러 번 입력되어 있다면 해당 셀에 색을 표시해 쉽게 구분할 수 있습니다.
중복값 표시하는 순서
- 엑셀 파일을 열고 중복 여부를 확인할 데이터 범위를 선택합니다.
- 상단 메뉴에서 홈 탭을 클릭합니다.
- 조건부 서식을 선택합니다.
- 셀 강조 규칙 → 중복 값을 클릭합니다.
- 표시할 색상을 선택합니다.
- 확인을 누릅니다.
예를 들어 거래처명이 B2부터 B100까지 입력되어 있다면 B2:B100 범위를 선택하면 됩니다.
Microsoft에서 제공하는 실제 사용 방법은 중복 값 찾아 제거하기 공식 안내에서도 확인할 수 있습니다.
설정을 마치면 해당 범위에서 두 번 이상 나타나는 거래처명이 지정한 색으로 표시됩니다.
이 기능의 장점은 원본 데이터를 삭제하지 않는다는 점입니다. 어떤 값이 반복되는지 먼저 살펴볼 수 있어 실무에서 사용하기 편리합니다.
또한 선택한 범위에 새 값이 입력되면 적용된 조건부 서식 규칙에 따라 표시가 바뀔 수 있습니다. 다만 새로 입력한 셀이 규칙의 적용 범위 밖에 있다면 표시되지 않으므로 범위도 함께 확인해야 합니다.
주의할 점도 있습니다.
조건부 서식은 선택한 셀의 값을 비교합니다. 거래처명 열만 선택했다면 담당자나 연락처가 달라도 회사 이름이 같다는 이유로 중복 표시가 나타납니다.
따라서 색이 표시된 항목을 바로 삭제하지 말고 다른 열의 정보까지 확인해야 합니다.
중복된 이름만 따로 모아서 확인하고 싶다면
중복값에 색을 표시했더라도 데이터가 많으면 중복된 행을 찾기 위해 계속 화면을 움직여야 할 수 있습니다.
이럴 때는 엑셀의 필터 기능을 함께 사용하면 편리합니다.
앞에서 조건부 서식으로 중복값에 색을 표시했다면 다음 순서로 진행해 보세요.
- 거래처 목록의 제목 행을 포함해 데이터 범위를 선택합니다.
- 상단 메뉴에서 데이터 → 필터를 클릭합니다.
- 거래처명 열의 필터 화살표를 누릅니다.
- 색 기준 필터에서 중복값에 적용된 셀 색상을 선택합니다.
그러면 해당 색상이 표시된 행만 화면에 나타나도록 필터링할 수 있습니다.
예를 들어 500개 거래처 중 이름이 반복되는 항목이 20개라면 그 항목들만 모아서 확인하기가 한결 수월해집니다.
단, 이 방법은 조건부 서식에서 중복값에 셀 채우기 색을 지정했을 때 사용하기 좋습니다. 글자색만 변경했다면 필터 메뉴에서 해당 글자색을 기준으로 선택해야 합니다.
또한 필터는 데이터를 삭제하는 기능이 아닙니다. 조건에 맞지 않는 행을 화면에서 잠시 숨기는 방식이므로 필터를 해제하면 전체 목록을 다시 볼 수 있습니다.
COUNTIF 함수로 같은 값이 몇 번 들어갔는지 확인하기
중복된 이름이 있다는 사실뿐 아니라 정확히 몇 번 등장하는지 알고 싶을 때도 있습니다.
엑셀 중복값 찾기 과정에서 반복 횟수까지 알아야 한다면 COUNTIF 함수를 활용할 수 있습니다.
이럴 때는 COUNTIF 함수를 사용할 수 있습니다.
COUNTIF는 지정한 범위에서 조건에 맞는 셀이 몇 개인지 세는 함수입니다.
예를 들어 B열에 거래처명이 입력되어 있고 B2부터 B100까지 데이터가 있다고 가정해 보겠습니다.
C2 셀에 다음 수식을 입력합니다.
=COUNTIF($B$2:$B$100,B2)
그다음 C2 셀의 채우기 핸들을 아래로 끌어 다른 행에도 수식을 복사합니다.
COUNTIF 함수의 기본 사용법과 추가 예제는 Microsoft COUNTIF 함수 공식 안내에서 확인할 수 있습니다.
수식 결과가 의미하는 내용은 간단합니다.
| 결과 | 의미 |
|---|---|
| 1 | 선택한 범위에서 해당 값이 한 번 등장 |
| 2 | 같은 값이 두 번 등장 |
| 3 | 같은 값이 세 번 등장 |
| 4 이상 | 같은 값이 네 번 이상 등장 |
예를 들어 한빛상사가 두 번 입력되어 있다면 두 행 모두 결과가 2로 표시됩니다.
이 방법을 사용하면 중복 여부뿐 아니라 반복 횟수까지 확인할 수 있습니다.
수식에 달러 기호가 들어가는 이유
처음 함수를 사용하는 사람이라면 $B$2:$B$100처럼 달러 기호가 들어간 부분이 낯설 수 있습니다.
이 표시는 수식을 아래로 복사하더라도 비교 범위가 바뀌지 않도록 고정하는 역할을 합니다.
반면 마지막에 있는 B2는 수식을 아래로 복사하면 B3, B4처럼 바뀝니다.
즉, 전체 거래처 목록을 고정해 두고 각 행의 거래처명이 몇 번 등장하는지 차례대로 계산하는 방식입니다.
COUNTIF 함수에서 특수문자가 포함된 경우
COUNTIF 함수는 일반적으로 대문자와 소문자를 구분하지 않습니다. 또한 *와 ?를 와일드카드로 해석합니다.
와일드카드는 특정 문자를 대신해 조건을 검색하는 기호입니다.
*는 여러 문자를 대신합니다.?는 문자 한 개를 대신합니다.~는 와일드카드 문자를 일반 문자로 검색할 때 사용합니다.
예를 들어 상품 코드에 AB*12라는 값이 있다면 COUNTIF는 별표를 실제 문자로만 취급하지 않을 수 있습니다.
이런 데이터에서는 단순한 COUNTIF 결과가 실제로 같은 코드의 개수와 다를 수 있습니다.
별표와 물음표를 일반 문자로 처리하려면 다음과 같이 사용할 수 있습니다.
=COUNTIF($B$2:$B$100,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"~","~~"),"*","~*"),"?","~?"))
이 수식은 먼저 물결표를 처리한 다음 별표와 물음표 앞에 물결표를 붙여 해당 문자를 문자 그대로 비교하도록 합니다.
다만 COUNTIF는 이 방법을 사용해도 대문자와 소문자를 구분하지 않습니다. 대소문자까지 구분해야 하는 상품 코드나 식별번호라면 다른 비교 방법을 사용해야 합니다.
일반적인 거래처명이나 직원 이름 목록에서는 기본 COUNTIF 수식으로 충분한 경우가 많지만, 특수문자가 포함된 코드 데이터에서는 비교 기준을 한 번 더 확인하는 것이 안전합니다.
같은 이름이 두 번째로 등장하는 행만 표시하려면
앞에서 사용한 COUNTIF 수식은 중복된 이름이 있는 모든 행에 반복 횟수를 표시합니다.
하지만 업무에 따라서는 처음 입력한 항목은 그대로 두고, 두 번째부터 등장하는 항목만 확인하고 싶을 수 있습니다.
예를 들어 B2부터 거래처명이 입력되어 있다면 C2에 다음 수식을 넣을 수 있습니다.
=COUNTIF($B$2:B2,B2)
이 수식을 아래로 복사하면 현재 행까지 같은 값이 몇 번 등장했는지 계산합니다.
첫 번째 한빛상사는 1, 두 번째 한빛상사는 2, 세 번째 한빛상사는 3으로 표시되는 방식입니다.
따라서 결과가 2 이상인 행을 확인하면 처음 등장한 항목 이후에 반복 입력된 항목을 찾을 수 있습니다.
이 방식은 명단을 순서대로 정리하거나 중복 입력이 시작된 위치를 확인할 때 유용합니다.
다만 첫 번째로 입력된 데이터가 반드시 정확한 원본이라는 뜻은 아닙니다. 오래된 연락처가 먼저 입력되었고 최신 연락처가 나중에 추가되었을 수도 있습니다.
어떤 정보를 남길지는 실제 업무 기준에 따라 판단해야 합니다.
앞에서 설명한 것처럼 이 수식도 COUNTIF를 사용하므로 특수문자나 대소문자 구분이 중요한 데이터에서는 결과를 추가로 확인해야 합니다.
중복값 찾기와 중복된 항목 제거는 다릅니다
엑셀에는 중복된 항목 제거라는 기능도 있습니다.
이름이 비슷해서 중복값 찾기와 같은 기능으로 생각하기 쉽지만 결과는 전혀 다릅니다.
- 중복값 찾기: 같은 내용이 반복되는 셀이나 행을 확인하는 작업
- 중복된 항목 제거: 지정한 기준에서 중복으로 판단된 행을 실제로 제거하는 작업
중복된 항목 제거는 선택한 기준 열에서 값이 동일한 행을 중복으로 판단하고, 처음 나타난 행을 남기는 방식으로 작동합니다.
여기서 중요한 것은 여러 열을 기준으로 선택하면 해당 열들의 값이 모두 같아야 중복으로 판단한다는 점입니다.
예를 들어 다음과 같은 자료가 있다고 해보겠습니다.
| 거래처명 | 담당자 | 연락처 |
|---|---|---|
| 한빛상사 | 김민수 | 010-1234-5678 |
| 한빛상사 | 박지훈 | 010-3456-7890 |
| 새봄유통 | 이지연 | 010-2345-6789 |
| 새봄유통 | 이지연 | 010-2345-6789 |
거래처명만 기준으로 중복된 항목 제거를 실행하면 한빛상사와 새봄유통은 각각 하나의 행만 남게 됩니다. 담당자가 다른 한빛상사 기록까지 제거될 수 있는 것이죠.
반면 거래처명, 담당자, 연락처를 모두 기준으로 선택하면 세 항목이 모두 같은 새봄유통의 중복 행만 제거 대상이 됩니다. 한빛상사 두 행은 담당자와 연락처가 다르므로 각각 유지됩니다.
물론 세 항목이 같다고 해서 언제나 불필요한 데이터인 것은 아닙니다. 같은 거래처에 여러 차례 납품한 기록이라면 다른 거래 정보를 함께 살펴봐야 합니다.
특히 주문 내역, 입금 내역, 납품 기록처럼 같은 거래처가 여러 번 등장하는 것이 정상인 데이터에서는 주의해야 합니다.
같은 회사에서 한 달 동안 다섯 번 주문했다면 거래처명은 같더라도 주문 다섯 건은 각각 다른 기록입니다.
이런 자료에서 거래처명만 기준으로 중복 행을 제거하면 실제 거래 내역이 사라질 수 있습니다.
중복값이 발견되었다는 이유만으로 삭제 대상이라고 판단해서는 안 됩니다.
중복된 항목 제거 기능을 사용해야 한다면
거래처 목록을 정리하다가 동일한 정보가 여러 번 등록된 것이 확실하다면 중복된 항목 제거 기능을 사용할 수 있습니다.
다만 원본 파일을 먼저 복사해 두는 것이 좋습니다.
중복된 항목 제거 순서
- 원본 파일을 별도 이름으로 저장하거나 작업용 시트에 복사합니다.
- 제목 행을 포함한 전체 데이터 범위를 선택합니다.
- 상단 메뉴에서 데이터 → 중복된 항목 제거를 클릭합니다.
- 제목 행이 있다면 머리글 포함 여부를 확인합니다.
- 중복 여부를 판단할 열을 선택합니다.
- 확인을 눌러 결과를 확인합니다.
여기서 가장 중요한 단계는 다섯 번째입니다.
거래처명만 기준으로 중복을 판단할지, 거래처명과 담당자, 연락처까지 함께 비교할지에 따라 삭제되는 행이 달라질 수 있습니다.
여러 열을 선택했다면 선택한 열들의 값이 모두 같을 때 중복으로 판단합니다. 반대로 선택하지 않은 열의 값은 중복 판단에 사용되지 않습니다.
예를 들어 거래처명과 연락처만 선택했다면 담당자가 달라도 두 값이 같다는 이유로 중복된 행이 제거될 수 있습니다.
직원 명단에서도 이름이 같다는 이유만으로 중복된 항목을 제거하면 동명이인까지 삭제할 위험이 있습니다.
직원번호처럼 사람마다 고유하게 부여된 값이 있다면 그 값을 기준으로 확인하는 편이 더 정확합니다.
또한 엑셀에서 중복된 항목을 제거하면 선택한 기준에 따라 행 데이터가 제거됩니다. 단순히 화면에서 숨기는 필터 기능과는 다르므로 작업 전에 백업하는 습관이 중요합니다.
같은 이름인데 중복값으로 표시되지 않는 이유
눈으로 보기에는 같은 이름인데 엑셀에서는 서로 다른 값으로 인식되는 경우가 있습니다.
대표적인 원인은 불필요한 공백이나 보이지 않는 문자입니다.
예를 들어 다음 두 값은 화면에서 비슷하게 보일 수 있습니다.
한빛상사한빛상사(이름 뒤에 공백이 들어간 경우)
이런 차이가 있으면 비교 결과가 예상과 달라질 수 있습니다.
다른 프로그램에서 복사한 자료에는 일반 공백 외에 특수 공백이나 제어 문자가 포함되기도 합니다.
이럴 때는 TRIM 함수로 일반적인 불필요한 공백을 정리하거나, CLEAN 함수로 일부 인쇄되지 않는 문자를 제거할 수 있습니다.
예를 들어 B2 셀에 거래처명이 있다면 다음과 같이 입력할 수 있습니다.
=TRIM(B2)
다만 TRIM만으로 모든 종류의 공백이 제거되는 것은 아닙니다. 웹페이지에서 복사한 특수 공백은 별도 처리가 필요할 수 있습니다.
또 하나 주의할 점은 이름이 비슷하다고 같은 거래처는 아니라는 사실입니다.
‘한빛상사’와 ‘한빛상사 서울지점’은 서로 관련된 업체일 수 있지만, 업무상 별도로 관리해야 하는 거래처일 수도 있습니다.
이런 경우에는 회사명만 보고 합치기보다 사업자등록번호나 거래처 관리번호 등 실제로 사용하는 식별 정보를 확인하는 것이 안전합니다.
실무에서 자주 생기는 중복 데이터 문제
엑셀 중복값 찾기는 단순히 같은 글자를 찾아내는 기능이 아닙니다. 업무 자료를 정리할 때 실수를 줄이는 데 활용할 수 있습니다.
거래처 목록을 합칠 때
거래처 목록을 정리하면서 견적서나 거래명세서도 함께 관리하고 있다면, 견적서와 거래명세서의 차이도 확인해 보세요. 거래 단계에 따라 어떤 서류를 사용해야 하는지 정리했습니다.
영업팀에서 관리하던 거래처 목록과 회계팀에서 관리하던 목록을 하나로 합치면 같은 업체가 중복 등록될 수 있습니다.
이때는 거래처명만 비교하기보다 사업자등록번호나 내부 거래처 코드 등 신뢰할 수 있는 식별 기준이 있는지 확인하는 것이 좋습니다.
직원 명단을 정리할 때
직원 이름만으로 중복 여부를 판단하면 동명이인을 구분하기 어렵습니다.
직원번호가 있다면 직원번호를 기준으로 확인하고, 이름은 보조 정보로 활용하는 편이 안전합니다.
고객 신청 명단을 확인할 때
행사 신청자 목록에 같은 이름이 두 번 나타나더라도 서로 다른 사람일 수 있습니다.
연락처나 신청번호 등을 함께 살펴보고, 동일인이 여러 번 신청한 것인지 확인해야 합니다.
단, 개인정보가 포함된 자료는 업무에 필요한 범위에서만 확인하고 불필요하게 다른 사람에게 공유하지 않도록 주의해야 합니다.
주문 내역을 확인할 때
같은 고객이 같은 상품을 여러 번 주문했다면 고객명과 상품명이 반복되는 것이 정상일 수 있습니다.
이 경우에는 주문번호나 거래일 등으로 실제 중복 입력인지 구분해야 합니다.
엑셀 중복값을 확인할 때 기억하면 좋은 점
엑셀 중복값 찾기는 데이터를 무조건 삭제하기 위한 작업이 아니라, 반복된 정보를 정확하게 확인하고 정리하기 위한 과정입니다.
엑셀에서 같은 값을 찾는 방법은 여러 가지지만, 처음부터 복잡한 함수를 사용할 필요는 없습니다.
중복된 항목을 눈으로 확인하려면 조건부 서식, 같은 값이 몇 번 등장하는지 알고 싶다면 COUNTIF 함수를 사용하면 됩니다.
반복된 항목만 모아 보고 싶다면 필터 기능을 함께 활용할 수도 있습니다.
무엇보다 중요한 것은 중복된 값과 잘못 입력된 데이터를 구분하는 일입니다.
같은 거래처명이나 직원 이름이 두 번 나타났다고 해서 반드시 삭제해야 하는 것은 아닙니다. 담당자가 다르거나 동명이인일 수도 있고, 같은 고객의 서로 다른 주문 기록일 수도 있습니다.
따라서 중복값을 찾은 뒤에는 다른 열의 정보와 실제 업무 기록을 함께 확인하세요.
몇 번의 클릭으로 중복된 데이터를 찾는 것도 중요하지만, 필요한 정보를 지우지 않고 정확하게 정리하는 것이 더 중요합니다.