엑셀 중복값 찾기·제거·표시 방법, 조건부 서식부터 중복 제거 버튼, UNIQUE 함수까지

엑셀 중복값은 홈 > 조건부 서식 > 셀 강조 규칙 > 중복 값으로 먼저 색을 칠해 확인하고, 데이터 > 중복 제거로 지운다. 중복 제거는 첫 번째 항목만 남기고 나머지를 영구 삭제하니 복사본을 두고 하자.

명단이나 주문 목록을 여러 파일에서 붙이다 보면 같은 줄이 두세 번 들어가 있을 때가 있다. 엑셀에는 이걸 처리하는 기능이 조건부 서식, 중복 제거, 고급 필터, UNIQUE 함수까지 넷이나 되는데, 어떤 건 색만 칠하고 어떤 건 행을 지워 버려서 순서를 잘못 잡으면 되돌리기 어렵다. 그래서 Microsoft 지원 문서 ‘중복 값 찾아 제거하기’, ‘고유 값 필터링 또는 중복 값 제거’, ‘UNIQUE 함수’를 읽고 어떤 기능을 언제 쓰는지 정리했다. 앞의 두 문서의 적용 대상은 Microsoft 365용 Excel과 Excel 2024·2021·2019·2016이고(UNIQUE 문서는 뒤에서 따로 적는다), 모두 2026년 10월에 열어 본 내용 기준이다.

엑셀 중복값 처리는 기능마다 원본에 하는 일이 다르다

아래 표에서 내가 하려는 일에 맞는 줄만 찾아 읽으면 된다. 맨 오른쪽 칸이 중요하다. 같은 ‘중복 처리’라도 원본 데이터가 그대로 남는 기능이 있고 사라지는 기능이 있다.

하려는 일	기능	메뉴	원본 데이터
중복 값에 색칠해서 보기	조건부 서식	홈 > 조건부 서식 > 셀 강조 규칙 > 중복 값	값은 그대로 두고 서식만 입힌다
중복 행 지우기	중복 제거	데이터 > 데이터 도구 > 중복 제거	중복 값이 영구적으로 삭제된다
중복 뺀 목록 만들기	고급 필터	데이터 > 정렬 & 필터 > 고급 > 고유 레코드만	현재 위치에서는 중복 행을 일시적으로 숨기고, 다른 위치에 복사하면 고유 값만 그 위치로 복사된다
중복 뺀 목록을 수식으로 만들기	UNIQUE 함수	=UNIQUE(범위)	수식 결과가 옆 셀로 펼쳐진다

Microsoft 문서도 같은 순서를 권한다. 중복을 제거하기 전에 고유한 값을 필터링하거나 조건부 서식을 지정해서 예상한 결과가 맞는지 먼저 확인하라는 것이다. 이 글도 색칠하기, 지우기, 목록만 뽑기 순으로 간다.

728x90

엑셀 중복값 표시: 조건부 서식으로 색칠하기

1. 중복을 확인할 셀 범위를 선택한다.
2. 홈 탭에서 조건부 서식 > 셀 강조 규칙 > 중복 값을 선택한다.
3. ‘값’ 다음 상자에서 중복 값에 적용할 서식을 고르고 확인을 누른다.

범위 안에서 겹치는 값이 든 셀이 고른 서식으로 강조된다. 색칠은 서식만 바꾸고 값을 건드리지 않으니 지우기 전에 눈으로 확인하는 용도로 쓰기 좋다.

반대로 한 번만 나온 값을 칠하고 싶으면 고급 서식 쪽을 쓴다. 문서에 있는 순서는 이렇다. 홈 탭에서 조건부 서식 화살표를 눌러 규칙 관리를 열고 새 규칙을 고른다. 규칙 유형에서 ‘고유 또는 중복 값만 서식 지정’을 고른 뒤, 규칙 설명의 ‘모두 서식’ 목록에서 고유 또는 중복을 고르고 서식을 정하면 된다.

한 가지 막히는 곳이 있다. 피벗 테이블 보고서의 값 영역에 있는 항목은 고유 값이나 중복 값을 기준으로 조건부 서식을 지정할 수 없다고 문서에 적혀 있다. 피벗에서 중복이 보여 칠하고 싶다면 피벗이 아니라 원본 데이터 범위에서 칠하는 쪽으로 생각해야 한다.

엑셀 중복값 제거: 중복 제거 버튼 쓰는 순서

이 기능은 색칠과 달리 데이터를 지운다. 문서는 영구적으로 삭제된다고 적었고, 실행 전에 원래 셀 범위나 표를 다른 워크시트 또는 통합 문서에 복사해 두라고 권한다. 복사본부터 만들고 시작하자.

