엑셀에서 중복 값을 찾고 강조 표시하는 방법을 찾고 계신다면 잘 오셨습니다. 스프레드시트에서 어떤 행이 중복되는지 확인한 뒤, 필요하다면 직접 삭제해야 할 때가 종종 있습니다. 한 열 안에서 중복 행을 표시하는 것은 비교적 간단하지만, 목록이 길어지면 수작업으로 일일이 지우는 데 시간이 많이 걸립니다. 반면 데이터 양이 적을 때는 손쉽게 처리할 수 있죠. 이 글에서는 엑셀에서 중복 값을 찾고 강조 표시하는 다양한 방법을 단계별로 자세히 살펴보겠습니다.
엑셀에서 중복 값 찾고 강조 표시하는 3가지 방법
몇 가지 간단한 단계만 거치면 단일 열, 여러 열, 또는 전체 데이터 범위에서 중복 값을 찾아 강조 표시할 수 있습니다. 엑셀은 이러한 작업을 효율적으로 처리할 수 있는 기능들을 기본적으로 제공합니다.
1. 조건부 서식 활용하기
조건부 서식(Conditional Formatting) 기능을 사용하면 중복 행을 손쉽게 강조 표시할 수 있습니다. 아래의 간단한 단계를 따라 해 보세요.
실행 단계:
- 먼저 중복을 확인할 셀 범위를 선택합니다. 여기서는 D5:D18입니다.
- 다음으로 홈(Home) 탭 > 조건부 서식(Conditional Formatting) > 셀 강조 규칙(Highlight Cells Rules) > 중복 값(Duplicate Values)을 차례로 클릭합니다.
- 그러면 중복 값(Duplicate Values) 대화 상자가 나타납니다.
- 목록에서 중복(Duplicate)을 선택합니다.
- ‘다음 서식으로 지정(values with)’ 항목에서 원하는 색상 옵션을 고릅니다. 이 예시에서는 연한 빨간색 채우기와 진한 빨간색 텍스트(Light Red Fill with Dark Red Text)를 선택했습니다.
완료하면 D열의 중복 값들이 아래 화면처럼 강조 표시된 것을 확인할 수 있습니다.
참고: 두 개 이상의 열을 선택해 중복 행을 강조 표시할 수도 있습니다. 다만 이 방법에는 약간의 기술적인 문제가 있으니 주의해야 합니다. 예를 들어, 한 열에 있는 텍스트가 다른 열에도 나타나면 실제로 중복이 아니더라도 엑셀이 해당 텍스트를 중복으로 판단해 강조 표시할 수 있습니다.
팁: 표에 방대한 데이터가 들어 있어서 중복 행을 열 상단으로 몰아서 보고 싶은 경우가 있을 수 있습니다. 엑셀은 색상을 기준으로 정렬하는 기능을 제공합니다. 예시 표에서 ‘Name’ 헤더를 클릭하면 오름차순 정렬(Sort A to Z), 내림차순 정렬(Sort Z to A), 색 기준 정렬(Sort by Color) 옵션이 나타납니다. ‘색 기준 정렬’을 선택한 뒤 ‘셀 색 기준 필터(Filter by Cell Color)’를 적용하면 아래 이미지처럼 결과를 볼 수 있습니다.
함께 읽으면 좋은 글: 수식으로 엑셀에서 중복 값 찾는 방법 (9가지 방법)
2. 첫 번째 항목을 제외한 중복 행 찾기 및 강조 표시
COUNTIF 함수를 사용하면 중복 행을 식별하고 강조 표시할 수 있습니다. 이 함수를 활용하면 첫 번째 항목을 포함하는 경우와 제외하는 경우 모두 처리 가능합니다. 먼저 첫 번째 항목을 제외하고 중복 행을 찾는 방법부터 살펴보겠습니다.
실행 단계:
- 먼저 중복을 찾을 셀 범위를 선택합니다. 여기서는 D5:D18입니다.
- 다음으로 홈(Home) 탭 > 조건부 서식(Conditional Formatting) > 새 규칙(New Rule)을 선택합니다.
- 그러면 새 서식 규칙(New Formatting Rule) 창이 열립니다.
- 수식을 사용하여 서식을 지정할 셀 결정(Use a formula to determine which cells to format)을 클릭합니다.
- 수식 입력란에 아래 수식을 입력합니다.
=COUNTIF($D$5:$D5,$D5)>1- 확인(OK)을 클릭합니다.
또한 서식(Format) 옵션에서 글꼴 크기, 색상 등을 설정해 주어야 합니다.
완료하면 첫 번째 항목을 제외한 중복 값들이 연한 주황색으로 표시됩니다.
함께 읽으면 좋은 글: 엑셀에서 행끼리 비교해 중복 찾는 방법 (3가지 쉬운 방법)
3. 여러 열 범위에서 중복 강조 표시하기
여러 열로 구성된 범위에서도 중복을 매우 쉽게 강조 표시할 수 있습니다. 역시 첫 번째 항목을 포함하는 경우와 제외하는 경우 모두에 적용할 수 있습니다.
3.1 첫 번째 항목을 포함하여 중복 강조 표시
여러 열에서 첫 번째 항목까지 포함해 중복을 강조 표시하려면 COUNTIF 함수를 사용해야 합니다. 설명을 위해 D열과 E열의 중복을 강조 표시해야 하는 아래 데이터 집합으로 작업해 보겠습니다.
실행 단계:
- 먼저 앞서 설명한 것처럼 조건부 서식 메뉴를 통해 새 서식 규칙(New Formatting Rule) 창을 엽니다.
- 수식을 사용하여 서식을 지정할 셀 결정 옵션을 선택합니다.
- 수식 입력란에 아래 수식을 입력합니다.
=COUNTIF($D$5:$E$16,$D5)>1
- 확인(OK)을 클릭합니다.
결과적으로 D열과 E열에서 첫 번째 항목까지 포함한 중복 값들이 모두 강조 표시된 것을 확인할 수 있습니다.
함께 읽으면 좋은 글: Excel VBA로 범위 내 중복 값 찾기 (7가지 예제)
3.2 첫 번째 항목을 제외하고 중복 강조 표시
여러 열뿐만 아니라 전체 데이터 열에서도 첫 번째 항목을 제외한 중복을 강조 표시할 수 있습니다. 두 경우 모두 COUNTIF 함수의 도움이 필요합니다.
⧪ 첫 번째 열에서 중복 강조 표시
여러 열에서 중복을 강조 표시하려면 첫 번째 열에 COUNTIF 함수를 적용해야 합니다.
실행 단계:
- 앞서 설명한 것과 동일하게 조건부 서식 메뉴를 통해 새 서식 규칙 창을 엽니다.
- 마찬가지로 수식을 사용하여 서식을 지정할 셀 결정 옵션을 선택합니다.
- 수식 입력란에 아래 수식을 입력합니다.
=COUNTIF($D$5:$D5,$D5)>1
- 확인(OK)을 클릭합니다.
최종 결과는 아래와 같으며, 첫 번째 항목을 제외한 중복 값들만 강조 표시됩니다.
⧪ 이후 모든 열에서 중복 강조 표시
다음의 간단한 단계를 따르면 그 뒤에 오는 모든 열에서도 중복을 강조 표시할 수 있습니다.
실행 단계:
- 앞서와 마찬가지로 조건부 서식 메뉴를 통해 새 서식 규칙 창을 엽니다.
- 동일하게 수식을 사용하여 서식을 지정할 셀 결정 옵션을 선택합니다.
- 수식 입력란에 아래 수식을 입력합니다.
=IF(COLUMNS($B5:B5)>1,COUNTIF(A$5:$B$16,B5),0)+COUNTIF(B$5:B5,B5)>1
- 확인(OK)을 클릭합니다.
완료하면 데이터베이스의 모든 열에서 중복을 찾아 표시할 수 있습니다.
함께 읽으면 좋은 글: 엑셀에서 두 열의 중복 찾기 (6가지 방법)
관련 추천 자료
- 엑셀에서 두 워크시트의 일치하는 값 찾기 (4가지 방법)
- 엑셀 수식으로 한 열에서 중복 찾기
- 엑셀에서 중복 찾아 다른 시트로 복사하기 (5가지 방법)
- 중복이 있는 엑셀 TOP 10 목록 만들기 (2가지 방법)
- 엑셀에서 두 열의 유사 텍스트 찾기 (3가지 쉬운 방법)
엑셀에서 중복 개수 세는 방법
엑셀 파일에서도 몇 가지 간단한 방법으로 중복의 개수를 셀 수 있습니다. 그중 하나가 COUNTIF 함수를 활용하는 방법입니다. 아래 데이터 집합에서 D열의 머리글은 ‘Item Name(품목명)’이며, 이 열에는 다양한 과일 이름이 들어 있습니다. 이 열의 중복 개수를 세고자 하며, ‘Item(항목)’이라는 이름의 F열 값을 참조해 ‘Count(개수)’라는 이름의 G열에 결과를 구해 보겠습니다.
실행 단계:
- 먼저 G5 셀에 아래 수식을 입력합니다.
=COUNTIF($D$5:$D$16,F5)
- Enter 키를 누르면 결과값 1이 표시됩니다.
- 참조 셀 G5의 우측 하단 모서리를 잡고 커서를 아래로 끌어 채우기 핸들(Fill Handle)을 사용합니다.
- 완료하면 G열에서 아래와 같이 모든 과일의 개수를 확인할 수 있습니다.
엑셀에서 중복 제거하는 방법
엑셀에서도 간단한 방법만 알면 중복을 매우 쉽게 제거할 수 있습니다. 그중 하나가 데이터 도구(Data Tools) 옵션을 사용하는 방법입니다. 여기서는 ‘Business Type(업종)’, ‘Branch Location(지점 위치)’, ‘Item Name(품목명)’ 머리글을 가진 아래 데이터 집합에서 B4:D16 범위의 중복을 제거해 보겠습니다.
실행 단계:
- 먼저 셀 범위를 선택합니다. 여기서는 B4:D16입니다.
- 다음으로 데이터(Data) 탭 > 데이터 도구(Data Tools) > 중복된 항목 제거(Remove Duplicates)를 선택합니다.
- 그러면 중복된 항목 제거(Remove Duplicates) 창이 나타납니다.
- 열(Column) 목록에서 고려할 열 옵션을 선택합니다.
- 확인(OK)을 클릭합니다.
결과적으로 아래와 같이 데이터 집합에서 중복이 제거된 것을 확인할 수 있습니다.
함께 읽으면 좋은 글: 엑셀에서 중복 행 찾기 및 제거하는 방법
꼭 기억해야 할 사항
- 데이터에 병합된 셀이 있다면 중복 관련 기능을 적용하기 전에 반드시 병합을 해제해야 합니다. 그렇지 않으면 엑셀이 중복 값을 올바르게 찾지 못합니다.
- 새 서식 규칙의 서식(Format) 옵션에서 글꼴 유형, 특히 색상을 반드시 지정해야 합니다. 색상을 제대로 설정하지 않으면 엑셀은 중복을 선택해 표시하지만 눈으로 확인할 수 없는 문제가 발생합니다.
결론
이 글의 내용만 잘 숙지하면 엑셀에서 중복 값을 찾고 강조 표시하는 작업을 아주 쉽게 처리할 수 있습니다. 추가 문의 사항이 있다면 공식 엑셀 학습 플랫폼 ExcelDemy를 언제든 방문해 주세요.
관련 글
- Excel VBA로 한 열에서 중복 찾기 (5가지 방법)
- 엑셀에서 일치 값 또는 중복 값 찾기 (8가지 방법)
- VBA 코드로 엑셀에서 중복 행 찾기 (3가지 방법)
- 엑셀에서 열의 중복 찾고 행 삭제하기 (4가지 빠른 방법)
- 엑셀에서 삭제 없이 중복 찾기 (7가지 방법)
- 서로 다른 엑셀 통합 문서에서 중복 찾기 (5가지 방법)
- 엑셀에서 중복 필터링하기 (7가지 쉬운 방법)