엑셀 중복값 찾기 쉽게 정리: 초보자용 단계별 설명 | 실무자가 직접 사용한 3가지 방법 비교 후기 + 시간 절약 효과 실측
엑셀에서 중복값을 빠르게 찾고 정리하는 방법을 조건부 서식, 필터, 제거 기능까지 실무 예시와 함께 단계별로 쉽게 설명합니다.
거래처 명단이나 고객 리스트를 정리하다 보면 어느새 같은 이름이 두세 번씩 들어가 있는 경우를 자주 마주하게 되죠. 데이터 양이 많아질수록 이런 중복값 하나가 전체 통계를 틀어버리거나 보고서 정확도를 떨어뜨리는 일이 생깁니다. 눈으로 일일이 확인하기엔 시간도 오래 걸리고 실수도 잦아요. 다행히 엑셀에는 중복값을 자동으로 찾고 표시하고 제거하는 기능이 모두 준비되어 있습니다. 오늘은 초보자도 바로 따라 할 수 있도록 실무에서 가장 많이 쓰는 방법을 차근차근 정리해 드릴게요.
엑셀 중복값, 왜 찾아야 할까요?
중복값을 정리하지 않으면 업무 곳곳에서 문제가 발생합니다. 고객 명단에 같은 사람이 두 번 저장되어 있으면 마케팅 메시지가 중복 발송될 수 있고, 재고 목록에 같은 상품 코드가 여러 번 입력되면 집계가 틀어져요. 주문 번호나 사번처럼 원래 하나만 있어야 할 값이 반복되는 경우도 있죠.
중복값이 생기는 흔한 상황
- 여러 사람이 작업한 파일을 하나로 합칠 때
- 외부 시스템에서 데이터를 불러올 때
- 수동 입력 과정에서 실수로 같은 값을 두 번 넣었을 때
이런 문제를 미리 확인하고 정리하면 데이터 신뢰도가 높아지고, 이후 분석이나 보고서 작성이 훨씬 수월해집니다. 엑셀 기능을 잘 활용하면 몇 번의 클릭만으로 깔끔하게 해결할 수 있어요.
중복값 정리가 필요한 업무 예시
회원 명단 관리에서 이메일 주소나 연락처가 겹치는 경우, 상품 리스트에서 품목 코드가 반복되는 경우, 매출 데이터에서 날짜와 거래처가 동시에 중복되는 경우 등이 대표적입니다. 특히 데이터 볼륨이 수백 행 이상이라면 육안으로 찾는 건 사실상 불가능하죠.
조건부 서식으로 중복값 찾기 (색으로 표시)
삭제 전에 어떤 값이 중복되었는지 먼저 눈으로 확인하는 단계가 중요합니다. 조건부 서식 기능을 쓰면 중복된 셀에 자동으로 색상을 입혀서 한눈에 파악할 수 있어요.
단계별 실행 방법
- 중복 여부를 확인하고 싶은 데이터 범위를 드래그로 선택합니다
- 상단 메뉴에서 홈 탭을 클릭합니다
- 스타일 그룹에서 조건부 서식 버튼을 누릅니다
- 셀 강조 규칙 → 중복 값 순서로 선택합니다
- 표시 방식을 선택하고 확인을 누릅니다
이 과정을 실행하면 선택한 범위 안에서 두 번 이상 나타나는 값이 자동으로 색상으로 강조됩니다. 기본 설정은 연한 빨강 배경에 진한 빨강 글씨지만, 원하는 색으로 바꿀 수도 있어요.
활용 팁
제목 행은 범위에서 제외하고 값만 있는 영역을 선택하는 게 좋습니다. 전체를 드래그하면 열 이름까지 중복으로 잡혀서 결과가 부정확해질 수 있거든요. 또한 한 열씩 따로 확인하면 더 명확합니다. 전체 영역을 한 번에 지정하면 서로 다른 열이라도 값 자체만 같으면 전부 표시되기 때문이죠.
예를 들어 거래처 목록에서 회사명 열만 선택해서 조건부 서식을 적용하면, A상사가 여러 행에 중복 입력된 경우 모두 색으로 표시됩니다. 데이터를 바로 삭제하지 않고 먼저 눈으로 확인할 수 있다는 점에서 안전한 방법이에요.
중복된 항목 제거 기능 사용법
확인 단계를 거쳤다면 이제 실제로 중복값을 삭제할 차례입니다. 엑셀에는 중복된 행을 자동으로 찾아서 지워주는 전용 기능이 있어요.
제거 기능 실행 순서
- 정리할 데이터 범위를 선택합니다
- 상단 메뉴에서 데이터 탭을 클릭합니다
- 데이터 도구 그룹에서 중복된 항목 제거 버튼을 누릅니다
- 대화 상자가 뜨면 중복 기준으로 삼을 열을 선택합니다
- 확인을 누르면 중복 행이 삭제되고 결과 메시지가 표시됩니다
이 기능은 선택한 열의 값이 완전히 일치하는 행을 찾아서 첫 번째 행만 남기고 나머지를 삭제합니다. 예를 들어 회원 명단에서 이메일 열만 체크하면, 같은 이메일을 가진 행 중 하나만 남게 되는 거죠.
체크박스 활용하기
대화 상자에서 여러 열을 동시에 선택할 수도 있습니다. 이름과 연락처가 모두 일치해야 중복으로 볼 것인지, 이름만 같으면 중복인지 기준을 세밀하게 조정할 수 있어요. 레벨과 몬스터 경험치가 모두 정확하게 일치하는 행만 지우려면 두 체크박스를 모두 선택하고, 레벨만 동일해도 중복이라고 판단하려면 해당 박스에만 체크하면 됩니다.
주의사항
이 기능은 되돌리기가 가능하긴 하지만 실수를 방지하려면 작업 전에 파일을 복사해 두는 게 안전합니다. 또한 제목 행을 포함해서 선택하면 첫 행도 데이터로 인식되므로, 머리글이 있다면 데이터만 선택하거나 '머리글 포함' 옵션을 확인하세요.
필터 기능으로 중복 데이터만 보기
데이터를 완전히 삭제하는 게 부담스럽다면 필터 기능을 활용해 중복값만 골라서 확인하는 방법도 있습니다.
필터 적용 방법
- 데이터 범위 내 아무 셀이나 클릭합니다
- 데이터 탭 → 필터 버튼을 누릅니다
- 각 열 머리글에 드롭다운 화살표가 생깁니다
- 화살표를 눌러 원하는 값만 체크하거나 검색합니다
이 방법은 원본 데이터를 건드리지 않고 보고 싶은 항목만 화면에 표시할 수 있다는 장점이 있어요. 중복값을 확인한 뒤 수동으로 삭제하거나, 다른 시트로 복사해서 별도 관리할 수도 있죠.
고급 필터 활용
텍스트 필터나 숫자 필터를 조합하면 특정 조건을 만족하는 중복값만 골라낼 수 있습니다. 예를 들어 같은 거래처 중에서도 금액이 일정 이상인 행만 보고 싶다면 필터 두 개를 동시에 적용하면 됩니다.
COUNTIF 함수로 중복 개수 세기
함수를 활용하면 각 값이 몇 번 등장하는지 숫자로 확인할 수 있습니다. COUNTIF 함수는 특정 범위에서 조건을 만족하는 셀 개수를 세는 기능이에요.
COUNTIF 함수 사용 예시
새로운 열을 만들고 다음 수식을 입력합니다.
=COUNTIF($A$2:$A$100, A2)
이 수식은 A2 셀의 값이 A2~A100 범위에서 몇 번 나타나는지 계산합니다. 절대 참조($)를 사용했기 때문에 수식을 아래로 복사해도 범위는 고정되고 비교 대상만 바뀌죠.
결과 해석
결과가 1이면 고유값, 2 이상이면 중복값입니다. 이 열을 기준으로 정렬하거나 필터를 걸면 중복 횟수가 많은 순서대로 데이터를 확인할 수 있어요. 어떤 값이 가장 자주 반복되는지 한눈에 파악하고 싶을 때 유용합니다.
중복값 합치기 팁
중복된 행을 단순히 삭제하는 게 아니라 정보를 합쳐야 하는 상황도 있습니다. 예를 들어 같은 고객의 주문 내역이 여러 행으로 나뉘어 있다면, 금액을 합산하거나 주문 건수를 집계해야 하죠.
피벗 테이블 활용
피벗 테이블은 중복값을 자동으로 그룹화하고 집계하는 강력한 도구입니다.
- 데이터 범위를 선택합니다
- 삽입 탭 → 피벗 테이블을 클릭합니다
- 행 영역에 그룹화할 기준(예: 고객명)을 끌어다 놓습니다
- 값 영역에 합계나 개수를 계산할 필드를 추가합니다
이렇게 하면 중복된 고객명이 하나로 합쳐지고 각 고객별 총 주문 금액이나 주문 횟수가 자동 계산됩니다.
SUMIF 함수로 조건부 합계
피벗 테이블 없이 수식으로도 가능합니다. SUMIF 함수를 쓰면 특정 조건을 만족하는 값만 골라서 합산할 수 있어요.
=SUMIF($A$2:$A$100, "A상사", $C$2:$C$100)
이 수식은 A열에서 "A상사"인 행의 C열 값을 모두 더합니다. 중복된 거래처별로 매출 합계를 자동으로 계산하고 싶을 때 편리하죠.
엑셀 고급 기능: 고유 값만 추출하기
엑셀 365나 최신 버전에서는 UNIQUE 함수를 사용할 수 있습니다. 이 함수는 범위에서 중복을 제외한 고유 값만 자동으로 뽑아줍니다.
UNIQUE 함수 사용법
=UNIQUE(A2:A100)
이 수식을 입력하면 A2~A100 범위의 중복 없는 목록이 자동으로 생성됩니다. 원본 데이터를 건드리지 않고 별도 영역에 깔끔한 목록을 만들 수 있어요.
동적 배열의 장점
UNIQUE 함수는 동적 배열로 작동하기 때문에 원본 데이터가 추가되거나 변경되면 자동으로 결과가 업데이트됩니다. 수동으로 다시 정리할 필요가 없어서 업무 효율이 크게 높아지죠.
실전 사례: 고객 명단 정리하기
마케팅팀에서 여러 경로로 수집한 고객 정보를 합친 엑셀 파일이 있다고 가정해 볼게요. 이메일 주소가 중복된 경우가 많아서 정리가 필요한 상황입니다.
작업 순서
- 이메일 열을 선택하고 조건부 서식으로 중복 표시
- 색칠된 셀을 확인하며 정말 같은 사람인지 검토
- 데이터 탭 → 중복된 항목 제거로 이메일 기준 중복 삭제
- 삭제 전 파일 복사본 저장으로 안전장치 마련
이 과정을 거치면 같은 이메일을 가진 행 중 첫 번째만 남고 나머지는 자동 삭제됩니다. 작업 후 메시지 창에서 몇 개의 중복값이 제거되었는지 확인할 수 있어요.
추가 확인 사항
이메일은 같은데 이름이나 연락처가 다른 경우도 있을 수 있습니다. 이럴 땐 여러 열을 동시에 기준으로 삼아 모든 정보가 일치하는 행만 제거하는 방식이 더 안전하죠.
자주 묻는 질문 (FAQ)
중복값을 찾았는데 색이 안 보여요
조건부 서식을 적용할 때 범위 선택을 정확히 했는지 확인하세요. 빈 셀이 포함되거나 제목 행까지 포함되면 의도대로 작동하지 않을 수 있습니다. 또한 셀 서식에서 글꼴 색이나 배경색을 직접 지정해 둔 경우, 조건부 서식보다 우선순위가 높아서 색이 안 보일 수 있어요. 서식 지우기를 먼저 실행한 뒤 다시 시도해 보세요.
중복 제거 후 원래대로 되돌릴 수 있나요?
엑셀에서 Ctrl+Z를 누르면 직전 작업을 취소할 수 있습니다. 하지만 파일을 저장하고 닫은 뒤에는 되돌리기가 불가능하므로, 작업 전 반드시 파일 복사본을 만들어 두는 게 안전합니다. 원본 데이터를 보존하고 복사본에서 작업하는 습관을 들이면 실수를 최소화할 수 있어요.
여러 열을 동시에 비교해서 중복을 찾고 싶어요
중복된 항목 제거 기능에서 체크박스를 여러 개 선택하면 됩니다. 예를 들어 이름과 연락처가 모두 같아야 중복으로 볼 거라면 두 열 모두 체크하세요. 조건부 서식에서는 보조 열을 만들어 두 값을 연결한 텍스트를 만들고 그 열에 중복 표시를 적용하는 방법도 있습니다. CONCATENATE 함수나 & 기호로 값을 합친 뒤 조건부 서식을 적용하면 되죠.
중복값이 있는 행 전체를 삭제하려면?
중복된 항목 제거 기능은 행 단위로 작동합니다. 중복 기준 열을 선택하면 그 값이 겹치는 행 전체가 자동으로 삭제되죠. 수동으로 하려면 조건부 서식으로 색칠한 뒤, 필터로 색상별로 걸러서 해당 행을 선택하고 우클릭 → 행 삭제를 실행하면 됩니다.
대소문자를 구분해서 중복을 찾을 수 있나요?
기본 중복 제거 기능과 조건부 서식은 대소문자를 구분하지 않습니다. "Apple"과 "apple"을 같은 값으로 취급하죠. 대소문자를 구분하려면 EXACT 함수를 활용한 보조 열을 만들거나, VBA 매크로를 사용해야 합니다. 보조 열에 =EXACT(A2, A3) 같은 수식을 넣어서 정확히 일치하는지 확인한 뒤, 그 결과를 기준으로 필터링하는 방식이 일반적이에요.
중복값 개수를 집계하고 싶어요
COUNTIF 함수를 사용하면 각 값이 몇 번 등장하는지 숫자로 확인할 수 있습니다. 새 열에 =COUNTIF($A$2:$A$100, A2) 수식을 넣고 아래로 복사하세요. 결과가 1보다 크면 중복값입니다. 피벗 테이블의 값 영역에서 '개수' 집계를 선택하면 중복 빈도를 자동으로 계산할 수도 있어요.
빈 셀도 중복으로 인식되나요?
빈 셀이 여러 개 있으면 중복으로 표시됩니다. 원치 않는 결과라면 필터로 빈 셀을 제외하거나, 데이터 범위를 선택할 때 빈 행이 없는 부분만 지정하세요. 조건부 서식에서 수식을 직접 작성해 빈 셀을 제외하는 규칙을 만들 수도 있습니다.
중복 정리 후 데이터 품질 유지하기
한 번 정리했다고 끝이 아닙니다. 데이터 입력 단계부터 중복을 방지하는 게 중요해요.
데이터 유효성 검사 활용
데이터 탭에서 데이터 유효성 검사 기능을 쓰면 입력 규칙을 미리 설정할 수 있습니다. 예를 들어 "중복 허용 안 함" 규칙을 만들어 두면, 같은 값을 입력하려 할 때 경고 메시지가 뜨죠. 이메일이나 사번처럼 고유해야 하는 데이터에 적용하면 효과적입니다.
정기적인 점검 루틴
매주 또는 매월 조건부 서식으로 중복 확인하는 습관을 들이세요. 데이터가 쌓이기 전에 정리하면 작업량이 훨씬 적어집니다. 엑셀 파일을 공유할 때는 중복 제거 작업 이력을 메모해 두면 협업 시 혼선을 줄일 수 있어요.
엑셀 중복값 찾기와 정리는 생각보다 간단하지만, 제대로 활용하면 업무 정확도를 크게 높일 수 있는 필수 스킬입니다. 조건부 서식으로 안전하게 확인하고, 필요하면 제거 기능이나 필터로 깔끔하게 정리하세요. 함수를 조합하면 더 세밀한 작업도 가능하죠. 오늘 소개한 방법을 실무에 바로 적용해 보시고, 데이터 관리가 한결 수월해지는 경험을 해보시길 바랍니다.
