엑셀 중복 값 제거를 하면 어떤 행이 남나요?

Microsoft 도움말에 따르면 목록에서 처음 나온 값이 남고, 그 뒤에 나오는 같은 값이 삭제됩니다. 특정 기록(예: 가장 최근 날짜)을 남기고 싶다면 중복 제거 전에 원하는 순서로 정렬해 두시면 됩니다.

엑셀 중복값 찾기만 하고 지우지 않으려면 어떻게 하나요?

[홈] 탭 [조건부 서식] → [셀 강조 규칙] → [중복 값]을 선택하면 중복된 셀에 색이 칠해지고 데이터는 그대로 남습니다. 등장 횟수까지 보고 싶다면 옆 열에 COUNTIF 함수를 넣어 2 이상인 행을 확인하면 됩니다.

중복 제거에서 한 열만 체크하면 그 열만 지워지나요?

아닙니다. 도움말은 모든 열을 선택하지 않더라도 데이터가 모든 열에서 제거된다고 안내합니다. 체크한 열의 값이 앞선 행과 같으면 처음 나온 행만 남고 나머지 행이 통째로 삭제되니, 원본을 다른 시트에 복사해 두고 실행하시길 권합니다.

엑셀 2019에서도 UNIQUE 함수를 쓸 수 있나요?

Microsoft Excel 함수 목록은 UNIQUE를 Excel 2021에서 도입된 함수로 표시하고, 이런 함수는 이전 버전에서 쓸 수 없다고 설명합니다. Excel 2016·2019에서는 [데이터] 탭의 [고급] 필터에서 「고유 레코드만」을 체크해 고유 값 목록을 만들 수 있습니다.

엑셀 중복 값 제거와 찾기 | 중복 제거·조건부 서식·UNIQUE 함수

엑셀 중복 값 제거는 [데이터] 탭의 [중복 제거] 버튼 하나로 끝나지만, 지운 데이터는 영구히 사라지기 때문에 순서가 중요합니다. 조건부 서식이나 COUNTIF 함수로 중복값을 먼저 찾아 눈으로 확인하고, 원본을 복사해 둔 뒤 중복 제거를 실행하는 것이 Microsoft 도움말이 권하는 흐름인데요. 원본을 건드리지 않고 고유 값만 따로 뽑고 싶다면 고급 필터나 UNIQUE 함수를 쓰면 됩니다.

광고 리포트나 고객 명단을 여러 파일에서 합치다 보면 같은 행이 두 번, 세 번 들어가 있는 경우가 생기죠. "이 목록에서 중복만 싹 지우면 되는데, 어떤 게 지워지고 어떤 게 남는 걸까?" 하고 망설여 본 적 있으신가요? 이번 글에서는 Microsoft 공식 도움말을 기준으로 중복값을 찾는 법, 지우는 법, 고유 값만 뽑는 법을 차례로 정리해 보겠습니다.

엑셀에서 말하는 「중복」은 정확히 무엇일까요?

도움말은 한 행의 모든 값이 다른 행의 모든 값과 같을 때 중복 값이라고 설명합니다. 그래서 중복 제거 창에서 어떤 열을 비교 대상으로 고르느냐에 따라 결과가 크게 달라지는데요, 이 부분은 뒤에서 다시 짚겠습니다.

또 하나 헷갈리기 쉬운 점은 비교 기준이 셀에 저장된 값이 아니라 셀에 보이는 내용이라는 것입니다. 도움말 예시로는 같은 날짜라도 한 셀은 「3/8/2006」, 다른 셀은 「Mar 8, 2006」 형식으로 표시되어 있으면 서로 다른 고유 값으로 본다고 하는데요. 날짜나 숫자 서식이 섞인 데이터라면 중복 제거 전에 서식부터 맞춰 두는 편이 안전합니다.

비유하자면 명함첩 정리와 비슷합니다. 같은 사람의 명함이 여러 장 꽂혀 있을 때, 잠깐 서랍에 넣어 두고 한 장씩만 보는 것이 고유 값 필터링이고, 나머지를 아예 버리는 것이 중복 값 제거인데요. 도움말도 고유 값을 필터링하면 중복 값이 일시적으로만 숨겨지지만, 중복 값을 제거하면 영구히 삭제된다고 구분합니다. 그렇다면 버리기 전에 어떤 명함이 겹치는지 먼저 확인하는 방법부터 볼까요?

1. 지우기 전에 중복값을 먼저 찾아 표시하려면?

가장 빠른 방법은 조건부 서식입니다. 엑셀 중복 항목 찾기 및 제거 도움말의 순서는 이렇습니다.

1단계: 중복 여부를 확인할 셀을 선택합니다.
2단계: [홈] 탭 [스타일] 그룹에서 [조건부 서식] → [셀 강조 규칙] → [중복 값]을 선택합니다.
3단계: 서식(색)을 고르고 [확인]을 누르면 중복값이 색으로 표시됩니다.

