엑셀 중복값 찾기 방법 5가지 – 조건부 서식부터 수식, 피벗까지

몇천 줄짜리 엑셀 파일에서 중복 데이터를 하나하나 눈으로 찾고 있다면, 이건 분명 비효율의 끝판왕이죠. 결론부터 말하면, 엑셀 중복값 찾기는 조건부 서식 한 번이면 1초 만에 해결됩니다. 좀 더 세밀한 처리가 필요하다면 COUNTIF 수식이나 피벗 테이블을 쓰면 되고요.

업무를 하다 보면 거래처 목록, 회원 명단, 재고 리스트 같은 데이터에서 중복이 생기는 건 거의 불가피합니다. 문제는 이걸 모르고 넘어가면 보고서 수치가 틀어지거나 메일을 두 번 보내는 등 골치 아픈 상황이 벌어진다는 거예요. 그래서 엑셀 중복값 찾기는 데이터 정리의 첫 단추라고 할 수 있습니다.

조건부 서식으로 엑셀 중복값 찾기 – 가장 빠른 방법

엑셀 중복값 찾기에서 가장 많이 쓰이고, 가장 직관적인 방법입니다. 클릭 몇 번이면 중복된 셀이 자동으로 색칠되니까요.

이렇게만 하면 중복된 데이터가 빨간색(또는 지정한 색)으로 표시됩니다. 1만 줄이 넘는 데이터에서도 1~2초면 끝나요. (*저도 처음 이 기능을 알았을 때 그동안 눈으로 찾았던 시간이 억울하더라고요.)

다만 조건부 서식의 한계가 하나 있습니다. 중복값을 **보여주기만** 할 뿐, 직접 삭제하거나 분리하는 기능은 없어요. 눈으로 확인하고 수동으로 처리해야 하는 거죠. 대량 데이터에서 중복을 자동으로 제거하고 싶다면 다음 섹션의 방법을 참고하세요.

COUNTIF 수식으로 엑셀 중복값 찾기 – 정확한 개수까지 파악

조건부 서식은 “중복이다/아니다”만 알려주는 반면, COUNTIF 수식은 각 값이 몇 번 등장하는지 정확한 횟수를 알려줍니다. 엑셀 중복값 찾기에서 한 단계 더 깊이 들어가는 셈이죠.

수식은 이렇습니다. A열에 데이터가 있다고 가정하면 B1 셀에 아래를 입력하세요.

=COUNTIF($A:$A, A1)

결과가 1이면 고유값, 2 이상이면 중복이에요. 이 수식을 아래로 쭉 드래그하면 모든 행의 중복 여부를 확인할 수 있습니다.

좀 더 응용하면 중복값만 필터링할 수도 있습니다. B열에 COUNTIF 결과를 넣은 뒤, 데이터 탭에서 필터를 걸고 “2 이상”만 표시하면 중복 데이터만 모아볼 수 있죠. 이 방법이 실무에서는 오히려 조건부 서식보다 자주 쓰입니다.

그리고 하나 더. 두 개 이상의 열을 조합해서 중복을 찾아야 할 때가 있잖아요. 예를 들어 이름만으로는 판단이 안 되고, 이름+전화번호가 둘 다 같아야 중복인 경우. 이때는 COUNTIFS를 쓰면 됩니다.

=COUNTIFS($A:$A, A1, $B:$B, B1)

이렇게 하면 A열과 B열의 값이 동시에 일치하는 행만 중복으로 카운트해요. 왜 이런 걸 학교에서 안 알려주는 건지 모르겠습니다.

중복 제거 기능으로 한 번에 정리하기

엑셀 중복값 찾기를 넘어서 아예 제거까지 하고 싶다면 ‘중복 제거’ 기능을 쓰면 됩니다. 데이터 탭 → 중복 제거를 클릭하면 되는데, 이 기능은 원본 데이터를 직접 수정하니까 반드시 백업을 먼저 해두세요.

방법	장점	단점
조건부 서식	시각적으로 즉시 확인	삭제·분리 불가
COUNTIF 수식	중복 횟수 정확히 파악	별도 열 필요
중복 제거	클릭 한 번으로 정리	원본 데이터 변경됨
피벗 테이블	대량 데이터 분석에 적합	초보자에게 다소 어려움
Power Query	반복 작업 자동화	학습 곡선 존재

중복 제거 기능을 쓸 때 주의할 점이 있어요. 엑셀은 중복된 행 중 **첫 번째 행만 남기고** 나머지를 삭제합니다. 그래서 어떤 행이 남을지 예측하려면 사전에 정렬을 해두는 게 좋아요. 예를 들어 최신 데이터를 남기고 싶다면 날짜 열 기준으로 내림차순 정렬한 뒤 중복 제거를 실행하면 됩니다.

그리고 다중 열 기준 중복 제거도 가능합니다. 중복 제거 대화상자에서 기준이 되는 열을 선택할 수 있거든요. 이름+부서+전화번호가 모두 같은 행만 중복으로 처리하고 싶다면 세 열 모두 체크하면 되는 거죠.

피벗 테이블로 엑셀 중복값 찾기 – 대량 데이터 분석

