엑셀 중복값 찾기 – 조건부 서식 색 표시부터 COUNTIF·중복된 항목 제거·UNIQUE까지

엑셀에서 중복값을 가장 빨리 보는 방법은 홈 → 조건부 서식 → 셀 강조 규칙 → 중복 값입니다. 중복된 셀이 바로 색칠됩니다.

몇 번 나왔는지 세거나 두 번째부터만 표시하려면 COUNTIF를, 한 번에 지우려면 데이터 → 중복된 항목 제거를 씁니다.

Microsoft 365라면 =UNIQUE(범위) 한 줄로 중복 없는 목록을 원본을 건드리지 않고 만들 수 있습니다.

명단이나 주문 목록을 합치다 보면 같은 이름, 같은 전화번호가 두 번 세 번 들어가 있기 마련입니다. 수백 줄이 넘으면 눈으로 찾기는 불가능하고 잘못 지우면 필요한 데이터까지 사라집니다. 엑셀에는 목적에 따라 중복을 찾는 방법이 여러 가지 있어서 상황에 맞는 방법을 골라야 합니다.

이 글은 Microsoft 365 엑셀 기준으로 중복값을 색으로 표시하는 방법, 함수로 세는 방법, 삭제하는 방법, 두 목록을 비교하는 방법을 정리했습니다. 엑셀 2016 이후 버전은 메뉴 위치가 같습니다.

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

데이터를 바꾸지 않고 어디가 중복인지 눈으로 확인하고 싶을 때 가장 먼저 쓰는 방법입니다. 클릭 몇 번이면 끝나고 데이터가 바뀌면 표시도 자동으로 따라 바뀝니다.

1. 중복을 찾을 범위를 선택합니다(예: A2:A500).
2. 홈 탭 → 조건부 서식 → 셀 강조 규칙 → 중복 값을 누릅니다.
3. 왼쪽 칸이 「중복」인지 확인하고 색을 고른 뒤 확인을 누릅니다.
4. 색칠된 셀만 보고 싶으면 데이터 → 필터를 켜고 「색 기준 필터」를 씁니다.

이 방법은 한 열 안의 같은 값을 찾습니다. 여러 열을 선택하면 열을 가리지 않고 같은 값이면 모두 색칠되므로, "이름과 전화번호가 둘 다 같은 행"처럼 행 단위로 찾으려면 아래의 COUNTIFS를 써야 합니다.

COUNTIF로 중복 개수 세기

옆 열에 수식을 넣으면 각 값이 몇 번 나왔는지 숫자로 볼 수 있습니다. 정렬·필터와 함께 쓰면 중복만 골라내기 쉽습니다.

하고 싶은 것	수식 (B2에 입력 후 아래로 복사)
전체에서 몇 번 나왔나	=COUNTIF($A$2:$A$500, A2)
중복이면 「중복」 표시	=IF(COUNTIF($A$2:$A$500, A2)>1, "중복", "")
두 번째로 나온 것부터만 표시	=IF(COUNTIF($A$2:A2, A2)>1, "중복", "")
이름+전화번호가 둘 다 같은 행	=COUNTIFS($A$2:$A$500, A2, $B$2:$B$500, B2)

세 번째 수식은 범위의 끝을 $A$2:A2처럼 한쪽만 고정해서 위에서부터 자기 자신까지만 셉니다. 그래서 처음 나온 값은 빈칸으로 두고 두 번째부터만 「중복」이 표시됩니다. 원본 하나는 남기고 나머지만 지울 때 가장 안전한 방법입니다.

중복 삭제하기 (중복된 항목 제거·UNIQUE)

중복을 아예 지우는 방법은 두 가지입니다. 원본을 바로 고치는 「중복된 항목 제거」와, 원본은 두고 새 목록을 만드는 UNIQUE 함수입니다.

방법	위치·수식	원본	특징
조건부 서식	홈 → 조건부 서식 → 중복 값	그대로	색으로 표시만
COUNTIF	=COUNTIF(범위, 셀)	그대로	몇 번인지 숫자로
중복된 항목 제거	데이터 → 데이터 도구 → 중복된 항목 제거	바뀜	첫 번째만 남기고 삭제
UNIQUE (365)	=UNIQUE(A2:A500)	그대로	중복 없는 새 목록, 자동 갱신

