엑셀 중복값 찾기, 이 방법 하나면 5분 안에 정리된다

거래처 명단 정리하다가 같은 이름이 두 번 들어간 거 뒤늦게 발견하면 참 허탈하죠. 엑셀 중복값 찾기, 생각보다 간단한데 모르면 수백 행을 눈으로 훑어야 하는 상황이 됩니다.

결론부터 말하면 – 조건부 서식 하나로 중복값을 한눈에 색으로 표시할 수 있고, COUNTIF 함수로 어디서 몇 번 중복됐는지도 확인할 수 있습니다. 어떤 방법을 쓸지는 데이터 규모와 목적에 따라 다릅니다.

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

가장 직관적인 방법입니다. 데이터를 보면서 바로 어느 셀이 중복인지 색으로 확인할 수 있어요. 먼저 중복 여부를 확인하고 싶은 열을 선택합니다. A열 전체라면 A1:A1000 같은 식으로요.

선택 후 메뉴에서 홈 탭 → 조건부 서식 → 셀 강조 규칙 → 중복 값을 클릭합니다. 팝업창이 뜨면 색상을 고르고 확인만 하면 끝. 중복된 셀들이 일제히 강조색으로 변합니다.

다만 이 방법의 함정이 하나 있습니다. 원본 데이터가 유지된 채로 시각적으로만 표시되는 방식이라서, 색이 칠해진 걸 보고 “아 여기 중복이구나” 알 수는 있는데 어떤 게 먼저 입력된 건지는 파악이 안 됩니다. 그냥 눈으로 훑고 지울 거면 충분한 방법이죠.

COUNTIF 함수로 중복 횟수 파악하기

엑셀 중복값 찾기에서 COUNTIF는 단순히 있다/없다가 아니라 몇 번 중복됐는지까지 보여줍니다. 빈 열에 다음 수식을 입력하면 됩니다.

=COUNTIF($A$1:$A$1000, A1)

이 수식은 A1 값이 A열 전체에서 몇 번 나오는지 세어줍니다. 결과가 1이면 고유값, 2 이상이면 중복이죠. 이 수식을 아래로 쭉 드래그하면 각 행마다 중복 횟수가 표시됩니다.

솔직히 저도 처음엔 이 함수 쓰기가 귀찮아서 그냥 눈으로 보다가 엄청 큰 파일에서 실수한 적이 있습니다. 수백 개짜리 명단에서 두 번 입력된 업체를 못 보고 그대로 메일을 두 번 보낸 거죠. 그 이후론 그냥 COUNTIF 습관처럼 씁니다.

중복 제거 기능 – 아예 삭제해버릴 때

확인이 아니라 아예 중복을 제거할 때는 데이터 탭 → 중복 항목 제거를 씁니다. 클릭 두 번이면 끝나는 방법이에요.

열을 선택하고 실행하면 어떤 열 기준으로 중복을 제거할지 체크박스가 나옵니다. 단일 열이면 그대로, 여러 열의 조합이 같은 행을 중복으로 볼 때는 해당 열들에 모두 체크합니다. 확인을 누르면 중복 행이 삭제되면서 몇 개가 제거됐는지 알림이 뜹니다.

주의할 점이 있습니다. 중복 제거는 뒤에 있는 데이터를 삭제합니다. 즉 같은 값이 3행과 7행에 있으면 7행이 사라지는 방식이죠. 어떤 항목을 살릴지 구분이 필요하다면 이 기능 쓰기 전에 정렬부터 하는 게 좋습니다.

방법	특징	적합한 상황
조건부 서식	색으로 시각 표시, 원본 유지	눈으로 확인 후 직접 처리
COUNTIF 함수	중복 횟수 수치로 표시	대용량 데이터, 정밀 분석
중복 항목 제거	자동 삭제, 빠름	즉시 정리, 원본 불필요할 때
고급 필터	고유값만 다른 위치에 추출	원본 보존하며 정제본 필요할 때

고급 필터로 고유값만 따로 뽑기

원본을 건드리지 않으면서 중복 없는 깨끗한 목록을 따로 만들고 싶을 때 씁니다. 데이터 탭 → 고급을 클릭하면 고급 필터 창이 열립니다.

여기서 “다른 장소에 복사”를 선택하고, 복사 위치를 지정한 뒤 “고유 레코드만”에 체크합니다. 확인을 누르면 지정한 위치에 중복이 제거된 데이터가 새로 생성됩니다.

