엑셀 중복값 표시하는 방법: 색상으로 찾고 수식으로 구분하기

엑셀 중복값 표시하는 방법은 중복을 확인할 범위를 선택한 뒤 **[홈] → [조건부 서식] → [셀 강조 규칙] → [중복 값]**을 선택하는 것입니다. 원하는 색상이나 서식을 지정하면 같은 값이 여러 번 입력된 셀이 자동으로 강조 표시됩니다.

중복값을 삭제하지 않고 먼저 확인하려는 목적이라면 이 방법이 가장 안전합니다. 특정 조건을 추가하거나 빈 셀을 제외하려면 조건부 서식의 수식 규칙에서 COUNTIF 함수를 사용할 수 있습니다.

엑셀 중복값 표시하는 방법: 가장 빠르게 중복값 표시하기

1. 중복 여부를 확인할 범위 선택

예를 들어 A열의 데이터에서 중복값을 찾는다면 실제 데이터가 있는 범위를 선택합니다.

• 제목이 1행에 있다면 A2:A400처럼 제목을 제외합니다.
• 여러 열의 값을 함께 확인하려면 필요한 범위를 드래그합니다.
• 표 전체를 선택할 때는 중복을 판단할 열과 제목 행이 포함되었는지 확인합니다.

2. 중복 값 규칙 열기

리본 메뉴에서 다음 순서로 이동합니다.

[홈] → [조건부 서식] → [셀 강조 규칙] → [중복 값]

3. 표시 형식 선택

열린 창에서 중복 값을 적용할지, 고유 값을 적용할지 선택하고 색상이나 서식을 지정합니다. 일반적으로 중복값을 찾을 때는 중복 값 옵션을 그대로 둔 뒤 눈에 잘 띄는 색상을 선택하면 됩니다.

4. 결과 확인

선택한 범위 안에서 같은 값이 두 번 이상 나타난 셀에 서식이 적용됩니다. 이 기능은 셀의 내용을 바꾸거나 삭제하지 않고 화면에서만 구분해 줍니다.

COUNTIF 수식으로 중복값 표시하기

기본 중복 값 규칙만으로 부족하다면 조건부 서식에서 수식 규칙을 만들 수 있습니다. 예를 들어 A2:A400 범위의 중복값을 표시하려면 다음 수식을 사용합니다.

=COUNTIF($A$2:$A$400,A2)>1

수식의 의미는 다음과 같습니다.

• $A$2:$A$400: 중복 여부를 확인할 전체 범위입니다.
• A2: 현재 서식을 적용하는 첫 번째 셀입니다.
• COUNTIF: 지정한 범위에서 특정 값이 몇 번 나타나는지 셉니다.
• >1: 같은 값이 2번 이상이면 TRUE가 되어 서식이 적용됩니다.

수식 규칙 적용 순서

1. 중복을 확인할 범위인 A2:A400을 선택합니다.

2. **[홈] → [조건부 서식] → [새 규칙]**을 선택합니다.

3. 수식을 사용해 서식을 지정하는 규칙을 선택합니다.

4. 수식 입력란에 다음 수식을 입력합니다.

   =COUNTIF($A$2:$A$400,A2)>1
   

5. **[서식]**을 눌러 채우기 색이나 글꼴 색을 지정합니다.

6. 확인을 눌러 규칙을 적용합니다.

범위를 다른 곳에 적용할 때는 고정된 범위와 기준 셀을 함께 바꿔야 합니다. 예를 들어 B2:B100을 대상으로 한다면 다음처럼 작성합니다.

=COUNTIF($B$2:$B$100,B2)>1

빈 셀을 중복으로 표시하지 않으려면

COUNTIF 수식은 빈 셀도 같은 값으로 여러 번 계산할 수 있습니다. 빈 셀을 제외하고 실제 입력값만 중복으로 표시하려면 다음처럼 조건을 추가합니다.

=AND(A2<>"",COUNTIF($A$2:$A$400,A2)>1)

A2<>""는 현재 셀이 비어 있지 않은지 확인하는 조건입니다. 데이터에 빈 행이 섞여 있고 빈 셀까지 색칠되는 경우 이 수식을 사용하세요.

중복값 표시, 필터링, 삭제의 차이

세 기능은 비슷해 보이지만 결과가 다릅니다.

중복을 먼저 검토하려면 조건부 서식이나 고유값 필터링을 사용하세요. **[데이터] → [중복 제거]**는 선택한 범위의 중복 데이터를 영구적으로 삭제할 수 있으므로 실행 전에 원본을 복사해 두는 것이 안전합니다.

중복값이 예상과 다르게 표시될 때

앞뒤에 공백이 들어간 경우

