엑셀(Excel)에서 중복 데이터 찾고 제거하는 9가지 방법

엑셀로 작업하다 보면 중복된 값 때문에 데이터 관리가 번거로워지는 경우가 많습니다. 다행히 엑셀에는 중복 값을 찾고 제거할 수 있는 다양한 기능이 내장되어 있습니다. 중복을 삭제하기 전에는 반드시 시트의 백업 사본을 만들어 두는 것이 좋습니다. 이 글에서는 엑셀에서 중복 값을 찾고, 개수를 확인하고, 제거하는 9가지 방법을 하나씩 살펴보겠습니다.

1. 중복된 항목 제거 버튼 활용하기

엑셀에는 중복 값을 한 번에 지울 수 있는 기본 기능인 '중복된 항목 제거' 버튼이 마련되어 있습니다.

중복을 제거할 열을 선택합니다.
상단의 '데이터' 탭으로 이동한 뒤 '중복된 항목 제거' 버튼을 클릭합니다.

중복 경고 창이 나타나면 선택 범위를 조절할 수 있습니다.
현재 선택한 데이터 범위에서만 중복을 제거하려면 '현재 선택 영역 유지' 옵션을 선택합니다.
더 많은 열을 포함하려면 '선택 확장'을 선택합니다.
어느 쪽이든 '중복된 항목 제거' 버튼을 누르면 됩니다.

중복이 포함된 열의 체크박스는 체크 상태로 두고, 그대로 유지할 열의 체크는 해제합니다.
'데이터에 머리글 포함' 항목에 체크되어 있는지 확인하세요. 이렇게 하면 각 중복 값 중 첫 번째 데이터는 남기고 이후에 등장하는 중복 항목만 제거됩니다.

확인 버튼을 클릭하면 엑셀이 모든 중복 값을 제거하고, 발견된 중복 값과 고유 값의 개수를 요약해 알려줍니다.

2. 조건부 서식으로 중복 데이터 찾기

이 방법은 조건부 서식을 활용해 중복 값을 실제로 삭제하지 않고 화면에 표시만 하는 방식입니다.

엑셀 시트에서 중복이 포함된 데이터를 선택합니다. 한 열 전체일 수도 있고 표 전체일 수도 있습니다.
'홈' 탭으로 이동해 '조건부 서식'을 클릭합니다.

'셀 강조 규칙'을 선택한 뒤 '중복 값'을 클릭합니다.

'중복 값' 팝업 창이 열리면 중복 값에 적용할 강조 색상을 변경할 수 있습니다.
반대로 고유 값만 확인하고 싶다면 '중복' 드롭다운 메뉴에서 '유니코드' 대신 '고유'를 선택합니다.
확인 버튼을 누르면 설정이 적용됩니다.

3. 조건부 필터로 중복 제거하기

중복 값이 색으로 강조된 상태라면 필터 기능을 사용해 해당 항목을 제거하거나, 앞서 소개한 방법으로 삭제할 수 있습니다.

필터 기능을 활성화하려면 '홈 탭 → 정렬 및 필터 → 필터' 순서로 이동합니다.

데이터의 열 머리글 옆에 드롭다운 화살표가 생깁니다. 중복을 필터링할 열의 드롭다운을 클릭합니다.
메뉴에서 '색 기준 필터'를 선택합니다. 시트에 남길 고유 값의 색상을 선택하면 나머지 중복 항목은 화면에서 숨겨집니다.

이제 시트에는 고유 값만 표시됩니다. 주의할 점은 이 방법이 첫 번째 중복 항목까지 모두 걸러낸다는 것입니다.

고유 값만 복사하려면 데이터 범위를 선택한 후 Alt + ; 단축키로 보이는 행만 선택하고, Ctrl + C로 복사한 뒤 원하는 위치에 붙여넣으면 됩니다.

4. 수식으로 중복 값 찾기

열 안의 중복은 엑셀 수식으로도 찾을 수 있습니다. 가장 기본적인 중복 감지 수식은 =COUNTIF(Range, Criteria) >1이며, 범위는 열 전체 또는 특정 행 범위로 지정할 수 있습니다.

새 열을 하나 만들고 아래 명령 중 하나를 입력합니다.

열 전체를 검사하려면 =COUNTIF(D:D, D2) >1을 사용합니다. 여기서 D는 열 이름, D2는 맨 위 셀입니다.
특정 행 범위만 검사하려면 =COUNTIF($D$2:$D$10, $D2) >1을 사용합니다. 첫 번째 인수가 선택한 행 범위이며, D2는 맨 위 셀입니다. 범위 앞에 반드시 달러($) 기호를 붙여야 합니다. 그렇지 않으면 수식을 아래로 드래그할 때 셀 참조가 함께 변경됩니다.

채우기 핸들을 드래그해 나머지 행에도 수식을 적용합니다. 중복 값에는 'TRUE', 고유 값에는 'FALSE'가 표시됩니다.

TRUE/FALSE 대신 다른 문구를 표시하고 싶다면 COUNTIF 수식을 IF 함수로 감싸면 됩니다. 예를 들어 =IF(COUNTIF($D$2:$D$10, $D2) > 1, "중복", "단일")처럼 작성할 수 있습니다.

고유 값인 경우 빈 셀로 표시되게 하려면 =IF(COUNTIF($D$2:$D$10, $D2) > 1, "중복", "") 수식을 사용합니다.

5. 수식으로 중복 개수 세기

앞선 수식에서 > 1 부분을 빼면 열 안의 중복 개수를 셀 수 있습니다. 두 수식은 각각 =COUNTIF($D$2:$D$10, $D2)와 =COUNTIF(D:D, D2)가 됩니다. 새 열에 입력하면 각 항목이 데이터에 몇 번 등장하는지 표시됩니다.

6. 수식 결과를 활용해 중복 값 제거하기

중복 값 또는 중복 개수를 파악했다면, 필터 기능으로 중복을 걸러내고 고유 값만 남길 수 있습니다.

'홈 탭 → 정렬 및 필터 → 필터'로 이동해 열 머리글에 필터 드롭다운을 활성화합니다.
드롭다운을 클릭하고 유지할 값의 체크박스만 남깁니다. 중복 찾기 방법을 사용했다면 'FALSE'(고유 값), 중복 개수 방법을 사용했다면 '1'을 선택하면 됩니다. 이렇게 하면 중복 값은 숨겨지고 고유 값만 표시됩니다.
Alt + ; 단축키로 보이는 행만 선택합니다.

7. 고급 필터로 중복 데이터 제거하기

'데이터' 탭으로 이동해 '고급'을 클릭합니다.

'고급 필터' 팝업 창이 열립니다.
같은 데이터 범위에서 중복을 숨기려면 '제자리에 목록 필터링' 옵션을 선택합니다. 이후 고유 값만 직접 복사해 같은 시트의 다른 위치나 다른 시트로 옮길 수 있습니다.
이 옵션을 선택한 상태에서 열 목록을 선택하면 '목록 범위' 필드에 자동으로 입력됩니다. '조건 범위'는 비워 둡니다.
'동일한 레코드는 하나만' 항목에 체크한 뒤 확인을 누릅니다.