엑셀 중복값 찾기 (중복값 표시 방법)

엑셀로 명단이나 거래내역, 상품 목록 등을 정리하다 보면 같은 데이터가 두 번 이상 입력되는 경우가 있습니다.

데이터가 몇십 개 정도라면 눈으로 확인할 수도 있지만 수백 개, 수천 개가 넘어가면 직접 중복값을 찾아내기가 쉽지 않습니다.

이럴 때 엑셀의 조건부 서식을 이용하면 중복된 값을 자동으로 찾아 색상으로 표시할 수 있습니다.

또한 COUNTIF 함수를 활용하면 단순히 중복 여부를 확인하는 것을 넘어 같은 값이 몇 번 입력되어 있는지도 확인할 수 있습니다.

이번 글에서는 엑셀에서 중복값을 찾고 표시하는 대표적인 방법을 알아보겠습니다.

엑셀 중복값 찾기 가장 쉬운 방법

가장 간단한 방법은 엑셀의 조건부 서식을 이용하는 것입니다.

예를 들어 A열에 다음과 같은 상품명이 입력되어 있다고 가정해 보겠습니다.

상품명
사과
배
포도
사과
딸기
배
수박

여기에서는 ‘사과’와 ‘배’가 각각 두 번 입력되어 있습니다.

데이터가 적으면 쉽게 찾을 수 있지만 데이터가 많아지면 눈으로 확인하기 어렵습니다.

먼저 중복값을 찾을 셀 범위를 선택합니다.

예를 들어 상품명이 A2부터 A100까지 입력되어 있다면 A2:A100 범위를 선택합니다.

이후 상단 메뉴에서

홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값

순서로 선택합니다.

중복 값 설정 창이 나타나면 중복된 셀에 적용할 표시 형식을 선택할 수 있습니다.

기본적으로 연한 빨강 채우기와 진한 빨강 텍스트 등이 제공되며 원하는 색상으로 변경할 수도 있습니다.

확인을 누르면 선택한 범위에서 두 번 이상 등장하는 값의 셀이 자동으로 강조 표시됩니다.

따라서 데이터가 많더라도 중복된 항목을 한눈에 찾을 수 있습니다.

중복값이 아닌 고유값만 표시하는 방법

조건부 서식의 중복 값 기능에서는 반대로 한 번만 존재하는 값을 표시할 수도 있습니다.

홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값

으로 들어간 다음 설정 창에서 ‘중복’ 대신 ‘고유’를 선택합니다.

그러면 선택한 범위에서 한 번만 등장하는 데이터가 강조됩니다.

중복 데이터를 찾는 것뿐만 아니라 다른 데이터와 겹치지 않는 항목을 확인해야 할 때 유용합니다.

COUNTIF 함수로 중복값 찾기

조건부 서식은 중복값을 빠르게 눈으로 확인하기에는 편리하지만 별도의 열에 중복 여부를 표시하고 싶다면 COUNTIF 함수를 활용할 수 있습니다.

예를 들어 A2:A10에 상품명이 입력되어 있고 B열에 중복 여부를 표시한다고 가정해 보겠습니다.

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

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

수식을 입력한 다음 아래쪽 셀까지 복사합니다.

COUNTIF 함수는 지정된 범위 안에 해당 값이 몇 개 존재하는지를 계산합니다.

예를 들어 ‘사과’가 두 번 있다면 결과가 2로 표시되고, ‘포도’가 한 번만 있다면 결과가 1로 표시됩니다.

따라서 결과값이

1 = 중복 없음

2 이상 = 중복값 존재

라고 판단할 수 있습니다.

중복이면 ‘중복’이라고 표시하기

숫자 대신 B열에 바로 ‘중복’이라고 표시하고 싶다면 IF 함수와 COUNTIF 함수를 함께 사용할 수 있습니다.

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

이 수식은 A열에서 현재 값이 두 번 이상 등장하는지 확인합니다.

두 번 이상 존재하면 중복이라는 문자를 표시하고, 한 번만 존재하면 빈칸으로 표시합니다.

결과는 다음과 같은 형태가 됩니다.

상품명	중복 여부
사과	중복
배	중복
포도
사과	중복
딸기
배	중복
수박

데이터를 검토하거나 다른 사람에게 전달해야 하는 문서라면 이런 방식으로 중복 여부를 별도의 열에 표시하는 것도 편리합니다.

두 번째로 입력된 값부터 중복 표시하기

앞에서 소개한 방법은 같은 값이 두 개라면 첫 번째 값과 두 번째 값 모두 중복으로 판단합니다.

하지만 데이터를 정리하다 보면 최초 입력된 값은 정상 데이터로 두고 두 번째로 등장한 값부터 중복으로 표시하고 싶은 경우도 있습니다.

이때는 COUNTIF 범위를 조금 다르게 지정하면 됩니다.

B2 셀에 다음과 같이 입력합니다.

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

