엑셀 중복값 찾기와 제거: 조건부 서식, 중복된 항목 제거, COUNTIF, UNIQUE 함수
TUREFF• •0
명단을 합치거나 여러 사람이 입력한 자료를 모으면 같은 이름, 같은 주문번호가 두 번 세 번 들어가 있기 마련입니다. 중복을 그냥 지우기 전에 먼저 눈으로 확인하고, 필요하면 표시만 하거나, 원본을 남긴 채 고유한 목록만 뽑는 방법까지 상황별로 정리했습니다.
1. 조건부 서식으로 중복값 색칠하기
가장 먼저 해 볼 방법입니다. 데이터를 지우지 않고 어떤 값이 겹치는지 눈으로 확인할 수 있습니다.
- 중복을 확인할 열(예: B2:B500)을 선택합니다.
- 홈 탭 → 조건부 서식 → 셀 강조 규칙 → 중복 값을 누릅니다.
- 표시 형식(연한 빨강 채우기 등)을 고르고 확인을 누릅니다.
같은 창에서 '중복' 대신 '고유'를 고르면 한 번만 나온 값이 칠해집니다. 주의할 점은 이 기능이 한 셀 단위로만 비교한다는 것입니다. 이름은 같지만 생년월일이 다른 동명이인도 중복으로 칠해지니, 여러 열을 함께 봐야 하는 경우에는 아래 COUNTIFS 방법을 쓰세요.
2. 중복된 항목 제거로 한 번에 지우기
- 표 안의 아무 셀이나 클릭합니다.
- 데이터 탭 → 데이터 도구 → 중복된 항목 제거를 누릅니다.
- 비교할 열에 체크합니다. 이름과 전화번호를 모두 체크하면 두 값이 모두 같은 행만 중복으로 봅니다.
- 확인을 누르면 몇 개가 지워지고 몇 개가 남았는지 알려 줍니다.
- 위에 있는 행이 남고 아래 행이 지워집니다. 최신 자료를 남기고 싶다면 먼저 날짜 기준 내림차순으로 정렬한 뒤 실행하세요.
- 되돌리기(Ctrl+Z)는 되지만 저장하고 닫으면 끝입니다. 원본 시트를 복사해 두고 작업하는 습관을 들이세요.
- 앞뒤 공백이 다르면 다른 값으로 봅니다. 'kim'과 'kim '은 중복으로 잡히지 않으니 TRIM 함수로 공백부터 정리하면 정확해집니다.
3. COUNTIF로 중복 표시하기
지우지 않고 '몇 번째 등장인지' 표시해 두면 필터로 골라내기 쉽습니다.
- 전체에서 몇 번 나왔는지: =COUNTIF($B$2:$B$500,B2)
- 위에서부터 몇 번째로 나온 것인지: =COUNTIF($B$2:B2,B2)
두 번째 수식은 범위의 시작만 고정하고 끝은 상대참조로 둔 것이 핵심입니다. 아래로 채우면 범위가 한 줄씩 늘어나서, 처음 나온 값은 1, 두 번째로 나온 값은 2가 됩니다. 결과가 2 이상인 행만 필터로 골라 지우면 첫 번째 값만 남습니다.
글자로 표시하고 싶으면 IF와 묶습니다.
=IF(COUNTIF($B$2:B2,B2)>1,"중복","")
이름과 생년월일처럼 두 열이 모두 같아야 중복인 경우는 COUNTIFS를 씁니다.
=COUNTIFS($B$2:B2,B2,$C$2:C2,C2)
4. UNIQUE 함수로 고유 목록 뽑기
원본은 그대로 두고 옆에 중복 없는 목록만 만들고 싶을 때 가장 편한 방법입니다.
=UNIQUE(B2:B500)
빈 셀 하나에 입력하면 결과가 아래로 자동으로 펼쳐집니다(동적 배열). 원본이 바뀌면 목록도 따라 바뀝니다.
- 여러 열 기준: =UNIQUE(B2:C500) 은 두 열 조합이 같은 행을 하나로 합칩니다.
- 정렬까지: =SORT(UNIQUE(B2:B500))
- 한 번만 나온 값만: =UNIQUE(B2:B500,FALSE,TRUE) 처럼 세 번째 인수를 TRUE로 주면 딱 한 번 등장한 값만 남습니다.
- 결과가 펼쳐질 자리에 다른 값이 있으면 #SPILL! 오류가 납니다. 아래쪽 셀을 비워 주세요.
UNIQUE는 Microsoft 365, Excel 2021, Excel 2024, 웹용 Excel에서 쓸 수 있고, Excel 2019 이하에는 없습니다. 예전 버전에서는 #NAME? 오류가 나므로, 회사에 구버전 사용자가 있다면 결과 범위를 복사해 값으로 붙여 넣은 뒤 공유하거나 '중복된 항목 제거'를 쓰는 편이 안전합니다.
상황별 추천
- 중복이 있는지 먼저 보고 싶다 → 조건부 서식
- 깔끔하게 지우고 끝내고 싶다 → 원본 복사 후 중복된 항목 제거
- 지우기 전에 어떤 행인지 남겨 두고 싶다 → COUNTIF로 표시 후 필터
- 원본은 두고 고유 목록이 필요하다 → UNIQUE
중복 제거를 했는데 개수가 이상하게 맞지 않거나, 공백·대소문자 때문에 안 잡히는 값이 있었다면 상황을 댓글로 알려 주세요. 다른 분들이 쓰는 요령도 함께 나눠 주시면 좋겠습니다.