1. 셀 범위를 선택하거나 표 안의 셀 하나를 선택한다.
2. 데이터 탭의 데이터 도구 그룹에서 중복 제거를 선택한다.
3. ‘열’ 아래에서 중복을 가를 열에 체크한다. 모두 선택, 모두 선택 취소 단추로 한꺼번에 켜고 끌 수 있다.
4. 확인을 누르면 제거된 중복 값 수나 남은 고유 값 수를 알려 주는 메시지가 뜬다. 이 메시지는 확인으로 닫는다.
5. 결과가 이상하면 실행 취소를 누르거나 Ctrl+Z를 누른다.

지워지는 범위는 선택한 셀 범위나 표 안으로 한정되고, 범위 밖의 값은 바뀌거나 이동하지 않는다. 같은 값이 여러 개면 목록에서 첫 번째 항목은 남고 나머지가 지워진다.

여기서 가장 헷갈리는 게 열 체크다. 문서는 열을 일부만 체크해도 지울 때는 행 전체가 지워진다고 적었다. Column1과 Column2만 체크하고 Column3을 빼면 Column1과 Column2의 값을 합친 것이 중복을 가르는 ‘키’가 되고, 이 키가 같은 행은 Column3에 무슨 값이 있든 통째로 사라진다. 아래 목록으로 따져 보자. 데이터는 설명하려고 내가 만든 예이고, 지워지는 행은 문서의 규칙을 적용해서 구한 것이다.

목록 순서	이름	지점	금액
1	김민수	서울	12,000
2	이서연	부산	8,000
3	김민수	서울	15,000
4	박지후	서울	8,000
5	이서연	부산	8,000
6	김민수	대구	12,000

체크한 열	지워지는 순서	남는 순서	남는 행 수
이름만	3, 5, 6	1, 2, 4	3개
이름 + 지점	3, 5	1, 2, 4, 6	4개
이름 + 지점 + 금액	5	1, 2, 3, 4, 6	5개

이름만 체크하면 김민수 세 줄 중 처음 것만 남아서 15,000원 줄과 대구 지점 줄이 같이 사라진다. 이름과 지점을 체크하면 대구의 김민수는 살아남고, 세 열을 다 체크하면 이서연 부산 8,000원이 두 번 나온 5번만 지워진다. 어디까지 같아야 같은 항목인지부터 정하고 열을 체크해야 한다.

남는 행을 고르는 문제도 있다. 문서는 첫 번째 항목을 남긴다고만 적었다. 그러니 최신 기록을 남기고 싶다면 실행 전에 날짜 같은 기준으로 정렬해서 남기고 싶은 행이 위로 오게 해 두면 된다.

엑셀 중복값 찾기가 안 맞을 때 확인할 것

먼저 문서가 말하는 ‘중복’의 뜻을 보자. 한 행의 모든 값이 다른 행의 모든 값과 같을 때 중복이고, 비교는 셀에 저장된 값이 아니라 셀에 나타나는 내용으로 한다. 문서가 든 예가 날짜다. 같은 날짜인데 한 셀은 “3/8/2006”, 다른 셀은 “Mar 8, 2006”으로 표시돼 있으면 두 값은 고유한 값으로 취급된다. 아래 표는 문서에 적힌 내용을 증상별로 다시 묶은 것이다.

증상	문서에 적힌 이유	해 볼 것
눈에는 같은 날짜인데 중복으로 안 잡힌다	비교 기준이 저장된 값이 아니라 화면에 나타나는 내용이라, 표시 형식이 다르면 다른 값이다	표시 형식을 하나로 맞춘 뒤 다시 한다
중복 제거가 되지 않는다	개요가 표시됐거나 부분합이 있는 데이터에서는 중복 값을 제거할 수 없다	윤곽선과 부분합을 모두 제거한 뒤 다시 한다
피벗 테이블 값 영역은 색칠이 안 된다	피벗 테이블 보고서의 값 영역은 고유·중복 기준 조건부 서식을 지정할 수 없다	원본 데이터 범위에서 칠한다
체크하지 않은 열의 데이터도 같이 사라졌다	체크한 열의 조합이 같으면 다른 열까지 포함해 행 전체가 제거된다	Ctrl+Z로 되돌리고 체크할 열을 다시 정한다
확인 메시지의 숫자가 예상과 다르다	메시지에 나오는 중복·고유 값 수에 빈 셀, 공백 등이 포함될 수 있다	색칠로 실제 중복 셀을 눈으로 확인한다

문서에는 수식으로 기준을 직접 잡는 방법도 하나 나온다. Office 작성 언어 및 언어 교정 설정이 일본어처럼 더블바이트(DBCS) 문자를 쓰는 언어로 돼 있으면 ‘중복 값’ 규칙이 반자와 전자를 같은 글자로 취급하는데, 이 둘을 구분해야 하면 수식을 쓰는 규칙 유형에 COUNTIF를 넣으라는 안내다. A2:A400 범위라면 =COUNTIF($A$2:$A$400,A2)>1이다. COUNTIF는 범위에서 조건에 맞는 셀이 몇 개인지 세는 함수라서, A2와 같은 값이 범위에 두 개 이상이면 참이 되어 칠해진다. 그 설정이 아니라면 일반 ‘중복 값’ 규칙으로 충분하고, 기준을 수식으로 직접 정하고 싶을 때 쓰는 기본형 정도로 알아 두면 된다.

