엑셀 중복값 찾기, 이 방법 하나만 알면 됩니다

엑셀로 수백 줄짜리 명단을 정리하다 보면 어느 순간 반드시 맞닥뜨리는 문제가 있습니다. 같은 값이 두 번 들어갔는지 확인해야 하는 상황이죠. 엑셀 중복값 찾기는 한 번만 제대로 익혀두면 두고두고 써먹을 수 있는 기능입니다.

조건부 서식으로 중복값 한눈에 표시하기

가장 직관적인 방법부터 시작하겠습니다. 조건부 서식을 쓰면 엑셀 중복값 찾기를 클릭 몇 번으로 해결할 수 있습니다.

먼저 중복 여부를 확인하고 싶은 범위를 선택합니다. A열 전체라면 A1부터 마지막 데이터까지 드래그하면 됩니다.

그 다음 상단 메뉴에서 홈 탭을 클릭하고, 스타일 그룹에 있는 ‘조건부 서식’을 찾습니다. 여기서 ‘셀 강조 규칙’ – ‘중복 값’을 선택하면 대화상자가 나타납니다.

기본값은 빨간 배경으로 표시되는데, 원하는 색상으로 바꿔도 됩니다. 확인을 누르는 순간 중복된 셀들이 전부 색으로 표시됩니다. 저도 처음에 이걸 알았을 때 왜 진작 안 썼나 싶었는데, 생각보다 모르는 분들이 많더라고요.

주의할 점이 있습니다. 이 방법은 중복값 찾기에는 좋지만 어디에 몇 개가 있는지 숫자로 파악하기엔 한계가 있습니다. 그럴 때는 다음 방법이 필요합니다.

COUNTIF 함수로 중복 개수 확인하기

COUNTIF 함수는 엑셀 중복값 찾기에서 조금 더 정밀한 도구입니다. 중복이 있다는 사실만이 아니라 몇 번이나 등장하는지까지 알 수 있죠.

사용법은 어렵지 않습니다. 빈 열(예: B열)에 다음 수식을 입력합니다.

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

이 수식은 A1 값이 A1부터 A100 범위에서 몇 번 나타나는지 세어줍니다. 결과가 2 이상이면 중복이 있다는 뜻입니다. 이 수식을 B열 전체에 복사해두면 각 행마다 자동으로 계산됩니다.

여기서 한 가지 더 – 중복인 항목만 빠르게 찾고 싶다면 B열을 기준으로 필터를 걸어서 2 이상인 것만 보면 됩니다. 필터는 Ctrl+Shift+L로 바로 켤 수 있습니다.

중복값 제거 – 실수 없이 처리하는 법

찾는 것도 중요하지만 제거까지 해야 하는 경우가 많습니다. 엑셀에는 중복 제거 전용 기능이 따로 있습니다.

데이터 탭 – 데이터 도구 그룹에서 ‘중복 항목 제거’를 클릭하면 됩니다. 어떤 열을 기준으로 중복을 판단할지 선택하는 창이 나오는데, 여러 열을 동시에 선택하면 해당 열 조합이 모두 같을 때만 중복으로 처리합니다.

그런데 이 기능은 원본 데이터가 바뀝니다. 실수가 있어도 되돌리기(Ctrl+Z)가 되긴 하지만, 중요한 파일이라면 작업 전에 반드시 다른 이름으로 저장해두는 게 안전합니다. 실제로 이 과정에서 필요한 데이터를 날린 경험이 있는 분들이 꽤 됩니다. 저도 한 번 아찔했던 적이 있어서…

▲ 중요한 점은 이 기능이 행 단위로 삭제된다는 것입니다. 특정 열만 지우는 게 아니라 해당 행 전체가 사라집니다. 여러 열이 있는 시트에서 사용할 때 각별히 주의해야 합니다.

고급 필터로 고유값만 따로 추출하기

원본은 그대로 두고 중복 없는 고유값 목록만 별도로 만들고 싶을 때는 고급 필터가 제격입니다. 엑셀 중복값 찾기와 정리를 동시에 처리하는 방법이죠.

데이터 탭 – 정렬 및 필터 그룹에서 ‘고급’을 클릭합니다. 대화상자에서 ‘다른 장소에 복사’를 선택하고 목록 범위(원본), 복사 위치를 지정한 다음 ‘동일한 레코드는 하나만’ 체크박스에 반드시 체크합니다.

이렇게 하면 원본은 그대로이고 지정한 위치에 중복이 제거된 목록이 생깁니다. 비교적 덜 알려진 기능인데 실무에서 굉장히 유용합니다.