눈으로는 같은 단어처럼 보여도 한 셀에 불필요한 앞뒤 공백이 있으면 다른 값으로 판단될 수 있습니다. 복사해 온 데이터나 다른 시스템에서 내려받은 데이터에서 자주 발생합니다.

공백이 의심되면 셀 내용을 직접 확인하거나 별도 열에서 불필요한 공백을 정리한 뒤 다시 중복 여부를 확인하세요.

날짜와 숫자 형식이 다른 경우

같은 날짜나 숫자처럼 보이더라도 실제 셀 값과 표시 형식이 다르면 결과가 예상과 달라질 수 있습니다. 날짜 형식, 숫자 형식, 텍스트로 저장된 숫자를 함께 확인해야 합니다.

선택 범위가 잘못된 경우

조건부 서식은 선택한 범위 안에서만 중복을 비교합니다. A열과 B열을 각각 따로 비교해야 하는데 전체 표를 선택했거나, 반대로 일부 행만 선택하면 결과가 달라질 수 있습니다. 규칙을 적용하기 전에 비교 대상 열과 시작 행을 확인하세요.

조건부 서식 규칙이 여러 개 있는 경우

같은 범위에 여러 조건부 서식이 적용되어 있으면 다른 규칙의 색상이나 우선순위 때문에 결과가 헷갈릴 수 있습니다. **[홈] → [조건부 서식] → [규칙 관리]**에서 적용 범위와 규칙을 확인하세요.

중복을 표시한 뒤 안전하게 처리하는 순서

1. 원본 파일 또는 원본 시트를 복사합니다.
2. 조건부 서식으로 중복값을 표시합니다.
3. 공백, 날짜 형식, 숫자 형식을 확인합니다.
4. 실제로 삭제할 중복인지 검토합니다.
5. 삭제가 필요할 때만 **[데이터] → [중복 제거]**를 사용합니다.
6. 삭제 후 행 수와 주요 데이터를 다시 확인합니다.

단순히 중복을 찾는 것과 중복 데이터를 삭제하는 것은 다른 작업입니다. 특히 고객명, 주문번호, 재고 목록처럼 같은 값이 정상적으로 반복될 수 있는 데이터에서는 표시 결과를 확인하지 않고 바로 삭제하지 않는 것이 좋습니다.

작업 전에는 엑셀 중복값 표시하는 방법의 적용 범위와 원본 상태를 먼저 확인하는 것이 좋습니다.

자주 묻는 질문

중복값을 표시하면 데이터가 삭제되나요?

아니요. 조건부 서식은 셀의 색상이나 글꼴 등 표시 형식만 바꾸며, 셀 내용은 삭제하지 않습니다. 데이터가 실제로 삭제되는 기능은 **[데이터] → [중복 제거]**입니다.

중복된 값 중 두 번째 값만 표시할 수 있나요?

기본적인 중복 값 규칙은 같은 값이 여러 번 나타나는 셀을 모두 표시합니다. 첫 번째 항목은 제외하고 이후 항목만 표시하려면 별도의 조건부 서식 수식이 필요하며, 데이터의 시작 행과 비교 범위에 맞춰 수식을 구성해야 합니다.

다른 열의 값이 같을 때만 중복으로 표시할 수 있나요?

가능합니다. 단일 열의 값만 비교할 때는 COUNTIF를 사용하고, 여러 열의 조합을 기준으로 판단할 때는 COUNTIFS 등 추가 조건을 사용하는 방식으로 규칙을 구성합니다. 이때 어떤 열의 조합을 하나의 항목으로 볼지 먼저 정해야 합니다.

빈 셀도 중복으로 색칠되는 이유는 무엇인가요?

COUNTIF가 빈 셀을 여러 번 나타나는 값으로 계산할 수 있기 때문입니다. 다음처럼 빈 셀 제외 조건을 추가하면 됩니다.

=AND(A2<>"",COUNTIF($A$2:$A$400,A2)>1)

중복값을 숨기기만 하려면 어떻게 하나요?

고유값 필터링을 사용하면 중복값을 일시적으로 숨길 수 있습니다. 이는 중복 데이터를 삭제하는 기능이 아니므로, 원본을 보존한 채 목록을 검토할 때 적합합니다.

마무리

빠르게 확인하려면 범위를 선택한 뒤 **[홈] → [조건부 서식] → [셀 강조 규칙] → [중복 값]**을 사용하세요. 빈 셀 제외, 특정 범위 지정, 추가 조건이 필요하면 COUNTIF 수식 규칙을 적용하면 됩니다. 중복을 확인하기 전에는 표시 기능을 먼저 사용하고, 실제 삭제가 필요할 때만 원본을 복사한 뒤 중복 제거를 실행하는 것이 안전합니다.