이 글의 목적은 Excel에서 조건부 서식을 활용해 중복 데이터를 신속하고 정확하게 식별·강조하는 실무 절차와 고급 공식을 체계적으로 제시하여 데이터 정합성 검증과 오류 예방에 즉시 활용하도록 돕는 것이다.
1. 기본 개념과 적용 범위
조건부 서식은 셀 값이 특정 조건을 충족할 때 서식을 자동으로 적용하는 기능이다. 중복값 강조는 동일한 값이 2회 이상 등장하는 셀을 시각적으로 구분하는 작업이다. 판매리스트, 고객DB, 품목코드, 프로젝트ID, 납품서 등에서 데이터 무결성을 확보하는 데 필수적이다. Windows·Mac·Microsoft 365·Excel 2016 이상 대부분 버전에서 동일한 절차로 사용 가능하다.
주의 : 중복값 강조는 데이터 삭제가 아니라 표시만 바꾸는 것이다. 삭제나 정제는 별도 절차로 수행해야 한다.
2. 가장 빠른 절차: 기본 "중복 값" 규칙
단일 열 또는 범위에서 중복을 강조하는 가장 간단한 방법이다.
- 중복을 검사할 범위를 선택한다. 예:
A2:A2000범위를 선택한다. - 리본 > 홈 > 조건부 서식 > 셀 강조 규칙 > 중복 값을 선택한다.
- 서식 스타일을 선택한다. 예: 연한 빨강 채우기·진한 빨강 텍스트를 선택한다.
- 확인을 누른다. 선택 범위에서 2회 이상 등장하는 모든 값이 강조된다.
이 방식은 공백도 값으로 취급하여 동일 공백이 여러 개면 중복으로 표시한다. 공백을 제외하려면 공식 기반 규칙을 사용해야 한다.
3. 공식 기반 규칙으로 정밀 제어
복수 열 비교, 대소문자 구분, 첫 발생만 표시, 공백 제외 등 고급 요구에 공식 기반 규칙을 사용한다. 경로는 다음과 같다.
홈 > 조건부 서식 > 새 규칙 > 수식을 사용하여 서식을 지정할 셀 결정을 선택한다.
3.1 단일 열 중복: COUNTIF 사용
범위가 $A$2:$A$2000일 때 각 셀의 중복 여부를 검사하는 공식이다.
=COUNTIF($A$2:$A$2000, A2)>1 적용 범위를 $A$2:$A$2000으로 지정하고 위 공식을 입력한다. 중복 전체를 강조한다.
3.2 공백 제외 중복
=AND(A2<>"", COUNTIF($A$2:$A$2000, A2)>1) 빈 셀은 강조하지 않는다.
3.3 여러 열을 묶어 "조합 키" 중복 검사
예를 들어 고객ID(A열)와 주문일(B열)의 조합이 중복인지 검사한다.
=COUNTIFS($A$2:$A$2000, $A2, $B$2:$B$2000, $B2)>1 적용 범위는 $A$2:$B$2000으로 지정한다.
3.4 첫 발생은 제외하고 "중복 발생분만" 강조
첫 번째 값은 정상으로 두고 두 번째 이후만 표시한다. 데이터가 위에서 아래로 정렬되어 있다고 가정한다.
=COUNTIF($A$2:A2, A2)>1 적용 범위는 $A$2:$A$2000이다. 동일 값의 2번째 등장부터 강조한다.
3.5 대소문자 구분 엄격 비교
기본 COUNTIF는 대소문자를 구분하지 않는다. 엄격 구분이 필요하면 SUMPRODUCT와 EXACT를 사용한다.
=SUMPRODUCT(--EXACT($A$2:$A$2000, A2))>1 이 공식은 "abc"와 "ABC"를 다른 값으로 본다.
3.6 앞뒤 공백·숨은 문자 제거 후 비교
입력값에 공백이나 비인쇄 문자가 섞이면 겉보기에는 같아도 다른 값으로 취급된다. 가상 정규화 함수를 공식 안에서 적용한다.
=COUNTIF( INDEX(TRIM(CLEAN($A$2:$A$2000)),0), TRIM(CLEAN(A2)) )>1 배열 취급이 필요한 구버전에서 오류가 나면 데이터 정제 후 비교하는 것이 안전하다.
3.7 부분 문자열 기준 중복
예: 하이픈을 제거한 "숫자만" 비교가 필요할 때이다.
=LET( rng, $A$2:$A$2000, cur, A2, norm_rng, BYROW(rng, LAMBDA(r, TEXTJOIN("",,IF(ISNUMBER(--MID(r, SEQUENCE(LEN(r)), 1)), MID(r, SEQUENCE(LEN(r)), 1), "")))), norm_cur, TEXTJOIN("",,IF(ISNUMBER(--MID(cur, SEQUENCE(LEN(cur)), 1)), MID(cur, SEQUENCE(LEN(cur)), 1), "")), COUNTIF(norm_rng, norm_cur)>1 ) Microsoft 365 동적 배열 함수 환경에서 사용 가능하다.
4. 범위 선택 전략과 적용 실수 방지
조건부 서식은 "적용 대상" 범위와 수식의 상대·절대 참조가 일치해야 올바르게 동작한다.
- 열 전체를 검사하면
$A:$A처럼 지정한다. 대용량 시 성능 부담이 크므로 데이터 영역으로 한정하는 것이 바람직하다. - 수식의 기준 셀은 적용 범위의 좌상단을 가리키게 한다. 예: 범위
$A$2:$A$2000에 수식COUNTIF($A$2:$A$2000, A2)를 사용한다. - 복수 열 비교 시 모든 열을 적용 범위에 포함한다. 예:
$A$2:$C$2000에COUNTIFS공식 사용한다.
주의 : 수식 내 셀 참조에 달러기호가 과도하거나 부족하면 엉뚱한 셀이 기준이 된다. 적용 범위를 바꿀 때 수식의 참조도 함께 검증해야 한다.
5. 시나리오별 권장 규칙 요약
| 시나리오 | 권장 규칙 | 공식 또는 메뉴 | 비고 |
|---|---|---|---|
| 단일 열 단순 중복 | 기본 "중복 값" 규칙 | 홈 > 조건부 서식 > 셀 강조 규칙 > 중복 값 | 가장 빠르다 |
| 공백 제외 | 공식 기반 | =AND(A2<>"", COUNTIF($A$2:$A$2000, A2)>1) | 빈 셀 무시 |
| 여러 열 조합 키 | 공식 기반 | =COUNTIFS($A$2:$A$2000,$A2, $B$2:$B$2000,$B2)>1 | 조합 기준 |
| 첫 발생 제외 | 공식 기반 | =COUNTIF($A$2:A2, A2)>1 | 2번째 이후만 |
| 대소문자 구분 | 공식 기반 | =SUMPRODUCT(--EXACT($A$2:$A$2000, A2))>1 | 엄격 비교 |
| 공백·제어문자 제거 | 공식 기반 | TRIM·CLEAN 결합 | 데이터 정제 |
| 부분 문자열 기준 | 공식 기반 | LET·BYROW·SEQUENCE | 동적 배열 |
6. 대량 데이터 성능 최적화
- 적용 범위를 실제 데이터 행으로 한정한다. 예:
A2:A50000처럼 지정한다. - 가능하면 기본 "중복 값" 규칙을 우선 고려한다. 내부 최적화가 잘 되어 있다.
- 공식 기반 규칙은
COUNTIF/COUNTIFS처럼 단일 패스 집계를 사용한다. 복잡한SEARCH·TEXTJOIN조합은 최소화한다. - 필요 시 표 개체(엑셀 테이블)를 사용한다. 열 자동 확장과 구조적 참조로 유지보수가 쉽다.
7. 중복값 강조와 삭제·요약의 차이
강조는 시각화이고, 삭제는 레코드를 제거하며, 요약은 중복을 그룹화한다. 각각의 도구가 다르다.
- 강조: 조건부 서식으로 색을 입힌다. 원본은 유지된다.
- 삭제: 데이터 > 중복 제거로 영구 삭제한다. 되돌릴 수 있도록 사본을 만들거나 Ctrl+Z를 고려한다.
- 요약: 피벗테이블,
UNIQUE함수, Power Query 그룹화로 중복을 분석한다.
주의 : 중복 제거는 되돌리기 이외 복구 수단이 없다. 원본 백업을 권장한다.
8. 동적 배열 함수로 보조 리스트 생성
중복값만 별도 시트로 뽑아 검토할 수 있다.
8.1 중복된 값 목록
=LET( u, UNIQUE(A2:A2000), FILTER(u, COUNTIF(A2:A2000, u)>1) ) 중복 후보만 추출한다.
8.2 각 값의 출현 횟수
=LET( u, UNIQUE(A2:A2000), HSTACK(u, COUNTIF(A2:A2000, u)) ) 값과 빈도를 나란히 표시한다.
9. 다른 시트·범위 간 교차 중복
시트1의 목록이 시트2와 겹치는지 확인하여 시트1에서만 강조한다.
=COUNTIF(Sheet2!$A$2:$A$5000, A2)>0 적용 범위는 시트1의 $A$2:$A$5000이고 수식은 시트2 범위를 절대참조로 고정한다.
10. 코드·모델·자산ID 등 "형식 차이" 중복 대책
하이픈, 공백, 대소문자 차이로 인해 같은 ID가 다르게 기록될 수 있다. 이 경우 비교 전 "정규화"를 수행한다.
- 대소문자 통일:
UPPER또는LOWER로 변환 후 비교한다. - 하이픈·공백 제거:
SUBSTITUTE(텍스트,"-",""),SUBSTITUTE(텍스트," ","")를 적용한다. - 숫자만 추출: 365 환경에서
TEXTSPLIT·TEXTJOIN조합 또는 위 3.7 공식을 활용한다.
11. 실무 예제: 고객DB 중복 이메일 강조
- 범위
$D$2:$D$20000에 이메일이 있다고 가정한다. - 조건부 서식 > 새 규칙 > 수식 사용을 선택한다.
- 수식 입력:
=AND($D2<>"", COUNTIF($D$2:$D$20000, $D2)>1) - 서식에서 채우기 색을 지정한다.
- 확인 후 필터에서 셀 색으로 필터링하여 중복 레코드만 검토한다.
12. 실무 예제: 다중 키(이름+생년월일) 중복
- 이름은 A열, 생년월일은 B열에 있다.
- 적용 범위를
$A$2:$B$30000로 지정한다. - 수식 입력:
=AND($A2<>"", $B2<>"", COUNTIFS($A$2:$A$30000,$A2, $B$2:$B$30000,$B2)>1) 동일 이름과 동일 생년월일 조합만 강조한다.
13. 서식 디자인 가이드
- 채우기 색은 주황 또는 빨강 계열을 선호한다. 색맹 친화성을 위해 테두리 두껍게와 글꼴 굵게를 병행한다.
- 조건부 서식 규칙 관리자에서 규칙 우선순위를 명확히 하여 다른 규칙과 충돌을 줄인다.
- 테마 색을 사용하면 인쇄·다크모드에서 가독성이 유지된다.
14. 규칙 관리와 유지보수
- 홈 > 조건부 서식 > 규칙 관리에서 현재 워크시트 규칙을 확인한다.
- 적용 대상 범위를 표로 변환하면 데이터 확장 시 자동으로 범위가 늘어난다.
- 규칙이 많아지면 성능이 저하된다. 동일한 목적의 규칙은 병합하고 적용 범위를 최소화한다.
15. Power Query로 사전 중복 정리
조건부 서식으로 가시화하기 전에 Power Query에서 사전 정제를 수행하면 품질이 높아진다.
- 열 정리: 공백 자르기, 대소문자 통일, 형식 변환을 적용한다.
- 그룹화로 중복 빈도를 집계한다.
- 쿼리 결과를 시트로 로드한 뒤 조건부 서식을 적용한다.
16. 버전·플랫폼 유의사항
- Microsoft 365는 동적 배열 함수와
LET지원으로 정규화 로직을 수식에 통합하기 쉽다. - Excel 2016 이하에서는 배열 수식 입력 시 Ctrl+Shift+Enter가 필요할 수 있다.
- Mac에서도 메뉴 구조는 유사하다. 단축키는 일부 다를 수 있다.
17. 검증 체크리스트
| 항목 | 체크 포인트 | 결과 |
|---|---|---|
| 적용 범위 | 데이터 영역만 선택했는가 | 예/아니오 |
| 참조 고정 | 수식의 절대/상대 참조가 올바른가 | 예/아니오 |
| 공백 처리 | 빈 셀을 제외하도록 설계했는가 | 예/아니오 |
| 정규화 | TRIM/CLEAN/대소문자 통일을 적용했는가 | 예/아니오 |
| 서식 충돌 | 다른 조건부 규칙과 우선순위 충돌이 없는가 | 예/아니오 |
| 성능 | 불필요한 전체열 지정이나 복잡 수식이 없는가 | 예/아니오 |
18. 문제 해결 가이드
- 중복이 분명한데 강조되지 않는다: 숨은 공백 또는 제어문자를 의심한다.
LEN(A2)로 길이를 비교한다. - 서식이 엉뚱한 범위에 적용된다: 규칙 관리자에서 적용 범위와 기준 셀의 상대참조를 점검한다.
- 너무 많은 셀이 강조된다: 공식을 단순화하고 조합 키를 올바르게 지정한다. 필요 시 보조열로 키를 만든다.
- 대소문자 구분 필요:
EXACT기반 공식을 사용한다.
19. 보조열 전략으로 유지보수 단순화
조건부 서식의 복잡도를 낮추려면 보조열에서 비교 키를 생성하고 조건부 서식은 보조열을 참조한다.
-- 보조열 G: 공백 제거 + 대문자 통일 =UPPER(TRIM(SUBSTITUTE(A2," ","")))
-- 조건부 서식(적용 범위 A2:A2000)
=COUNTIF($G$2:$G$2000, $G2)>1
보조열을 숨기면 화면이 깔끔하다.
20. 템플릿 규칙 블록
아래 블록을 규칙 관리자에 저장해 비슷한 분석에 재사용한다.
-- 단일 열 중복 전체 =COUNTIF($A$2:$A$2000, A2)>1
-- 공백 제외
=AND(A2<>"", COUNTIF($A$2:$A$2000, A2)>1)
-- 첫 발생 제외
=COUNTIF($A$2:A2, A2)>1
-- 대소문자 구분
=SUMPRODUCT(--EXACT($A$2:$A$2000, A2))>1
-- 두 열 조합 키
=COUNTIFS($A$2:$A$2000,$A2, $B$2:$B$2000,$B2)>1
-- 다른 시트와 교차 비교
=COUNTIF(Sheet2!$A$2:$A$5000, A2)>0
FAQ
기본 "중복 값" 규칙과 COUNTIF 공식의 차이는 무엇인가
기본 규칙은 범위 내부 출현 빈도가 2회 이상이면 모두 강조하는 간편 방식이다. COUNTIF 공식은 공백 제외, 첫 발생 제외, 다른 시트 비교 등 정밀 제어가 가능하다.
서식만으로 중복을 제거할 수 있나
불가능하다. 조건부 서식은 표시 변경만 수행한다. 삭제는 데이터 > 중복 제거를 사용한다.
대소문자 구분이 필요한 이유는 무엇인가
일부 코드 시스템이나 해시값은 대소문자를 구분한다. 이때 EXACT 기반 공식으로 일치 여부를 판정해야 한다.
표 범위를 늘려도 규칙이 자동 확장되나
테이블(삽입 > 표)인 경우 자동 확장된다. 일반 범위는 수동으로 적용 범위를 수정해야 한다.
성능이 느려지면 어떻게 하나
적용 범위를 데이터 구간으로 제한하고, 규칙 수를 줄이며, 보조열 전략으로 공식을 단순화한다.