반대로 한 번만 나오는 값을 칠하고 싶다면 [조건부 서식] → [규칙 관리] → [새 규칙]에서 규칙 유형을 「고유 또는 중복 값만 서식 지정」(Format only unique or duplicate values)으로 고르고, [모두 서식] 목록에서 「고유」나 「중복」을 선택하면 됩니다. 다만 도움말은 피벗 테이블 보고서의 값 영역에서는 중복 항목을 강조 표시할 수 없다고 적고 있으니, 피벗 결과가 아니라 원본 데이터에 서식을 거시길 권합니다.

몇 번 겹치는지 숫자로 보고 싶을 때는 COUNTIF 함수를 옆 열에 넣는 방법도 있습니다. COUNTIF 함수는 범위 안에서 조건에 맞는 셀의 개수를 세는 함수라서, A열 이메일 목록이라면 B2에 =COUNTIF($A$2:$A$100,A2)를 넣고 아래로 채우면 값마다 등장 횟수가 나오는데요. 2 이상인 행이 중복이고, 필터로 그 행만 골라 보면 어떤 데이터가 겹쳤는지 한눈에 확인할 수 있습니다. COUNTIF는 텍스트의 대소문자를 구분하지 않고, 255자를 넘는 문자열을 비교하면 잘못된 결과를 돌려준다는 점도 함께 기억해 두시면 좋습니다.

2. 엑셀 중복 값 제거는 어떤 순서로 하나요?

중복을 확인했다면 이제 지울 차례입니다. 도움말이 먼저 강조하는 것은 원본 데이터를 다른 워크시트로 복사해 두라는 점인데요, 중복 제거는 되돌릴 수 없는 삭제이기 때문입니다.

1단계: 셀 범위를 선택하거나, 표 안의 셀 하나를 선택해 둡니다.
2단계: [데이터] 탭 [데이터 도구] 그룹에서 [중복 제거]를 선택합니다.
3단계: [열] 목록에서 비교할 열을 고릅니다. [모두 선택]이나 [모두 선택 취소]로 한꺼번에 바꿀 수도 있습니다.
4단계: [확인]을 누르면 중복 값이 몇 개 제거됐는지, 고유 값이 몇 개 남았는지 알려 주는 메시지가 나옵니다.

실무에서 가장 조심해야 할 곳은 3단계입니다. 도움말은 모든 열을 선택하지 않더라도 데이터는 모든 열에서 제거된다고 적고 있는데요, 예를 들어 「이메일」 열만 체크하면 이메일이 같은 행이 통째로 지워지고 다른 열의 값이 달라도 남지 않는다는 뜻입니다. 이때 남는 쪽은 목록에서 처음 나온 값이고 그 뒤의 같은 값이 삭제된다고 도움말이 설명하니, 최신 기록을 남기고 싶다면 날짜 기준으로 먼저 정렬해 두는 것이 좋습니다.

잘못 지웠다면 바로 [실행 취소]를 누르거나 Ctrl+Z로 되돌릴 수 있습니다. 또 윤곽(개요)이 설정되어 있거나 부분합이 들어간 데이터에서는 중복 값을 제거할 수 없다고 하니, 그런 데이터라면 윤곽과 부분합을 먼저 해제해야 하는데요. 제거 뒤 메시지에 나오는 중복·고유 값 개수에는 빈 셀이나 공백도 포함될 수 있다는 점도 도움말에 적혀 있습니다. 그런데 원본은 그대로 두고 고유 값 목록만 따로 만들고 싶을 때는 어떻게 할까요?

3. 원본은 두고 고유 값만 뽑으려면 어떻게 하나요?

첫 번째 방법은 고급 필터입니다. [데이터] 탭 [정렬 & 필터] 그룹에서 [고급]을 누르고, 고급 필터 대화 상자에서 「다른 위치에 복사(Copy to another location)」를 고른 뒤 [복사 위치] 상자에 셀을 지정하고 「고유 레코드만(Unique records only)」을 체크하면 원본 옆이나 다른 곳에 고유 값 목록이 만들어집니다. 「목록 필터링(Filter the list, in-place)」을 고르면 중복 행이 잠시 숨겨질 뿐이라, 중복 제거 전에 결과를 미리 확인하는 용도로도 쓸 수 있는데요. 도움말도 중복 값을 제거하기 전에 고유 값으로 필터링하거나 조건부 서식을 적용해 원하는 결과가 나오는지 먼저 확인하라고 권합니다.

두 번째 방법은 UNIQUE 함수입니다. 형식은 =UNIQUE(array,[by_col],[exactly_once])이고, 인수는 이렇게 나뉩니다.

인수	뜻	생략하면
array	고유한 행이나 열을 돌려받을 범위	필수
by_col	TRUE면 열끼리, FALSE면 행끼리 비교	FALSE(행끼리 비교)
exactly_once	TRUE면 정확히 한 번만 나온 값만 반환	FALSE(모든 고유 값 반환)

