엑셀을 사용하여 방대한 데이터를 관리하다 보면 가장 흔하게 마주치는 문제가 바로 중복된 데이터입니다. 고객 명단을 정리하거나 재고 현황을 파악할 때 동일한 값이 여러 번 입력되어 있으면 데이터의 신뢰도가 떨어지고 통계 결과에 오류를 발생시킬 수 있습니다. 특히 2025년 현재 업무 환경에서는 데이터 분석의 중요성이 더욱 커지고 있어 깔끔한 데이터 전처리는 필수적인 역량으로 자리 잡았습니다. 엑셀은 이러한 문제를 해결하기 위해 직관적인 중복 제거 기능부터 조건부 서식, 그리고 함수를 활용한 고급 필터링 방법까지 다양한 도구를 제공합니다.
📚 함께 읽으면 좋은 글
많은 분들이 단순히 ‘중복된 항목 제거’ 버튼만 알고 계시지만, 실무에서는 중복된 값을 눈으로 먼저 확인하거나 특정 조건에 맞는 중복값만 골라내야 하는 경우가 많습니다. 이번 글에서는 가장 기초적인 제거 방법부터 함수를 이용한 응용 방법, 그리고 실수하기 쉬운 데이터 정리 팁까지 상세하게 다뤄보겠습니다. 엑셀 버전이 업데이트되면서 UI가 조금씩 세련되어졌지만 핵심 기능의 원리는 동일하므로 모든 버전 사용자에게 유용한 정보가 될 것입니다.
엑셀 중복된 항목 제거 기능 사용법 확인하기
가장 빠르고 확실하게 중복 데이터를 삭제하는 방법은 엑셀의 데이터 탭에 있는 기본 기능을 활용하는 것입니다. 이 기능은 원본 데이터를 영구적으로 삭제하므로 작업 전에 반드시 원본 데이터를 복사해 두거나 백업 파일을 만들어두는 것이 좋습니다. 사용 방법은 매우 간단합니다. 먼저 중복 검사를 실행할 데이터 범위를 마우스로 드래그하여 선택합니다. 만약 표 전체를 대상으로 한다면 표 내부의 셀 하나만 클릭해도 엑셀이 자동으로 범위를 인식합니다.
상단 메뉴의 [데이터] 탭으로 이동한 후 [데이터 도구] 그룹에 있는 [중복된 항목 제거] 아이콘을 클릭합니다. 팝업 창이 뜨면 열 목록이 나타나는데, 여기서 중복을 판단할 기준이 되는 열을 체크합니다. 모든 열을 체크하면 행 전체의 내용이 토씨 하나 틀리지 않고 똑같은 경우에만 중복으로 간주하여 삭제합니다. 반면 특정 열(예: 이메일 주소)만 체크하면 나머지 정보가 다르더라도 이메일이 같으면 중복으로 처리되어 삭제됩니다.
확인 버튼을 누르면 엑셀이 자동으로 중복된 값을 찾아 제거하고 몇 개의 중복 값이 제거되었으며 몇 개의 고유 값이 남았는지 메시지 창으로 알려줍니다. 이 과정은 되돌리기(Ctrl+Z)가 가능하므로 결과가 예상과 다르다면 즉시 실행을 취소하고 조건을 다시 설정할 수 있습니다.
조건부 서식으로 중복 데이터 색깔 표시 상세 보기
데이터를 무작정 삭제하기보다는 어떤 데이터가 중복되었는지 눈으로 먼저 확인하고 싶을 때가 있습니다. 이때는 [조건부 서식] 기능을 활용하면 매우 직관적입니다. 데이터를 삭제하지 않고 셀의 색상이나 글자 색만 변경하여 사용자에게 시각적인 정보를 제공하기 때문입니다. 우선 중복 여부를 확인하고 싶은 데이터 범위를 지정합니다.
홈 탭의 스타일 그룹에서 [조건부 서식]을 클릭하고 [셀 강조 규칙] 메뉴 하단에 있는 [중복 값]을 선택합니다. 대화 상자가 나타나면 기본 설정인 ‘진한 빨강 텍스트가 있는 연한 빨강 채우기’가 적용되어 중복된 데이터들이 붉게 표시되는 것을 볼 수 있습니다. 서식은 사용자가 원하는 대로 노란색이나 녹색 등으로 변경할 수 있으며, 반대로 ‘고유’를 선택하여 중복되지 않은 데이터만 강조할 수도 있습니다.
이 기능은 데이터 입력 단계에서 실수를 방지하는 데에도 탁월합니다. 데이터가 입력되는 열 전체에 미리 이 조건부 서식을 걸어두면, 작업자가 이미 존재하는 값을 입력하는 순간 셀 색상이 변하여 즉시 중복임을 인지할 수 있게 됩니다. 눈으로 확인한 후에는 필터 기능을 이용해 색상이 있는 셀만 정렬하여 따로 관리하거나 삭제 여부를 결정하면 됩니다.
COUNTIF 함수 활용하여 중복값 개수 세기 배우기
단순히 중복 여부만 아는 것을 넘어, 해당 데이터가 몇 번이나 반복되는지 카운트해야 할 때는 함수를 사용해야 합니다. 이때 가장 많이 사용되는 함수가 바로 COUNTIF입니다. 이 함수는 지정한 범위 내에서 특정 조건(값)이 몇 번 등장하는지를 숫자로 반환해 줍니다. 수식의 기본 구조는 =COUNTIF(범위, 기준값)입니다.
예를 들어 A열에 데이터가 있고 B열에 중복 횟수를 표시하고 싶다면, B2 셀에 =COUNTIF(A:A, A2)라고 입력합니다. 이렇게 하면 A열 전체에서 A2 셀의 값과 똑같은 데이터가 몇 개인지 계산됩니다. 결과값이 1이라면 유일한 데이터라는 뜻이고, 2 이상이라면 중복된 데이터가 있다는 의미입니다. 데이터 양이 많을 때는 이 수식을 아래로 자동 채우기 하여 모든 행의 중복 횟수를 파악할 수 있습니다.
더 나아가 IF 함수와 결합하여 “중복”, “고유”와 같은 텍스트로 표시할 수도 있습니다. =IF(COUNTIF(A:A, A2)>1, "중복", "")와 같이 입력하면 2번 이상 등장하는 데이터 옆에만 ‘중복’이라는 글자가 표시됩니다. 함수를 활용하는 방법은 원본 데이터를 손상시키지 않으면서 별도의 열에 분석 결과를 남길 수 있어 보고서 작성 시 매우 유용합니다.
데이터 정리 시 자주 발생하는 오류 해결하기
중복 제거 기능을 실행했는데도 눈에 보기에 똑같은 데이터가 삭제되지 않고 남아있는 경우가 종종 있습니다. 이는 엑셀이 컴퓨터의 관점에서 데이터를 엄격하게 비교하기 때문입니다. 가장 흔한 원인은 눈에 보이지 않는 ‘공백(Space)’입니다. 예를 들어 “홍길동”과 “홍길동 “(뒤에 공백 포함)은 사람 눈에는 같아 보이지만 엑셀은 서로 다른 문자로 인식합니다. 웹사이트에서 데이터를 복사해 붙여넣기 했을 때 이런 현상이 자주 발생합니다.
이럴 때는 TRIM 함수를 사용하여 불필요한 공백을 먼저 제거해야 합니다. 다른 열에 =TRIM(원본셀)을 입력하여 공백을 정리한 깨끗한 데이터를 만든 후, 이를 값 복사하여 원본 자리에 덮어쓰기 하고 중복 제거를 진행하면 해결됩니다. 또한 숫자처럼 보이지만 텍스트 형식으로 저장된 숫자와 실제 숫자가 섞여 있을 때도 중복 인식이 안 될 수 있으므로 데이터 형식을 통일하는 과정이 필요합니다.
고유 값을 추출할 때 대소문자 구분이 필요한 경우라면 기본 기능보다는 파워 쿼리나 고급 필터 기능을 활용하는 것이 정확도를 높이는 방법입니다. 2025년 최신 엑셀 버전에서는 이러한 데이터 클렌징 기능이 더욱 강화되어 데이터 탭에서 ‘데이터 가져오기 및 변환’ 기능을 통해 더욱 정교한 전처리가 가능해졌습니다.
FAQ
Q1. 중복된 값을 제거한 후에 다시 복구할 수 있나요?
중복 제거 기능은 실행 즉시 데이터를 영구 삭제합니다. 만약 파일을 저장하지 않았다면 Ctrl + Z를 눌러 실행 취소를 할 수 있지만, 파일을 저장하고 닫아버렸다면 복구가 불가능합니다. 따라서 작업 전에 반드시 시트를 복사하거나 별도 파일로 백업해두는 습관이 필요합니다.
Q2. 두 개의 열을 기준으로 중복을 체크하려면 어떻게 하나요?
‘중복된 항목 제거’ 기능을 실행할 때 나타나는 열 목록에서 기준이 될 두 개의 열을 모두 체크하면 됩니다. 예를 들어 ‘이름’과 ‘전화번호’를 모두 체크하면, 이름과 전화번호가 둘 다 똑같은 행만 중복으로 간주하여 삭제합니다. 하나라도 다르면 고유한 값으로 남겨둡니다.
Q3. 중복된 데이터 중 첫 번째 값이 아닌 마지막 값을 남길 수는 없나요?
엑셀의 기본 기능은 무조건 위에서부터 아래로 검색하여 처음 나오는 값을 남기고 그 뒤에 나오는 값들을 삭제합니다. 만약 마지막 값을 남기고 싶다면, 데이터에 번호를 매기거나 날짜순으로 정렬을 반대로(내림차순) 한 뒤 중복 제거를 실행하면 됩니다. 정렬 순서를 바꾸는 것만으로 남기는 데이터를 조절할 수 있습니다.
Q4. 많은 양의 데이터에서 중복 제거 속도가 너무 느립니다. 해결 방법이 있나요?
수만 행 이상의 대용량 데이터를 처리할 때는 엑셀 기본 시트 기능보다 ‘파워 쿼리’를 사용하는 것이 훨씬 빠르고 효율적입니다. 데이터 탭의 ‘테이블/범위에서 가져오기’를 통해 파워 쿼리 편집기를 열고, 거기서 ‘중복 제거’를 실행하면 메모리 관리 효율이 좋아져 훨씬 쾌적하게 작업할 수 있습니다.