원본은 그대로 남아 있으니 비교가 필요할 때 유용하죠. 고객 명단이나 재고 목록처럼 원본 보존이 필요한 경우에 주로 씁니다.

• 단일 열 중복 – 조건부 서식 또는 COUNTIF
• 여러 열 조합 중복 – 중복 항목 제거 (여러 열 체크)
• 원본 보존 필요 – 고급 필터 복사
• 대용량 데이터 – COUNTIF + 보조열 필터 조합
• 엑셀 버전 무관 범용 – COUNTIF (모든 버전에서 동작)

UNIQUE 함수 – 최신 엑셀 전용 방법

마이크로소프트 365나 엑셀 2021 이상을 쓴다면 UNIQUE 함수가 훨씬 편합니다. 수식 하나로 고유값 목록이 자동으로 만들어지거든요.

=UNIQUE(A1:A500)

이렇게 입력하면 A열에서 중복을 제거한 고유값 목록이 동적으로 쏟아집니다. 원본이 바뀌면 UNIQUE 결과도 자동으로 갱신되는 게 장점이에요.

반대로 UNIQUE 함수는 엑셀 2016 이하에서는 아예 동작하지 않습니다. 회사 공용 PC에서 작업하다 보면 구버전 엑셀인 경우가 은근히 많아서, 집에서는 되던 수식이 회사에서는 오류가 뜨는 당황스러운 상황이 생기기도 합니다. 이 함수 쓸 때는 버전 확인이 먼저입니다.

두 목록 비교해서 중복 찾기

엑셀 중복값 찾기에서 또 자주 나오는 케이스가 두 개의 별도 목록을 비교하는 상황입니다. 예를 들어 지난달 거래처 목록과 이번 달 거래처 목록에서 겹치는 업체를 찾고 싶을 때죠.

이때는 MATCH나 VLOOKUP을 씁니다. B열 목록이 A열에 있는지 확인하려면 C열에 이렇게 입력합니다.

=ISNUMBER(MATCH(B1,$A$1:$A$500,0))

결과가 TRUE이면 B열 값이 A열에 존재한다는 뜻, FALSE면 없다는 뜻입니다. 조건부 서식과 조합하면 TRUE인 셀만 색으로 표시되게 할 수도 있어요.

▲ 두 목록 비교 시 공백이나 띄어쓰기 차이로 같은 이름인데 불일치로 나오는 경우가 생깁니다. 이럴 때는 TRIM 함수로 앞뒤 공백 먼저 제거하고 비교하는 게 정확합니다.

관련해서 마이크로소프트 공식 지원 페이지(엑셀 고유값 필터링 공식 가이드)도 한번 보시면 추가 응용 방법들이 잘 정리돼 있습니다.

자주 묻는 질문 FAQ

Q. 엑셀에서 중복값을 찾을 때 대소문자를 구분하나요?

기본적으로 엑셀의 중복 찾기 기능은 대소문자를 구분하지 않습니다. “Apple”과 “apple”은 같은 값으로 처리됩니다. 대소문자를 구분해서 비교하고 싶다면 EXACT 함수를 활용해야 합니다.

Q. 중복값을 찾아서 합계를 구하고 싶은데 어떻게 하나요?

SUMIF 함수를 쓰면 됩니다. =SUMIF(범위, 조건, 합계범위) 형식으로 입력하면 같은 항목끼리 수량이나 금액을 합산할 수 있습니다. 피벗 테이블도 이런 집계 작업에 매우 편리합니다.

Q. 조건부 서식으로 색 표시한 것을 나중에 지우려면?

홈 탭 → 조건부 서식 → 규칙 지우기 → 선택한 셀의 규칙 지우기 또는 시트 전체 규칙 지우기를 선택하면 색상 표시가 제거됩니다. 데이터는 그대로 유지됩니다.

Q. 중복이 몇 개인지 숫자로만 빠르게 알고 싶어요

SUMPRODUCT 함수를 활용하면 됩니다. =SUMPRODUCT((COUNTIF(A1:A500,A1:A500)>1)*1) 이 수식이 중복에 해당하는 셀의 총 개수를 반환합니다. 고유 중복 종류 수는 별도로 계산해야 합니다.

Q. 엑셀 중복값 찾기가 365 버전과 구버전에서 다른 점이 있나요?

UNIQUE, FILTER 같은 동적 배열 함수는 마이크로소프트 365와 엑셀 2021에서만 사용 가능합니다. COUNTIF, 조건부 서식, 고급 필터는 구버전에서도 모두 동작하니, 버전이 낮은 환경이라면 이 방법들을 사용하세요.