엑셀 중복값 찾기와 강조: 조건부 서식, COUNTIF, UNIQUE 함수 활용법

핵심 요약
엑셀 중복값 찾기는 겹친 셀에 색만 입히려면 조건부 서식(홈 > 조건부 서식 > 셀 강조 규칙 > 중복 값), 각 값이 몇 번 나왔는지 세려면 COUNTIF, 중복을 뺀 목록을 뽑으려면 UNIQUE를 씁니다. UNIQUE는 Microsoft 365·엑셀 2021 이상에서만 동작합니다.

 
명단이나 거래 내역을 엑셀로 정리하다 보면 같은 이름, 같은 주문번호가 두 번씩 들어가는 경우가 생깁니다. 행이 수백 줄이면 눈으로 하나씩 대조하기는 어렵습니다. 어디가 겹쳤는지, 몇 번 겹쳤는지부터 알아야 다음 작업으로 넘어갈 수 있습니다.
 
엑셀 중복값 찾기는 목적에 따라 방법이 갈립니다. 겹친 셀에 색을 입혀 눈에 띄게 하려면 조건부 서식, 각 값이 몇 번 나왔는지 세려면 COUNTIF, 중복을 걸러 낸 목록을 따로 뽑으려면 UNIQUE 함수를 씁니다. 세 가지를 차례로 정리했습니다.

 

1. 메뉴 클릭만으로 중복값에 색 입히기 (조건부 서식)

함수를 쓰지 않고 가장 빠르게 중복을 표시하는 방법입니다. 조건부 서식은 조건을 만족하는 셀에 자동으로 서식을 입혀 주는 기능이고, 그중 '중복 값' 규칙은 범위 안에서 두 번 이상 나온 값에 색을 칠해 줍니다.

1. 중복을 확인할 범위를 선택합니다.
2. 홈 탭의 스타일 그룹에서 조건부 서식 화살표를 누르고 셀 강조 규칙을 선택합니다.
3. 중복 값을 선택합니다.
4. 적용할 서식을 고르고 확인을 누릅니다.

 
기본값은 중복된 셀을 최초 항목까지 모두 강조합니다. 반대로 한 번만 나온 값을 보고 싶다면 대화 상자의 드롭다운을 '중복'에서 '고유'로 바꾸면 됩니다.

 

2. 각 값이 몇 번 나왔는지 세기 (COUNTIF)

색으로 보는 것만으로 부족하고 건수가 필요할 때 쓰는 함수입니다. COUNTIF는 범위에서 조건에 맞는 셀의 개수를 세는 함수입니다.

=COUNTIF(범위, 조건)

 
중복 확인에는 '범위 전체'를 대상으로 '그 범위의 셀 하나'를 조건으로 넣습니다. 결과가 2 이상이면 중복입니다. 수식을 아래로 채울 때 범위가 따라 움직이지 않도록 범위는 $로 고정합니다.

=COUNTIF($A$2:$A$7, A2)

A2	김영희	=COUNTIF($A$2:$A$7,A2)	2
A3	이철수	=COUNTIF($A$2:$A$7,A3)	3
A5	박민수	=COUNTIF($A$2:$A$7,A5)	1

 
이 예시 범위에는 김영희가 2번, 이철수가 3번, 박민수가 1번 들어 있습니다. 결과가 1인 박민수만 중복이 아닙니다. 범위에 $를 붙이지 않으면 수식을 복사할 때 기준 범위가 한 칸씩 밀려 엉뚱한 숫자가 나오므로 주의합니다.
 

3. 최초 값은 두고 중복만 골라 강조하기 (COUNTIF + 조건부 서식)

1번의 메뉴 방식은 겹친 셀을 전부 칠합니다. 어느 것이 처음 입력된 값이고 어느 것이 나중에 또 들어온 값인지 구분하려면, 조건부 서식에서 수식 규칙을 직접 넣습니다. 홈 탭 조건부 서식에서 새 규칙, '수식을 사용하여 서식을 지정할 셀 결정'을 고른 뒤 다음 수식을 입력합니다.

=COUNTIF($A$2:$A$7,A2)>1

 
이 수식은 1번 메뉴와 같은 결과로, 겹친 값을 최초 항목까지 모두 강조합니다. 두 번째로 등장한 것부터만 색을 칠하고 싶다면 범위의 끝을 고정하지 않고 열어 둡니다.

=COUNTIF($A$2:A2,A2)>1

 
범위가 행마다 늘어나면서 '지금 셀까지 같은 값이 2개 이상 쌓였는지'를 보기 때문에, 처음 나온 값은 넘어가고 나중에 다시 들어온 값만 강조됩니다.
 

4. 중복을 뺀 목록 따로 뽑기 (UNIQUE)

원본은 그대로 두고 중복이 정리된 깔끔한 목록을 옆에 만들고 싶을 때 쓰는 함수입니다.

=UNIQUE(array, [by_col], [exactly_once])

 
array는 대상 범위입니다. by_col은 비교 방향으로, 생략하거나 FALSE면 행끼리 비교합니다. exactly_once는 TRUE로 두면 딱 한 번만 나온 값만 돌려주고, 생략하면 서로 다른 값을 종류별로 한 번씩 돌려줍니다. 결과는 아래 칸으로 자동으로 펼쳐집니다(스필).

=UNIQUE(A2:A7)	종류별로 한 번씩	김영희, 이철수, 박민수
=UNIQUE(A2:A7,,TRUE)	딱 한 번 나온 값만	박민수

 
UNIQUE는 Microsoft 365, 엑셀 2021과 2024, 웹·모바일 버전에서 동작합니다. 엑셀 2019와 2016 이하에서는 동적 배열을 지원하지 않아 쓸 수 없습니다. 결과가 펼쳐질 아래 칸에 다른 값이 들어 있으면 #SPILL! 오류가 나므로 빈 공간을 비워 둡니다.
 
색으로 빠르게 훑어보려면 조건부 서식, 건수까지 확인하려면 COUNTIF, 중복이 정리된 목록이 필요하면 UNIQUE가 맞습니다. 찾은 중복을 아예 지우는 작업은 데이터 탭의 중복된 항목 제거 기능이 따로 있습니다.
 
먼저 조건부 서식으로 겹친 곳을 눈으로 확인한 다음, 건수나 별도 목록이 필요하면 COUNTIF와 UNIQUE를 이어서 적용해 보세요.
 

참고 자료

• Microsoft Excel에서 COUNTIF 함수 사용
• UNIQUE function - Microsoft Support
• Excel에서 조건부 서식을 사용하여 정보 강조 표시

 
※ 더 보기
AI에 넣을 데이터 정리법: 엑셀 중복 제거와 TRIM 함수, 지나친 정리가 역효과인 이유
엑셀 빈칸 없애기 함수: 효율적인 데이터 정리 방법