728x90

엑셀 중복값 추출: 지우지 않고 중복 뺀 목록 만들기

원본은 그대로 두고 중복이 빠진 목록만 필요하다면 지우는 것보다 뽑아내는 쪽이 안전하다. 방법은 둘이다.

고급 필터로 뽑기

1. 셀 범위를 선택하거나 표 안의 셀 하나를 선택한다. 열 머리글이 있는지 확인해 둔다.
2. 데이터 탭의 정렬 & 필터 그룹에서 고급을 선택한다.
3. 고급 필터 창에서 ‘목록 필터링’을 고르면 현재 위치에서 걸러지고, ‘다른 위치에 복사’를 고르면 ‘복사 위치’ 상자에 셀 참조를 적는다.
4. ‘고유 레코드만’에 체크하고 확인을 누른다.

문서에 따르면 현재 위치 필터링은 중복 값을 일시적으로 숨길 뿐이고, 다른 위치에 복사하면 범위의 고유 값이 지정한 곳으로 복사된다. 고유 값이 몇 개인지만 알고 싶다면 복사한 범위 아래 빈 셀에 ROWS 함수를 쓴다. 고유 값 범위가 B2:B45라면 =ROWS(B2:B45)이고, 열 머리글은 범위에서 뺀다.

UNIQUE 함수로 뽑기

수식은 =UNIQUE(array,[by_col],[exactly_once])이다. 인수는 세 개다.

인수	필수 여부	뜻
array	필수	고유한 행 또는 열을 뽑을 범위나 배열
by_col	선택	TRUE면 열끼리 비교하고, FALSE나 생략이면 행끼리 비교한다
exactly_once	선택	TRUE면 범위에서 정확히 한 번 나오는 행·열만 돌려주고, FALSE나 생략이면 고유한 행·열을 모두 돌려준다

UNIQUE는 배열을 돌려주고, 이 배열이 수식의 최종 결과이면 인접한 셀로 펼쳐진다. Enter를 누르면 엑셀이 알맞은 크기의 범위를 만든다. 목록을 가나다순으로 정렬하려면 문서 예시처럼 SORT로 감싼다. 이름이 두 열에 나뉘어 있을 때 =SORT(UNIQUE(B2:B12&" "&A2:A12))처럼 &로 이어 붙인 전체 이름을 UNIQUE에 넣는 식이다.

exactly_once를 TRUE로 바꾸면 결과가 달라진다. 문서 예시는 서비스를 한 번만 받은 고객을 뽑아 다시 연락하려는 경우다. 앞의 예시 목록에서 이름 열만 범위로 잡았다고 하면 김민수가 세 번 나왔으니 기본값에서는 한 번 보이고, TRUE에서는 아예 빠진다. 이름·지점·금액 세 열을 함께 범위로 잡으면 UNIQUE가 행 전체를 비교하므로 이야기가 달라진다. 중복을 ‘뺀’ 목록과 중복된 적 없는 것만 ‘남긴’ 목록은 다르다.

버전은 확인해야 한다. 문서의 UNIQUE 적용 대상은 Microsoft 365용 Excel, Excel 2024, Excel 2021이고(Mac·웹·모바일판 포함) 2019와 2016은 목록에 없다. 2019 이하라면 위의 고급 필터를 쓰는 쪽이다. 또 다른 통합 문서를 참조하는 동적 배열은 두 통합 문서가 모두 열려 있을 때만 지원되고, 원본 통합 문서를 닫으면 새로 고칠 때 #REF! 오류가 난다고 한다.

실수 없이 하는 순서 다섯 단계

1. 원본 범위를 다른 워크시트에 복사해 둔다.
2. 조건부 서식으로 중복을 칠하고, 칠해진 셀이 예상과 맞는지 눈으로 본다.
3. 몇 열이 같아야 같은 항목인지 정하고, 남기고 싶은 행이 위로 오게 정렬한다.
4. 데이터 탭의 중복 제거에서 열을 체크해 실행하고, 메시지에 나온 숫자를 확인한다.
5. 예상과 다르면 바로 Ctrl+Z로 되돌리고 3번부터 다시 한다.

화면에 보이는 메뉴 이름은 문서의 한국어 표기를 따랐다. 엑셀 버전이나 창 크기에 따라 단추 위치가 조금 다를 수 있으니, 이름이 안 보이면 같은 탭에서 비슷한 이름의 단추를 찾아보자. 어느 방법을 쓰든 확인하고 나서 지운다는 순서만 지키면 크게 틀어지지 않는다.