엑셀에서 조건부 서식으로 중복 값 강조하기

본 글은 Excel 조건부 서식을 이용하여 중복 값을 신속하게 시각화하고, 고급 규칙·오류 대처·자동화까지 포괄적으로 설명하여 현업 사용자가 데이터 정합성을 즉시 검증하고 품질 문제를 사전에 방지하도록 돕는 것을 목적으로 한다.

중복 값 강조 기본 절차

1. 검사할 셀 범위를 선택한다.
2. 홈 ▸ 조건부 서식 ▸ 셀 강조 규칙 ▸ 중복 값을 클릭한다. :contentReference[oaicite:0]{index=0}
3. 대화 상자에서 중복을 선택하고 서식(예: 밝은 빨강 채우기 색·짙은 빨강 텍스트)을 지정한다.
4. 확인을 누르면 선택 범위에 중복 값이 즉시 강조된다.

고급 시나리오별 규칙 작성

시나리오	사용 수식	적용 범위
열 A와 B를 합쳐 중복 검사	=COUNTIFS($A:$A,$A1,$B:$B,$B1)>1	A1:B1000
대·소문자 구분 중복 검사	=SUMPRODUCT(--EXACT($C$2:$C$500,C2))>1	C2:C500
보이는 값(필터 제외)만 검사	=COUNTIFS($D$2:$D$500,D2,$E$2:$E$500,"<>")>1	D2:D500
7일 이내 날짜 중복	=AND(TODAY()-$E2<=7,COUNTIF($E:$E,$E2)>1)	E2:E100

수식 규칙을 선택할 때 새 규칙 ▸ 수식을 사용하여 서식을 지정할 셀 결정을 이용하면 원하는 로직을 자유롭게 구현할 수 있다. :contentReference[oaicite:1]{index=1}

실무 예시: 고객명·전화번호 중복 존재 여부 즉석 검증

'가정: A열=고객명, B열=전화번호
1단계  : A:B 범위 선택
2단계  : 조건부 서식 ▸ 새 규칙 ▸ 수식
3단계  : 수식 입력 =COUNTIFS($A:$A,$A1,$B:$B,$B1)>1
4단계  : 서식 ▸ 채우기 ▸ 주황색 선택 ▸ 확인
5단계  : 필요 시 필터 ▸ 셀 색으로 주황 필터링 → 중복 고객 확인 완료

숫자 서식·텍스트 형식 관련 주의 사항

• 같아 보이는 숫자라도 내부적으로 텍스트 vs 숫자 형식이 다르면 COUNTIF가 구분하여 중복을 인식하지 못한다. :contentReference[oaicite:2]{index=2}
• 일관된 결과를 얻으려면 검사 전 빈 셀에 1을 복사 ▸ 서식 없는 붙여넣기 ▸ 곱하기 방식으로 숫자 강제 변환을 수행한다.
• 길이가 15자 초과인 숫자(신용카드 번호 등)는 과학적 표기로 인해 마지막 자리가 0으로 잘려 중복 판정이 오류를 낳을 수 있다. TEXT 함수로 문자열 처리 후 검사한다.

중복 값 확인 후 후속 조치

대량 데이터 시 대안	방법
인접 열에 “중복/고유” 태그 붙이기	=IF(COUNTIF($A:$A,A2)>1,"중복","고유") 작성 후 채우기
중복만 필터링 후 삭제	데이터 ▸ 고급 필터 ▸ 고유 레코드 제외 체크
Power Query로 고유 레코드 테이블 생성	데이터 ▸ 데이터 가져오기 ▸ 테이블/범위에서 ▸ 행 제거 ▸ 중복 제거
피벗테이블로 중복 빈도 통계	행 레이블에 대상 필드를 배치, 값 영역에 해당 필드 개수

조건부 서식은 시각화 도구이다. 대량 데이터 정제·집계에는 Power Query, Remove Duplicates, 피벗테이블을 병행해야 완전한 데이터 품질 관리 프로세스가 구축된다. :contentReference[oaicite:3]{index=3}

VBA 매크로: 동적 범위의 중복 강조 자동화

'선택 영역 자동 탐지 후 중복 조건부 서식 적용
Sub HighlightDup()
    Dim rng As Range
    On Error Resume Next
    Set rng = Application.InputBox(Prompt:="중복을 검사할 범위를 지정하십시오.", Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub
    With rng
        .FormatConditions.Delete
        .FormatConditions.AddUniqueValues
        .FormatConditions(.FormatConditions.Count).DupeUnique = xlDuplicate
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .TintAndShade = 0
            .Color = vbYellow
        End With
    End With
    MsgBox rng.Address(False, False) & " 범위에 중복 강조 규칙을 적용했다.", vbInformation
End Sub

위 매크로는 범위를 직접 지정받아 Yellow 채우기 색으로 중복을 강조한다. 대용량 보고서 자동화에 유용하다.

문제 해결 체크리스트

점검 항목	예/아니오	조치
범위가 표(Table) 구조인가?		범위를 표로 변환 시 규칙이 자동 확장된다.
범위 일부만 선택했는가?		전열 선택(Ctrl+A) 후 다시 규칙 적용한다.
규칙 우선순위 충돌 여부		조건부 서식 관리자에서 위/아래 이동으로 우선순위 조정
숫자·텍스트 혼합 데이터인가?		텍스트→숫자, 숫자→텍스트로 일괄 변환 후 재검사
조건부 서식 총 개수 64개 초과?		불필요 규칙 삭제 또는 워크시트 분할

FAQ

Q. 조건부 서식을 적용했는데 일부 중복 값이 강조되지 않는다.

A. 범위 외부 셀에서 같은 값을 찾으려면 COUNTIF($A:$A,A1)처럼 열 전체를 지정해야 한다. 또한 숫자 문자열 혼합이 원인일 수 있으므로 VALUE 함수로 강제 변환 후 재적용한다. :contentReference[oaicite:4]{index=4}

Q. 중복 값 강조 후 값이 변경되었을 때 서식이 즉시 업데이트되지 않는다.

A. 계산 자동이 수동이면 규칙이 재평가되지 않는다. 수식 ▸ 계산 옵션 ▸ 자동으로 설정하거나 F9를 눌러 수동 재계산한다.

Q. 피벗테이블 값 영역에는 중복 강조가 작동하지 않는다.

A. 피벗테이블은 내부적으로 요약된 값 표이므로 셀 강조 규칙이 제한된다. 피벗이 아닌 원본 범위에서 중복 값을 식별하거나, 피벗 데이터 분석 ▸ 값 필드 설정으로 고유 개수를 구해 대체한다. :contentReference[oaicite:5]{index=5}

Q. 조건부 서식 규칙을 다른 시트에 복사하려면?

A. 서식 복사 브러시 또는 홈 ▸ 서식 ▸ 서식 복사 붙여넣기를 사용한다. 단, COUNTIF 등 범위 참조가 절대주소($ 부호)인지 확인 후 경로를 수정한다.

Q. 중복 값이 하이라이트되면 자동으로 팝업 경고를 띄우고 싶다.

A. VBA Worksheet_Change 이벤트에서 Intersect(Target, Range("검사범위"))를 감지해 IF COUNTIF 조건으로 MsgBox를 표시하면 실시간 경고가 가능하다.