데이터가 수만 줄 이상이거나, 단순 중복 여부가 아니라 “어떤 값이 몇 번 나타나는지” 전체 분포를 보고 싶을 때는 피벗 테이블이 제격입니다. 엑셀 중복값 찾기를 넘어서 데이터 분석 영역으로 들어가는 셈이에요.

만드는 방법은 간단합니다. 데이터 범위를 선택한 뒤 삽입 탭 → 피벗 테이블을 클릭하세요. 행(Rows) 영역에 중복을 확인하려는 열을 넣고, 값(Values) 영역에도 같은 열을 넣되 “개수”로 설정하면 각 값의 출현 빈도가 한눈에 보입니다.

피벗 테이블의 장점은 원본 데이터를 건드리지 않는다는 겁니다. 분석 결과가 별도 시트에 만들어지니까 실수로 데이터를 날릴 걱정이 없어요. 중복 제거와 달리 비파괴적인 분석이 가능하다는 점에서 대용량 작업에 더 안전하죠.

사실 피벗 테이블이라는 말만 들으면 좀 어렵게 느껴지는데, 한두 번만 직접 만들어보면 금방 익숙해집니다. 이거 쓸 줄 알면 회사에서 엑셀 좀 한다는 소리 듣는 건 시간문제더라고요.

Power Query로 반복 작업 자동화하기

매주, 매월 같은 양식의 데이터를 받아서 엑셀 중복값 찾기를 반복해야 한다면 Power Query를 배워둘 가치가 있습니다. 엑셀 2016 이상 버전에서는 기본 내장 기능이에요.

데이터 탭 → 테이블에서/범위에서를 클릭하면 Power Query 편집기가 열립니다. 여기서 열 기준 그룹화를 하면 각 값의 출현 빈도를 자동으로 계산해주고, 중복 행 제거도 클릭 한 번이면 됩니다.

• 행 제거 → 중복 항목 제거 – 선택한 열 기준으로 중복 행을 자동 삭제
• 행 유지 → 중복 항목 유지 – 반대로 중복된 행만 남기기
• 열 그룹화 – 각 값의 개수, 합계, 평균 등을 한 번에 계산
• 쿼리 새로 고침 – 원본 데이터가 바뀌면 결과도 자동 업데이트

Power Query의 진짜 강점은 한 번 쿼리를 만들어두면 다음에는 “새로 고침” 버튼 하나로 같은 작업이 반복된다는 점이에요. 매달 보고서를 만드는 사람이라면 초기 셋업에 30분 투자하고, 이후 매번 5분씩 절약하는 구조가 됩니다.

이쯤 되면 “그냥 COUNTIF 쓰고 말지”라는 생각이 드실 수도 있는데, 데이터가 10만 줄을 넘어가면 COUNTIF는 눈에 띄게 느려집니다. Power Query는 그런 대용량 처리에서 확실히 빠르더라고요. 마이크로소프트 공식 Power Query 가이드도 참고해 보세요.

자주 묻는 질문 FAQ

Q. 엑셀 중복값 찾기에서 대소문자를 구분할 수 있나요?

A. COUNTIF와 조건부 서식은 대소문자를 구분하지 않습니다. 대소문자를 구분하려면 EXACT 함수를 SUMPRODUCT와 조합해서 쓰면 됩니다. =SUMPRODUCT((EXACT($A$1:$A$100,A1))*1) 형태로요.

Q. 중복값 중에서 첫 번째만 남기고 나머지를 색칠할 수 있나요?

A. 가능합니다. 조건부 서식에서 새 규칙 → 수식을 사용하여 서식 지정 → =COUNTIF($A$1:A1,A1)>1 을 입력하면 두 번째 이후 중복만 색칠됩니다.

Q. 두 시트 간 엑셀 중복값 찾기도 되나요?

A. COUNTIF 함수에서 범위를 다른 시트로 지정하면 됩니다. =COUNTIF(Sheet2!$A:$A, A1) 형태로 쓰면 Sheet2의 A열과 비교해서 중복 여부를 알 수 있어요.

Q. 엑셀 중복값 찾기 후 한 번에 삭제하는 방법은?

A. COUNTIF로 중복 표시 → 필터로 중복만 표시 → 해당 행 전체 선택 → 삭제. 이 순서가 가장 안전합니다. 데이터 탭의 중복 제거 기능도 빠르지만, 어떤 행이 삭제되는지 미리 확인이 안 되는 게 단점이에요.

Q. 구글 시트에서도 같은 방법을 쓸 수 있나요?

A. 조건부 서식과 COUNTIF는 구글 시트에서도 거의 동일하게 작동합니다. 중복 제거 기능은 데이터 메뉴 → 데이터 정리 → 중복 항목 삭제에 있어요.

엑셀 중복값 찾기가 별거 아닌 것 같아도, 이걸 제대로 모르고 수작업으로 하고 있는 사람이 생각보다 많습니다. 한번 익혀두면 매일 10분씩은 아끼게 되니까, 귀찮더라도 오늘 한번 직접 해보시길 바랍니다. 특히 COUNTIF 수식은 한번 써보면 다시는 눈으로 중복을 찾는 일은 없을 거예요.