엑셀에서 중복된 값을 찾는 가장 빠른 방법은 조건부 서식을 활용하는 겁니다. 데이터 범위만 선택한 뒤 '조건부 서식 > 셀 강조 규칙 > 중복 값'을 클릭하면 3초 만에 모든 중복 데이터가 색깔로 표시됩니다. 이 방법은 별도 함수를 몰라도 바로 적용할 수 있어 실무에서 가장 많이 쓰입니다. 다만, 특정 조건에 맞는 중복만 찾거나 여러 시트를 비교해야 한다면 COUNTIF나 VLOOKUP 같은 함수가 필요합니다.
중복값 찾기, 조건부 서식 하나면 충분할까
조건부 서식은 중복된 셀을 시각적으로 강조할 때 가장 직관적인 방법입니다. 단, 단순히 같은 값이 있는지만 알려줄 뿐, "이 값이 몇 번째 중복인지"나 "다른 시트에도 같은 값이 있는지"는 확인할 수 없습니다.
- 장점: 클릭 세 번이면 끝, 함수 몰라도 사용 가능
- 한계: 대소문자 구분 불가, 조건부 중복 필터링 불가
- 추천 상황: 단일 시트 내 빠른 중복 확인용
예를 들어 고객 명단에서 동명이인을 찾을 때는 조건부 서식이 최고지만, "서울 지역 고객 중에서만 중복을 찾아라" 같은 조건이 붙으면 함수를 써야 합니다.
COUNTIF 함수로 중복 개수와 위치를 한 번에
COUNTIF 함수는 지정한 범위에서 특정 값이 몇 번 등장하는지 세어줍니다. 이 값을 활용하면 중복 여부뿐 아니라 중복 횟수도 파악할 수 있습니다.
=COUNTIF(범위, 조건)
예를 들어 A열에 이름이 있다면 B1 셀에 =COUNTIF(A:A, A1)을 입력하고 아래로 복사합니다. 결과가 2 이상이면 중복, 1이면 유일한 값입니다. 이 방식의 장점은 중복 횟수까지 숫자로 확인할 수 있다는 점입니다.
- 응용: COUNTIF 결과가 2 이상인 행만 필터링하면 중복 목록만 따로 볼 수 있음
- 주의: 대소문자를 구분하지 않음. EXACT 함수와 결합하면 대소문자까지 구별 가능
VLOOKUP으로 다른 시트 간 중복 데이터 비교하기
서로 다른 두 시트에서 동일한 값이 있는지 확인할 때는 VLOOKUP이 유용합니다. 기준이 되는 시트의 값을 다른 시트에서 찾아 있으면 해당 값을, 없으면 오류를 반환합니다.
=VLOOKUP(찾을값, 비교할범위, 열번호, 0)
예를 들어 시트1의 A1 값이 시트2의 A열에 있는지 확인하려면 =VLOOKUP(A1, Sheet2!A:A, 1, 0)을 입력합니다. 값이 있으면 그 값을, 없으면 #N/A 오류가 표시됩니다. 이렇게 하면 오류가 난 셀만 골라서 누락된 데이터를 쉽게 찾을 수 있습니다.
- 팁: IFERROR 함수로 감싸면
=IFERROR(VLOOKUP(...), "없음")처럼 깔끔하게 표시 가능 - 주의: VLOOKUP은 항상 첫 번째 일치 값만 반환. 중복이 여러 개면 첫 번째 값만 찾음
여러 조건을 동시에 만족하는 중복 찾기 (COUNTIFS)
회사에서 "같은 부서에서 같은 이름을 가진 사람"을 찾으려면 조건이 두 개 이상 필요합니다. 이럴 때는 COUNTIFS 함수를 사용합니다.
=COUNTIFS(조건범위1, 조건1, 조건범위2, 조건2)
예를 들어 A열이 부서명, B열이 이름일 때 =COUNTIFS(A:A, A1, B:B, B1)을 입력하면 두 조건을 모두 만족하는 행의 개수를 반환합니다. 결과가 2 이상이면 같은 부서에 동명이인이 있다는 뜻입니다.
- 실무 예시: "서울 + 영업부" 조건으로 중복 확인 시
=COUNTIFS(A:A, "서울", B:B, "영업부", C:C, C1) - 한계: 조건이 많아질수록 수식이 길어짐. FILTER 함수(Office 365 이상)로 대체 가능
자주 묻는 질문
Q. 조건부 서식으로 찾은 중복값만 따로 추출할 수 있나요?
네, 가능합니다. 조건부 서식을 적용한 후 필터 기능을 사용하거나, UNIQUE 함수(Office 365)로 중복이 제거된 목록을 만든 뒤 COUNTIF로 원래 데이터와 비교하면 됩니다. FILTER 함수를 써도 중복값만 추출할 수 있습니다.
Q. 엑셀에서 대소문자를 구분해서 중복을 찾고 싶어요
EXACT 함수를 사용하면 됩니다. =SUMPRODUCT(--EXACT(범위, 비교할셀))을 입력하면 대소문자를 정확히 구분해서 중복 여부를 확인할 수 있습니다. 조건부 서식에서도 '수식을 사용하여 서식 지정'에 이 수식을 넣으면 적용됩니다.
Q. 100만 건 이상의 데이터에서 중복 찾기가 너무 느려요
데이터 양이 많을 때는 피벗 테이블을 추천합니다. 피벗 테이블을 만든 후 행 레이블에 중복을 확인할 필드를 넣고, 값 필드에 개수를 추가하면 중복 횟수가 한눈에 보입니다. 조건부 서식이나 COUNTIF는 데이터가 많을수록 속도가 급격히 느려집니다.