엑셀 중복값 찾기·제거·개수 세기 완벽 정리

명단·주문내역에서 중복 데이터를 처리하는 방법은 목적에 따라 세 가지로 나뉩니다. '중복인지 표시만 하고 싶다', '완전히 삭제하고 싶다', '중복 없는 목록만 별도로 뽑고 싶다'는 서로 다른 방법을 씁니다. 목적에 맞지 않는 방법을 쓰면 원본이 손상되거나 원하는 결과가 안 나옵니다.

세 가지 방법 비교

1) 중복인지 표시만 하기 — COUNTIF

A열 명단에서 중복된 이름 옆에 '중복'이라고 표시하려면 B열에 다음 수식을 넣습니다.

=IF(COUNTIF($A$2:$A2, A2)>1, "중복", "")

COUNTIF의 첫 범위를 $A$2:$A2처럼 시작 위치만 절대참조로 고정하면, 아래로 내려갈수록 범위가 A2:A2, A2:A3, A2:A4로 넓어져 '처음 등장한 것은 그대로 두고 두 번째부터 나오는 것만 중복 표시'할 수 있습니다. 단순히 $A$2:$A$100처럼 전체를 고정하면 첫 등장 행까지 전부 중복으로 표시되니 목적에 맞게 골라야 합니다.

색으로 바로 확인하려면 — 조건부 서식

1. 1중복 여부를 확인할 범위(A2:A100)를 선택합니다
2. 2리본 메뉴 → [홈] 탭 → [스타일] 그룹 → [조건부 서식] → [셀 강조 규칙] → [중복 값] 클릭
3. 3드롭다운에서 '중복' 또는 '고유' 중 강조할 대상을 선택합니다
4. 4적용할 색을 선택하고 확인을 누르면 바로 색이 칠해집니다

2) 완전히 삭제하기 — 데이터 → 중복된 항목 제거

1. 1중복을 제거할 범위(표 전체)를 선택합니다. 단축키는 Alt → A → M 순서로 누르면 메뉴 없이도 바로 실행됩니다
2. 2리본 메뉴 → [데이터] 탭 → [데이터 도구] 그룹 → [중복된 항목 제거] 클릭
3. 3'내 데이터에 머리글 표시'에 체크가 되어 있는지 확인
4. 4중복 판단 기준으로 쓸 열에 체크(이름만 볼지, 이름+연락처를 함께 볼지 선택 가능) 후 확인
5. 5몇 개의 중복 값이 제거되고 몇 개의 고유 값이 남았는지 알림창으로 안내됩니다

3) 원본은 그대로 두고 고유 목록만 뽑기 — UNIQUE

최신 엑셀(365)이나 구글시트라면 UNIQUE 함수로 원본을 건드리지 않고 중복 없는 목록만 다른 위치에 자동으로 뽑을 수 있습니다.

=UNIQUE(A2:A100)

고유 항목이 몇 개인지 개수만 알고 싶다면 COUNTA와 조합합니다.

=COUNTA(UNIQUE(A2:A100))

구버전 엑셀에서는 UNIQUE가 없으므로 배열 수식으로 대체합니다.

=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))

여러 열을 동시에 기준으로 중복 판단하기

이름은 같지만 전화번호가 다른 사람을 서로 다른 사람으로 처리하고 싶다면, 이름 하나만이 아니라 '이름+전화번호' 조합이 같을 때만 중복으로 봐야 합니다. COUNTIF를 COUNTIFS로 바꾸면 됩니다.

=IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)>1, "중복", "")

'중복된 항목 제거' 메뉴를 쓸 때도 2단계 창에서 이름 열과 전화번호 열을 둘 다 체크하면, 두 값이 모두 같은 행만 중복으로 판단해 삭제합니다.

공백·표기 차이 때문에 중복이 안 잡힐 때

'홍길동'과 '홍길동 '(끝에 공백 하나)는 사람 눈에는 같아 보여도 COUNTIF·중복 제거 모두 서로 다른 값으로 인식해 중복으로 잡지 못합니다. 대소문자는 무시하지만 공백은 무시하지 않는 것이 엑셀의 기본 동작입니다.

구글시트에서의 차이점

구글시트도 COUNTIF·UNIQUE 함수는 동일하게 지원하지만, 메뉴 경로는 [데이터] → [데이터 정리] → [중복 항목 삭제]로 이름이 다릅니다. 기능은 엑셀의 '중복된 항목 제거'와 거의 같으며, 되돌릴 수 없다는 점도 동일하므로 백업 원칙은 그대로 적용됩니다.