예를 들어 =UNIQUE(A2:A100)을 한 셀에 넣으면 결과가 아래 셀로 자동으로 퍼져 나오는데, 이를 도움말은 「분산」이라고 부릅니다. 원본 데이터를 엑셀 표로 만들어 두고 구조적 참조로 지정하면 행을 추가하거나 지울 때 결과 범위도 자동으로 바뀌기 때문에, 매주 갱신되는 리포트에서 편리한데요. exactly_once를 TRUE로 두면 한 번만 등장한 값만 남으니, 「한 번만 구매한 고객」처럼 딱 한 번 나온 항목을 뽑을 때 쓸 수 있습니다.

※ 참고 사항: 중복인데 안 지워진다면 무엇을 봐야 할까요?

눈으로는 똑같은데 중복으로 잡히지 않는다면 보이지 않는 차이가 있을 가능성이 큽니다.

서식 차이: 앞서 본 것처럼 비교는 셀에 보이는 내용 기준이라, 날짜 표시 형식이 다르면 다른 값으로 봅니다.
앞뒤 공백: COUNTIF 도움말도 앞뒤 공백이나 곧은 따옴표·둥근 따옴표를 섞어 쓰면 텍스트 비교가 어긋날 수 있다고 주의를 줍니다. TRIM 함수는 단어 사이의 공백 하나만 남기고 나머지 공백을 지우는데요, 7비트 ASCII 공백(값 32)만 지우도록 만들어져 웹 페이지에서 자주 쓰이는 줄 바꿈하지 않는 공백(값 160)은 TRIM만으로는 지워지지 않는다고 합니다.
엑셀 버전: UNIQUE 함수는 Excel 함수 목록에 Excel 2021에서 도입된 함수로 표시되어 있어 Excel 2016·2019에서는 쓸 수 없습니다. 이 버전이라면 중복 제거 기능이나 고급 필터를 쓰시면 되는데요, 두 기능은 도움말 적용 대상에 Excel 2016부터 Microsoft 365까지 올라 있습니다.

다른 곳에서 내려받은 CSV에서 한글이 깨져 있다면 같은 이름도 다른 값으로 읽힐 수 있으니, 이럴 때는 엑셀 한글 깨짐 해결 글을 먼저 참고해 주세요.

그렇다면 어떤 방법을 고르면 좋을까요?

도움말 내용을 바탕으로 제 기준을 정리하면 이렇습니다.

어디가 겹치는지 먼저 보고 싶을 때: 조건부 서식의 [중복 값], 몇 번 겹치는지까지 보려면 COUNTIF
데이터를 깔끔하게 정리해 넘겨야 할 때: 원본을 복사해 둔 뒤 [중복 제거]
원본을 남기고 목록만 필요할 때: 고급 필터의 「고유 레코드만」, Microsoft 365나 Excel 2021 이후라면 UNIQUE 함수

정리하면 엑셀 중복 값 제거는 찾기, 백업, 제거 순서로 하시는 것이 가장 안전하고, 중복 제거 창에서 어떤 열을 체크하는지가 결과를 좌우합니다. 중복을 걷어낸 데이터는 VLOOKUP·XLOOKUP으로 다른 표와 붙이거나 피벗테이블로 요약할 때 숫자가 부풀지 않는데요. 저도 리포트 데이터를 합칠 때 이 순서를 지키면서 다시 정리해 보려고 하니, 세부 옵션은 공식 도움말을 참고해 주세요.

자주 묻는 질문

엑셀 중복 값 제거를 하면 어떤 행이 남나요?: Microsoft 도움말에 따르면 목록에서 처음 나온 값이 남고, 그 뒤에 나오는 같은 값이 삭제됩니다. 특정 기록(예: 가장 최근 날짜)을 남기고 싶다면 중복 제거 전에 원하는 순서로 정렬해 두시면 됩니다.
엑셀 중복값 찾기만 하고 지우지 않으려면 어떻게 하나요?: [홈] 탭 [조건부 서식] → [셀 강조 규칙] → [중복 값]을 선택하면 중복된 셀에 색이 칠해지고 데이터는 그대로 남습니다. 등장 횟수까지 보고 싶다면 옆 열에 COUNTIF 함수를 넣어 2 이상인 행을 확인하면 됩니다.
중복 제거에서 한 열만 체크하면 그 열만 지워지나요?: 아닙니다. 도움말은 모든 열을 선택하지 않더라도 데이터가 모든 열에서 제거된다고 안내합니다. 체크한 열의 값이 앞선 행과 같으면 처음 나온 행만 남고 나머지 행이 통째로 삭제되니, 원본을 다른 시트에 복사해 두고 실행하시길 권합니다.
엑셀 2019에서도 UNIQUE 함수를 쓸 수 있나요?: Microsoft Excel 함수 목록은 UNIQUE를 Excel 2021에서 도입된 함수로 표시하고, 이런 함수는 이전 버전에서 쓸 수 없다고 설명합니다. Excel 2016·2019에서는 [데이터] 탭의 [고급] 필터에서 「고유 레코드만」을 체크해 고유 값 목록을 만들 수 있습니다.