눈으로 찾는 데이터 오류, 언제까지 스크롤만 내리시나요
수백, 수천 행짜리 엑셀 명단이나 실적 데이터를 검토할 때 가장 먼저 부딪히는 문제는 “이상한 값이 어디 있는지 모르겠다”는 것입니다. 담당자 명단에 같은 이름이 두 번 들어가 있거나, 매출 실적표에 자릿수가 하나 잘못 들어간 값이 섞여 있어도 육안으로는 쉽게 발견되지 않습니다. 함수를 하나씩 걸어서 찾는 방법도 있지만, 매번 수식을 새로 짜는 것도 번거롭습니다.
이럴 때 엑셀의 조건부 서식 기능을 쓰면 별도 수식 작성 없이도 셀 색깔만으로 문제가 되는 데이터를 즉시 눈에 띄게 만들 수 있습니다. 오늘은 관공서·중소기업 실무에서 자주 마주치는 세 가지 상황, 즉 중복값 찾기, 이상치(비정상적으로 크거나 작은 값) 찾기, 빈 셀 찾기를 조건부 서식으로 처리하는 방법을 정리해보겠습니다.
중복값 찾기: 명단 관리의 기본
담당자 명단, 참가자 명단, 거래처 목록 등에서 중복 입력은 흔한 실수입니다. 엑셀에서는 다음 절차로 중복값을 자동으로 표시할 수 있습니다.
- 중복 여부를 확인할 범위를 선택합니다 (예: A2:A200, 이름 또는 사업자번호 열)
- 리본 메뉴에서 [홈] → [조건부 서식] → [셀 강조 규칙] → [중복 값] 선택
- 강조할 서식(예: 진한 빨강 텍스트가 있는 연한 빨강 채우기)을 지정하고 확인
이렇게 하면 동일한 값이 두 번 이상 나타나는 셀이 즉시 색으로 표시됩니다. 만약 “완전히 같은 행”이 중복인지 확인하려면 여러 열을 조합한 보조 열을 만들고, 그 보조 열에 조건부 서식을 적용하는 방식이 더 정확합니다. 예를 들어 이름과 연락처를 합친 값을 =A2&B2 형태로 만든 뒤 그 열에 중복 규칙을 걸면, 이름만 같고 연락처가 다른 동명이인은 걸러낼 수 있습니다.
이상치 찾기: 수식 기반 조건부 서식
숫자 데이터에서는 평균에서 크게 벗어난 값을 찾는 것이 중요합니다. 매출, 근태 시간, 재고 수량처럼 일정 범위 안에 있어야 할 값이 갑자기 튀는 경우, 다음과 같이 수식을 활용한 조건부 서식을 적용할 수 있습니다.
- 데이터 범위 선택 (예: B2:B200)
- [조건부 서식] → [새 규칙] → [수식을 사용하여 서식을 지정할 셀 결정]
- 아래와 같은 수식 입력
=OR(B2>AVERAGE($B$2:$B$200)+2*STDEV($B$2:$B$200),
B2<AVERAGE($B$2:$B$200)-2*STDEV($B$2:$B$200))이 수식은 평균에서 표준편차의 2배 이상 벗어난 값을 자동으로 찾아 표시합니다. 통계적으로 엄밀한 이상치 탐지는 아니지만, 실무에서 “일단 눈에 띄게 걸러내고 사람이 재검토”하는 용도로는 충분히 실용적입니다. 표준편차 대신 특정 기준값(예: 예산 상한선)을 고정값으로 넣어도 됩니다.
=B2>10000000이런 단순 조건도 조건부 서식에서는 훌륭하게 작동합니다. 조건을 복잡하게 만들 필요 없이, 우리 부서 기준에서 “이상하다”고 판단하는 값을 그대로 수식에 옮기면 됩니다.
빈 셀과 오류값 찾기
필수 입력 항목이 비어 있는 경우도 조건부 서식으로 쉽게 찾을 수 있습니다.
- [조건부 서식] → [셀 강조 규칙] → [빈 셀]
또는 수식으로 직접 지정할 수도 있습니다.
=ISBLANK(C2)수식 오류(#N/A, #REF! 등)가 섞인 데이터라면 다음 수식을 사용합니다.
=ISERROR(C2)VLOOKUP이나 XLOOKUP으로 다른 표를 참조해 만든 데이터에서 오류가 발생했을 때, 이 규칙을 걸어두면 어디서 참조가 끊어졌는지 바로 확인할 수 있습니다.
데이터 막대와 색조로 분포 한눈에 보기
조건부 서식에는 특정 조건을 만족하는 셀만 강조하는 기능 외에, 데이터 전체의 분포를 색이나 막대로 시각화하는 기능도 있습니다. [조건부 서식] → [데이터 막대]를 적용하면 숫자 크기에 비례해 셀 안에 막대가 채워지고, [색조]를 적용하면 값의 크기에 따라 초록에서 빨강으로 점진적으로 색이 바뀝니다. 표 전체를 훑어보며 어느 구간에 값이 몰려 있는지, 유독 튀는 값이 어디인지 감각적으로 파악할 때 유용합니다. 다만 이 기능은 어디까지나 참고용이며, 정확한 판단을 위해서는 앞서 소개한 수식 기반 규칙과 함께 쓰는 것이 좋습니다.
규칙이 많아지면 관리도 필요합니다
조건부 서식 규칙을 여러 개 걸다 보면 어떤 규칙이 어디에 적용되어 있는지 헷갈리기 시작합니다. [조건부 서식] → [규칙 관리]에 들어가면 현재 시트에 적용된 모든 규칙과 적용 범위를 한 번에 확인하고 수정, 삭제할 수 있습니다. 규칙이 서로 충돌하거나 우선순위가 꼬여 원하는 셀이 강조되지 않는 경우도 있으니, 정기적으로 정리해주는 것이 좋습니다.
또한 조건부 서식은 원본 데이터를 바꾸지 않고 시각적으로만 표시해주는 기능이라는 점도 기억해야 합니다. 실제로 데이터를 고치거나 제거하는 작업, 혹은 여러 파일에서 반복적으로 이상치를 걸러내는 작업까지 자동화하고 싶다면 파이썬을 활용하는 방법도 고려할 만합니다. 관련해서는 엑셀 반복 업무, 파이썬으로 10분 만에 끝내기 글에서 pandas를 이용한 데이터 취합·정리 자동화를 다룬 바 있으며, 데이터를 더 체계적으로 손질하는 절차는 비전공자를 위한 데이터 클렌징 입문에서도 확인하실 수 있습니다. 조건부 서식으로 문제를 발견하고, 반복되는 정리 작업은 스크립트로 넘기는 식으로 역할을 나누면 훨씬 효율적입니다.
또한 애초에 잘못된 값이 입력되지 않도록 막는 것도 중요합니다. 드롭다운 목록으로 입력 항목 자체를 제한하는 방법은 엑셀 데이터 유효성 검사로 입력 실수 줄이기 글에서 다뤘으니 함께 참고하시면 좋습니다.
마무리
조건부 서식은 별도 프로그램 설치나 복잡한 수식 없이도, 매일 다루는 엑셀 파일에서 문제가 되는 데이터를 즉시 눈에 띄게 만들어주는 기본기입니다. 중복값, 이상치, 빈 셀, 오류값을 규칙 몇 개로 걸어두면 검토 시간이 크게 줄어듭니다. 다만 데이터 양이 많아지고 반복 작업이 잦아지면 엑셀 기능만으로는 한계에 부딪히는 시점이 옵니다. 그런 경우라면 데이터 가공과 자동화를 함께 검토해보시는 것을 권해드리며, 픽셀앤코드는 이런 실무 데이터 정리와 업무 자동화 구축을 지원하고 있습니다.