오늘 포스팅에서는 엑셀 중복값 찾기 방법, 엑셀 중복값을 제거하고 추출하거나 강조하는 방법에 대해서 정리해보았습니다.
참고로 아래 글은 아래 테스트 날짜와 기기, 앱 버전에서 작동 여부를 직접 확인 완료 하였습니다.
테스트 날짜 : 26년 1월 31일
윈도우 버전 : Windows 11 Home / 25H2
사용기기 : Microsoft Surface Pro 11th Edition
프로그램명/버전 : Microsoft 365 / Excel / 버전 2510
마이크로소프트에서 만든 여러가지 유틸리티 중에 엑셀은 윈도우 운영체제에서 대표적인 킬러 애플리케이션이 아닐까 싶습니다. 엑셀은 스프레드시트 프로그램으로 왠만한 자료 분석이나 통계처리가 가능하며 이정도의 가성비가 나오는 프로그램은 없다고 생각됩니다.
게다가 간단한 프로그래밍이 가능한 비주얼베이직 기반의 사용자 프로그래밍 환경(VBA)까지 지원하여 실무에 필수적으로 사용되는 경우가 많아 몇몇 직군에서는 필수적인 툴로 사용되고 있습니다.
그렇기 때문인지 기능이 워낙 다양하고 많아서 기본적인 기능이라고 잊어버리기 마련인데요,
이번 포스팅에서는 엑셀 중복값 찾기 방법, 엑셀 중복값에 대해서 필터 설정이나 조건부 서식 등을 통해 제거하거나 추출 또는 강조하는 방법에 대해서 아래와 같이 정리해보았습니다.
엑셀 중복값 찾기 (1) 고급 필터 사용하기
엑셀 중복값 찾기의 가장 기본적인 첫 번째 방법은 바로 데이터 탭에 있는 고급 필터를 사용하는 방법입니다.
엑셀의 고급 필터 기능은 생각보다 매우 강력하고 유용합니다. 엑셀에서 데이터를 다룰 때는 수십 개 내지는 수천, 수만 개의 데이터를 정리하는 일이 생기곤 하는데요. 이때 중복된 항목을 걸러내고 고유한 값만 남기고 싶을 때 엑셀 중복값 찾기 기능 중 고급 필터가 가장 적합합니다.
우선 이해를 돕기 위해 아래와 같이 매우 간단한 예제 데이터를 준비했습니다. 노란색으로 표시한 행이 우리가 찾아내야 할 중복값입니다. itmanual.net이라는 주소를 가진 정보가 방문자 수와 속성 값까지 모두 동일한 엑셀 중복값 상황입니다.
눈으로 셀 수 있을 정도의 데이터라면 수동으로 지우면 그만이겠지만, 실무 데이터는 그렇지 않기 때문에 다음 단계를 따라 해 보시기 바랍니다.
1. 데이터 탭 > 필터 항목 내의 “고급” 선택
상단의 메뉴 항목 중에서 데이터 탭을 선택한 다음 필터를 누르고 고급 항목을 눌러줍니다.
2. 고급 필터 > 목록범위 및 복사위치 입력 > “동일한 레코드는 하나로” 활성화 > 확인
고급 필터 대화 상자가 나타나면 여기서 중요한 설정을 해야 합니다. 원본 데이터를 훼손하지 않고 중복값이 제거된 깔끔한 리스트를 따로 뽑아내기 위함입니다.
결과: 현재 위치에 필터를 하게 되면 원본 데이터가 숨겨지거나 변경될 수 있으므로, 다른 장소에 복사를 선택하는 것을 추천합니다. 이것이 저의 실무 노하우 중 하나입니다. 원본은 그대로 두고 중복이 제거된 클린 데이터만 옆에 따로 추출하는 것이 데이터 안정성 측면에서 좋습니다.
목록 범위: 중복값을 찾아서 정리할 원본 데이터 전체(표 전체)를 드래그하여 지정해 줍니다.
복사 위치: 중복이 제거된 데이터가 출력될 새로운 셀의 시작 위치를 클릭해 줍니다.
동일한 레코드는 하나만: 이 체크박스가 핵심입니다. 엑셀 중복값 찾기 후 중복된 것은 버리고 고유한 값 하나만 남기겠다는 의미입니다. 반드시 체크해 줍니다.
모든 설정이 완료되었다면 하단의 확인 버튼을 눌러줍니다.
위와 같이 실행하면, 설정한 복사 위치에 중복된 itmanual.net 데이터 중 하나가 제거되고, 고유한 값들만 남은 표가 새롭게 생성되는 것을 확인할 수 있습니다. 이 방법을 사용하면 원본 데이터를 유지하면서도 중복이 제거된 목록을 30초 안에 손쉽게 확보할 수 있습니다.
엑셀 중복값 찾기 (2) 조건부 서식 사용하기
엑셀 중복값 찾기의 두 번째 방법은 바로 조건부 서식을 사용하는 방법입니다. 앞선 고급 필터 방법이 중복을 제거하고 추출하는 데 목적이 있다면, 조건부 서식은 데이터를 삭제하기 전에 어떤 데이터가 중복되었는지 눈으로 확인하고 강조하는 데 목적이 있습니다.
실무에서는 무턱대고 데이터를 지우기보다, 눈으로 확인해야 하는 경우가 많으므로 이 방법이 매우 유용하게 쓰입니다.
1. 홈 > 조건부 서식 > 셀 강조 규칙 > “중복 값” 선택
먼저 중복 검사를 하고 싶은 데이터 범위를 마우스로 드래그하여 선택합니다. 그다음 상단의 홈 탭을 선택하고, 스타일 그룹에 있는 조건부 서식을 클릭합니다. 나타나는 메뉴에서 셀 강조 규칙에 마우스를 올리고, 옆으로 나오는 하위 메뉴에서 중복 값을 선택합니다.
참고로 조건부 서식이란 사용자가 지정한 특정 조건(여기서는 중복)을 만족하는 셀에만 색상을 입히거나 글꼴을 바꾸는 등 서식을 변경할 때 활용하는 기능입니다.
2. 중복값 > 조건(중복) 지정 > 적용할 서식 선택
중복 값 설정 팝업창이 나타나면, 왼쪽 드롭다운 메뉴에서는 중복을 선택하고, 오른쪽 드롭다운 메뉴에서는 적용할 서식을 선택합니다. 기본값으로는 진한 빨강 텍스트가 있는 연한 빨강 채우기가 설정되어 있습니다. 눈에 가장 잘 띄기 때문에 보통 기본값을 그대로 사용합니다. 설정을 마쳤다면 확인을 누릅니다.
예를 들어 아래 이미지의 경우에는 중복값이 나타날 경우 “진한 빨강 텍스트가 있는 연한 빨강 채우기”를 선택했습니다.
그러면 아래 이미지와 같이 엑셀 중복값에 해당하는 셀들이 자동으로 빨간색으로 변한 것을 확인할 수 있습니다.
위와 같은 방법으로 엑셀 중복값 찾기 = 빨간색 글씨, 연한 빨간색 셀을 손쉽게 찾을 수 있습니다.
엑셀 중복값 관리 개인 노하우
여기서 저만의 팁을 하나 더 드리자면, 조건부 서식으로 중복값을 빨간색으로 표시한 뒤, 데이터 탭의 필터 기능을 사용하여 색 기준 정렬을 할 수 있습니다. 필터 화살표를 누르고 색 기준 정렬 > 셀 색 > 연한 빨강을 선택하면, 중복된 데이터들만 맨 위로 모아서 볼 수 있습니다. 이렇게 하면 중복 데이터를 일일이 찾으러 스크롤을 내릴 필요 없이 한곳에 모아두고 삭제할지, 수정할지 결정하기가 매우 수월해집니다.
이번 포스팅에서는 위와 같이 엑셀 중복값 찾기에 대해서 고급 필터 설정을 통한 추출 방법과 조건부 서식을 통한 시각적 강조 방법에 대해서 정리해보았습니다.
엑셀 중복값 찾기 관련 자주 묻는 질문 (FAQ)
Q1. 조건부 서식으로 표시된 색상을 다시 없애려면 어떻게 해야 하나요?
A1. 범위 지정 후 홈 > 조건부 서식 > 규칙 지우기 > 선택한 셀의 규칙 지우기를 선택하면 강조 표시가 사라집니다.
Q2. 두 개의 열을 동시에 비교해서 중복을 찾을 수 있나요?
A2. 네, 가능합니다. 고급 필터를 사용할 때 목록 범위를 두 열 모두 포함시키면, 두 열의 값이 모두 동일한 경우에만 중복으로 인식하여 처리합니다.
Q3. 중복값을 제거했는데 원본 데이터가 날아갔습니다. 복구할 수 있나요?
A3. 엑셀의 실행 취소 단축키인 Ctrl + Z를 누르면 복구 가능합니다. 하지만 문서를 저장하고 닫은 뒤에는 복구가 어려우므로, 중복 제거 작업 전에는 반드시 다른 이름으로 저장하거나 시트를 복사해두는 습관을 들이는 것이 좋습니다.
Q4. 대소문자를 구분해서 중복을 찾고 싶습니다.
A4. 기본 기능인 중복된 항목 제거나 조건부 서식은 대소문자를 구분하지 않는 경우가 많습니다. 대소문자를 엄격하게 구분하려면 EXACT 함수를 활용한 별도의 수식을 사용해야 합니다.
Q5. 엑셀 중복값 찾기 기능이 비활성화되어 있습니다.
A5. 해당 시트가 보호되어 있거나, 통합 문서 공유 상태일 경우 기능이 제한될 수 있습니다. 검토 탭에서 시트 보호 해제를 하거나 통합 문서 공유를 해제한 후 시도해 보세요.
참고로 마이크로소프트 지원 페이지 여기 링크에서 엑셀의 조건부 서식에 대해 간단하게 안내하고 있습니다.
핵심 요약
기능: 엑셀 중복값 찾기 및 제거, 강조.
방법 1 (추출/제거): 데이터 > 고급 > 동일한 레코드는 하나만 체크. (원본 보존 가능)
방법 2 (강조/확인): 홈 > 조건부 서식 > 셀 강조 규칙 > 중복 값. (시각적 확인 용이)
활용 팁: 조건부 서식 적용 후 색 기준 정렬을 하면 중복 데이터만 모아서 관리하기 편함. 주의사항: 대량의 데이터 삭제 전에는 반드시 백업을 권장함.
이번 포스팅에서는 위와 같이 엑셀 중복값에 대해서 필터 설정이나 조건부 서식 등을 통해 제거하거나 추출 또는 강조하는 방법에 대해서 아래와 같이 정리해보았습니다.
앞선 포스팅에서 정리한 엑셀 관련 사용법 및 노하우 관련 글은 아래와 같습니다.
[Excel] 엑셀 조건부 서식 만들기 삭제 방법 1분 마스터하기
[Excel] 엑셀 동일값 찾기 및 중복값 삭제 방법 1분 끝내기 (엑셀 조건부 서식 활용 꿀팁)
작성자 :Sean Woo
[전공] HCI(인간-컴퓨터 상호작용)·UX(사용자 경험 연구)
[업무] 신기술 기반 사업 전략·기술 기획·디지털 트랜스포메이션 업무
ITmanual.net에서는 실생활, 업무를 진행하면서 느꼈던,
스마트폰(아이폰·갤럭시)·운영체제·각종 앱/프로그램·IT기기 활용 노하우를 실생활 중심 IT 매뉴얼로 정리합니다.
덕분에 문제 해결했습니다! 감사합니다.
방문해주셔서 감사합니다~^^