티스토리 뷰
엑셀에서 중복값을 처리하는 방법은 목적에 따라 셋으로 갈립니다. 눈으로 확인만 하려면 조건부서식, 몇 번 나왔는지 세려면 COUNTIF, 실제로 지우려면 데이터 → 중복된 항목 제거입니다. 순서를 지키지 않고 바로 제거부터 누르면 되돌릴 수 없는 데이터 손실이 납니다. 아래에서 세 방법을 상황별로 정리했습니다.
이 글에서 답하는 질문
- 중복값을 색으로 표시하려면?
- 몇 개가 중복인지 개수를 세려면?
- 중복을 실제로 지우려면?
- 두 개 이상의 열을 동시에 봐야 하는 중복은?
- 중복 제거 전에 반드시 해야 할 일은?
1. 목적부터 정하세요
| 목적 | 방법 | 원본 변경 |
|---|---|---|
| 어디가 중복인지 눈으로 보고 싶다 | 조건부서식 → 중복 값 | 없음 |
| 몇 번 나왔는지 세고 싶다 | COUNTIF | 없음 |
| 첫 번째만 남기고 지우고 싶다 | 데이터 → 중복된 항목 제거 | 있음 (복구 불가) |
| 고유값 목록만 뽑고 싶다 | 고급 필터 또는 피벗테이블 | 없음 |
가장 많이 하는 실수가 확인 없이 "중복된 항목 제거"를 누르는 것입니다. 이 기능은 실행 취소를 하면 되돌아오긴 하지만, 저장 후 파일을 닫으면 끝입니다. 작업 전에 시트를 복사해 두세요.
2. 방법 1 — 조건부서식으로 색칠하기 (가장 안전)
1단계. 검사할 범위를 드래그로 선택
2단계. 홈 탭 → 조건부 서식 → 셀 강조 규칙 → 중복 값
3단계. 서식(진한 빨강 텍스트가 있는 연한 빨강 채우기 등)을 고르고 확인
선택한 범위 안에서 두 번 이상 나오는 값이 전부 칠해집니다. 원본은 전혀 건드리지 않습니다.
주의할 점 하나. 이 기능은 첫 번째 항목까지 포함해서 전부 칠합니다. "두 번째부터"만 표시하고 싶다면 조건부서식의 새 규칙 → 수식을 사용해 서식을 지정할 셀 결정을 고르고 다음 수식을 넣습니다.
=COUNTIF($A$2:$A2, $A2) > 1범위의 시작($A$2)은 절대참조, 끝(A2)은 상대참조인 게 핵심입니다. 이렇게 하면 위에서부터 누적으로 세기 때문에 두 번째 등장부터 칠해집니다.
3. 방법 2 — COUNTIF로 중복 개수 세기
옆 열에 수식을 넣어 각 값이 몇 번 나오는지 셉니다.
=COUNTIF($A$2:$A$100, A2)결과가 1이면 유일한 값, 2 이상이면 중복입니다.
중복 여부만 글자로 보고 싶다면:
=IF(COUNTIF($A$2:$A$100, A2)>1, "중복", "")몇 번째 등장인지 순번을 매기고 싶다면:
=COUNTIF($A$2:$A2, A2)이 수식은 1, 2, 3... 으로 누적 카운트를 매깁니다. 결과가 1인 행만 필터로 걸러내면 원본을 지우지 않고 고유값 목록을 얻을 수 있습니다. 실무에서 가장 자주 쓰는 패턴입니다.
4. 방법 3 — 실제로 제거하기
1단계. 시트 탭에서 마우스 오른쪽 → 이동/복사 → 복사본 만들기 체크 (백업)
2단계. 데이터 범위 안의 아무 셀이나 클릭
3단계. 데이터 탭 → 중복된 항목 제거
4단계. 중복 판단 기준으로 삼을 열을 체크
5단계. 확인 → "n개의 중복된 값이 검색되어 제거되었습니다" 메시지 확인
여기서 4단계가 결정적입니다. 체크한 열의 값이 모두 같아야 중복으로 봅니다.
- 이름 열만 체크 → 동명이인이 지워집니다
- 이름 + 생년월일 체크 → 동명이인은 살아남습니다
즉 체크를 많이 할수록 덜 지워집니다. 무엇을 기준으로 "같은 데이터"라고 볼 것인지 먼저 정하고 들어가세요.
남는 행은 위에서 첫 번째로 등장한 행입니다. 최신 데이터를 남기고 싶다면 제거 전에 날짜 기준 내림차순 정렬을 먼저 하세요.
5. 여러 열을 동시에 봐야 하는 중복
거래처명과 사업자번호를 합쳐서 중복을 판단해야 하는 경우가 흔합니다. COUNTIF로는 열 하나만 볼 수 있으니 COUNTIFS를 씁니다.
=COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)세 개 이상도 인수를 계속 추가하면 됩니다.
=COUNTIFS($A$2:$A$100,A2, $B$2:$B$100,B2, $C$2:$C$100,C2)보조 열을 만들어 값을 이어 붙인 뒤 COUNTIF를 쓰는 방법도 있습니다.
=A2 & "|" & B2구분자로 파이프(|)를 넣는 이유가 있습니다. 구분자 없이 이어 붙이면 "AB" + "C"와 "A" + "BC"가 똑같이 "ABC"가 되어 엉뚱한 중복이 잡힙니다.
6. 중복이 안 잡힐 때 — 원인 4가지
"분명히 똑같아 보이는데 중복으로 안 잡혀요"의 원인은 거의 정해져 있습니다.
앞뒤 공백
가장 흔합니다. "삼성전자"와 "삼성전자 "는 다른 값입니다.
=TRIM(A2)TRIM으로 정리한 열을 만들어 그 열로 비교하세요.
숫자와 텍스트 혼재
"1001"(텍스트)과 1001(숫자)은 다른 값입니다. 셀 왼쪽 위에 초록색 삼각형이 있으면 텍스트 형식입니다. 데이터 → 텍스트 나누기를 그냥 마침까지 눌러도 숫자로 변환됩니다.
보이지 않는 문자
웹이나 시스템에서 내려받은 데이터에 줄바꿈이나 특수문자가 섞인 경우입니다.
=CLEAN(TRIM(A2))대소문자
COUNTIF와 중복된 항목 제거는 영문 대소문자를 구분하지 않습니다. "abc"와 "ABC"를 다른 값으로 봐야 한다면 EXACT와 SUMPRODUCT를 조합해야 합니다.
=SUMPRODUCT(--EXACT($A$2:$A$100, A2))7. 고유값 목록만 뽑는 방법 (원본 보존)
피벗테이블 이용
행 영역에 해당 열 하나만 올리면 고유값 목록이 그대로 나옵니다. 개수까지 같이 보려면 값 영역에도 같은 열을 올리고 개수로 설정합니다. 가장 빠르고 안전한 방법입니다.
고급 필터 이용
데이터 탭 → 고급 → "다른 장소에 복사" 선택 → "동일한 레코드는 하나만" 체크
COUNTIF 필터 이용
앞서 본 =COUNTIF($A$2:$A2, A2) 결과가 1인 행만 필터링합니다.
8. 첨부 실습파일 사용법
첨부 엑셀에는 시트 세 개가 있습니다.
- 원본데이터: 의도적으로 공백·대소문자·텍스트형 숫자 문제를 섞어 둔 거래처 목록입니다.
- 중복검사: COUNTIF, COUNTIFS, 누적카운트, TRIM 적용 결과가 열별로 나란히 계산됩니다. 왜 어떤 건 잡히고 어떤 건 안 잡히는지 바로 보입니다.
- 정리결과: 고유값만 남긴 결과표입니다.
노란색 칸에 값을 직접 추가해 보면 수식이 어떻게 반응하는지 확인할 수 있습니다.
마무리
세 줄 요약입니다. 확인은 조건부서식, 카운트는 COUNTIF, 제거는 백업 후 중복된 항목 제거. 그리고 중복이 이상하게 안 잡히면 십중팔구 공백입니다. TRIM부터 걸어 보세요.
관련 글
- 엑셀 COUNTIF·COUNTIFS 함수 사용법 완전정리
- 엑셀 조건부서식 활용법 완전정리
- 엑셀 피벗테이블 만드는 법
- 엑셀 IFERROR 함수 완전정리 — 오류 6가지 한 번에 처리하는 법
- 엑셀 VLOOKUP 함수 완전정리
공식 출처
- Microsoft 지원 — 고유 값 필터링 또는 중복 값 제거: https://support.microsoft.com/ko-kr/office/%EA%B3%A0%EC%9C%A0-%EA%B0%92-%ED%95%84%ED%84%B0%EB%A7%81-%EB%98%90%EB%8A%94-%EC%A4%91%EB%B3%B5-%EA%B0%92-%EC%A0%9C%EA%B1%B0-ccf664b0-81d6-449b-bbe1-8daaec1e83c2
- Microsoft 지원 — 중복 값 찾아 제거하기: https://support.microsoft.com/ko-kr/office/%EC%A4%91%EB%B3%B5-%EA%B0%92-%EC%B0%BE%EC%95%84-%EC%A0%9C%EA%B1%B0%ED%95%98%EA%B8%B0-00e35bea-b46a-4d5d-b28e-66a552dc138d