여기에서 중요한 부분은

$A$2:A2

입니다.

수식을 아래로 복사하면 범위가 다음과 같이 늘어납니다.

$A$2:A2

$A$2:A3

$A$2:A4

$A$2:A5

이런 방식으로 위에서부터 현재 행까지 같은 값이 몇 번 등장했는지 계산합니다.

따라서 처음 등장한 ‘사과’는 정상으로 처리되고 두 번째 ‘사과’부터 중복이라고 표시됩니다.

실제 데이터에서 중복으로 추가 입력된 행을 찾아 삭제해야 할 때 특히 유용한 방법입니다.

조건부 서식에 COUNTIF 함수 활용하기

조건부 서식에서도 COUNTIF 함수를 이용할 수 있습니다.

단순한 중복 값 기능보다 조건을 세밀하게 지정하고 싶을 때 사용하면 좋습니다.

먼저 A2:A100처럼 중복값을 확인할 범위를 선택합니다.

그다음

홈 → 조건부 서식 → 새 규칙 → 수식을 사용하여 서식을 지정할 셀 결정

을 선택합니다.

수식 입력란에 다음과 같이 입력합니다.

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

이후 원하는 채우기 색상이나 글자 색상을 지정합니다.

그러면 같은 값이 두 개 이상 존재하는 셀만 지정한 서식으로 표시됩니다.

중복값을 찾은 다음 삭제하려면?

중복값을 찾는 것에서 끝나는 것이 아니라 실제로 중복 데이터를 삭제해야 할 수도 있습니다.

이때는 엑셀에서 제공하는 중복된 항목 제거 기능을 이용할 수 있습니다.

데이터 범위를 선택한 다음

데이터 → 중복된 항목 제거

를 선택합니다.

중복 여부를 판단할 열을 선택하고 확인하면 중복된 데이터가 제거됩니다.

다만 주의할 점이 있습니다.

조건부 서식은 중복값을 표시만 하는 기능이지만 중복된 항목 제거는 실제 데이터를 삭제합니다.

따라서 중요한 자료라면 원본 파일을 복사해 두거나 중복 데이터를 먼저 확인한 후 제거하는 것이 안전합니다.

중복값 찾기와 같은 값 합치기는 다르다

중복값을 찾은 다음 무조건 삭제해야 하는 것은 아닙니다.

예를 들어 다음과 같은 판매 데이터가 있다고 가정해 보겠습니다.

상품	판매량
사과	10
배	5
사과	20

여기에서 ‘사과’가 두 번 있다고 해서 두 번째 행을 삭제하면 실제 판매량 데이터가 사라집니다.

이런 경우에는 중복 제거가 아니라 같은 상품의 판매량을 합산해야 합니다.

같은 값을 기준으로 숫자를 합치는 작업에는 SUMIF 함수 또는 피벗 테이블을 활용할 수 있습니다.

따라서 중복값을 발견했다면 먼저 해당 데이터가 잘못 입력된 중복 데이터인지, 정상적으로 여러 번 발생한 데이터인지 구분하는 것이 중요합니다.

엑셀 중복값 찾기 방법 정리

엑셀에서는 목적에 따라 중복값을 확인하는 방법을 다르게 선택할 수 있습니다.

원하는 작업	추천 방법
중복값을 빠르게 색상으로 표시	조건부 서식
같은 값이 몇 개인지 확인	COUNTIF
별도 열에 ‘중복’ 표시	IF + COUNTIF
두 번째 데이터부터 중복 표시	누적 COUNTIF
중복 데이터를 실제로 삭제	중복된 항목 제거
중복값별 숫자를 합산	SUMIF 또는 피벗 테이블

단순히 중복된 데이터가 있는지 확인하는 것이 목적이라면 조건부 서식이 가장 간단합니다.

반면 중복 여부를 이용해 추가 작업을 하거나 데이터 분석에 활용해야 한다면 COUNTIF 함수를 알아두는 것이 좋습니다.

엑셀 같은 값 합치는 방법 (중복 데이터 정리)

마무리

엑셀에서 중복값을 찾는 가장 쉬운 방법은 홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값 기능을 이용하는 것입니다.

별도의 함수를 입력할 필요가 없기 때문에 엑셀을 처음 사용하는 사람도 쉽게 활용할 수 있습니다.

조금 더 세밀하게 데이터를 관리하고 싶다면 COUNTIF 함수를 활용해 중복된 횟수를 계산하거나 IF 함수와 조합해 ‘중복’이라고 자동으로 표시할 수도 있습니다.

특히 업무용 데이터에서는 중복된 값이라고 해서 무조건 삭제해서는 안 됩니다. 단순 입력 오류인지, 실제로 여러 번 발생한 정상 데이터인지 확인한 후 중복 제거 또는 같은 값 합치기 중 적절한 방법을 선택하는 것이 좋습니다.