「중복된 항목 제거」 전에 시트를 복사해 두세요

이 기능은 되돌리기(Ctrl+Z) 말고는 복구할 방법이 없고 저장한 뒤에는 되돌릴 수 없습니다. 또 창에서 체크한 열이 모두 같을 때만 중복으로 봅니다. 이름 열만 체크하면 동명이인까지 지워지니, 기준 열을 신중하게 고르세요.

두 목록 비교하기와 자주 하는 실수

A열 명단과 B열 명단을 비교해 양쪽에 모두 있는 값이나 한쪽에만 있는 값을 찾을 때도 COUNTIF를 씁니다. C2에 =IF(COUNTIF($B$2:$B$500, A2)>0, "B에도 있음", "A에만 있음")을 넣고 아래로 복사하면 됩니다.

중복인데 안 찾아질 때 확인할 것

• 앞뒤 공백 — "홍길동"과 "홍길동 "은 다른 값입니다. =TRIM(A2)로 공백을 지운 열로 비교하세요
• 숫자와 텍스트 — 전화번호 010-1234-5678과 01012345678은 다릅니다. 형식을 먼저 통일하세요
• 대소문자 — 조건부 서식과 COUNTIF는 대소문자를 구분하지 않습니다. 구분하려면 EXACT 함수를 씁니다
• 병합된 셀 — 병합된 범위에서는 중복된 항목 제거가 동작하지 않으니 병합을 먼저 풉니다

엑셀 중복값 자주 묻는 질문

Q. 중복된 항목 제거를 하면 어떤 행이 남나요?

처음 나온 행만 남고 그 아래 중복 행이 지워집니다. 이때 창에서 체크한 열의 값이 모두 같아야 중복으로 봅니다. 이름 열만 체크하면 동명이인까지 지워지니 기준 열을 신중히 고르고 이름과 전화번호처럼 두 열 이상을 함께 체크하는 편이 안전합니다. 또 저장한 뒤에는 되돌릴 수 없으므로 실행 전에 시트를 복사해 두세요.

Q. 눈으로 보면 같은 값인데 중복으로 표시되지 않아요.

앞뒤 공백이나 형식 차이 때문인 경우가 대부분입니다. 「홍길동」과 끝에 공백이 붙은 「홍길동 」은 다른 값이고 전화번호 010-1234-5678과 01012345678도 엑셀은 다르게 봅니다. =TRIM(A2)로 공백을 지운 열이나 형식을 통일한 열을 만들어 다시 비교하세요.

Q. UNIQUE 함수가 안 되면 어떻게 하나요?

엑셀 버전을 먼저 확인하세요. UNIQUE는 엑셀 2021 이상이나 Microsoft 365에서만 쓸 수 있습니다. 이전 버전이라면 원본을 복사한 뒤 데이터 → 중복된 항목 제거를 쓰거나 COUNTIF로 두 번째부터만 「중복」을 표시한 다음 필터로 골라내면 됩니다. 이 방법은 원본을 건드리지 않아서 지우기 전에 어떤 값이 중복인지 먼저 확인할 때도 좋습니다.

함께 보면 좋은 글

중복 없이 정리한 목록을 입력 칸의 선택지로 쓰려면 엑셀 드롭다운 만들기 – 데이터 유효성 검사 목록부터 자동 확장·연동 드롭다운·삭제까지를 보세요. UNIQUE 결과를 드롭다운 원본으로 연결하는 방법도 나와 있습니다.

날짜 목록을 다룬다면 날짜 계산기 – 며칠 후 날짜·두 날짜 사이 일수·평일 수 계산에서 NETWORKDAYS·WORKDAY 같은 날짜 함수도 함께 확인할 수 있습니다.

출처: Microsoft 지원 「중복 값 필터링 또는 제거」·「조건부 서식으로 중복 값 강조 표시」·「COUNTIF 함수」·「COUNTIFS 함수」·「UNIQUE 함수」·「TRIM 함수」.

공식 누리집 바로가기: Microsoft Excel 지원 · Microsoft Learn

공식 안내: Microsoft 지원 「중복 값 찾아 제거하기」

본 글은 Microsoft 365 엑셀(Windows) 화면 기준입니다. UNIQUE 함수는 엑셀 2021 이상 또는 Microsoft 365에서 쓸 수 있습니다.