여러 열에 걸친 중복값 찾기

실무에서는 단순히 한 열만 비교하는 경우보다 여러 열을 묶어서 중복을 판단해야 하는 경우가 많습니다. 예를 들어 이름과 전화번호가 모두 같을 때만 중복으로 보는 식이죠.

이럴 때는 COUNTIFS 함수를 씁니다. 복수의 조건을 동시에 적용하는 함수입니다.

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

이 수식은 A열과 B열이 동시에 일치하는 경우만 카운트합니다. 조건을 더 추가하면 3개, 4개 열 조합도 가능합니다. 엑셀 중복값 찾기를 할 때 이 함수를 모르면 꽤 당황하게 되는 상황이 많습니다.

참고로 마이크로소프트 공식 지원 페이지(support.microsoft.com)에서 COUNTIFS 함수 상세 사용법을 확인할 수 있습니다.

대용량 데이터에서 중복값 찾을 때 주의사항

수만 행 이상의 데이터를 다루다 보면 엑셀 중복값 찾기가 느려지거나 파일이 버벅이는 경험을 하게 됩니다. 몇 가지 주의사항을 알아두면 도움이 됩니다.

우선 조건부 서식은 데이터가 많을수록 셀 하나하나에 조건을 적용하기 때문에 파일이 무거워집니다. 데이터가 1만 행이 넘는다면 COUNTIF 함수로 확인하고 조건부 서식은 최소화하는 게 낫습니다.

COUNTIF 범위 지정도 습관이 중요합니다. 범위를 A:A처럼 열 전체로 지정하면 빈 셀까지 전부 계산해서 속도가 느려집니다. 실제 데이터가 있는 범위만 정확하게 지정하는 게 좋습니다.

• 데이터 저장 시 .xlsx 형식 유지 – .xls는 행 수 제한이 있음
• 조건부 서식은 꼭 필요한 범위에만 적용
• COUNTIF 범위는 실제 데이터 범위로 정확하게 지정
• 대용량 파일은 작업 전 복사본 저장 필수
• 피벗 테이블로 중복 개수를 집계하는 방법도 검토

▲ 엑셀 버전에 따라 인터페이스가 조금씩 다를 수 있습니다. 2016 이상 버전이라면 위 설명과 거의 동일하게 따라갈 수 있습니다.

솔직히 이런 기능들을 굳이 인터넷에서 찾아봐야 한다는 게 좀 불편하죠. 엑셀이 워낙 기능이 많다 보니 자주 쓰지 않는 기능은 매번 까먹게 되는데, 위 방법 중 하나만 손에 익혀두면 그나마 낫습니다.

자주 묻는 질문 FAQ

Q. 중복값 찾기 색 표시를 다시 없애려면 어떻게 하나요?
조건부 서식 – 규칙 관리 – 해당 규칙 선택 후 삭제하면 됩니다. 또는 범위를 선택하고 조건부 서식 – 규칙 지우기 – 선택한 셀의 규칙 지우기로도 가능합니다.

Q. COUNTIF로 찾은 중복값만 한꺼번에 삭제할 수 있나요?
가능합니다. COUNTIF 결과 열을 기준으로 필터를 걸어 2 이상인 행만 보이게 한 다음, 해당 행을 모두 선택해서 삭제하면 됩니다. 단, 첫 번째 항목도 같이 사라지니 주의해야 합니다.

Q. 엑셀 중복값 찾기를 할 때 대소문자 구분이 되나요?
기본적으로는 대소문자를 구분하지 않습니다. “Apple”과 “apple”을 같은 값으로 처리합니다. 대소문자를 구분해야 한다면 EXACT 함수를 조합한 배열 수식을 써야 하는데, 일반적인 상황에서는 거의 필요 없습니다.

Q. 두 시트 간에 중복값을 찾으려면 어떻게 하나요?
COUNTIF 범위를 다른 시트로 지정하면 됩니다. 예를 들어 Sheet2의 A열과 비교할 때는 =COUNTIF(Sheet2!$A:$A,A1) 형태로 입력합니다. 시트명 뒤에 느낌표(!)를 붙이는 게 핵심입니다.

Q. 중복값이 많을 때 피벗 테이블이 더 편리할 수 있나요?
맞습니다. 특히 중복된 값이 각각 몇 번씩 나타나는지 집계가 필요할 때는 피벗 테이블이 훨씬 빠릅니다. 데이터를 피벗으로 끌어다 놓고 개수를 집계하면 한눈에 파악됩니다.