엑셀 유효성 검사로 잘못 입력된 데이터 한 번에 찾는 방법

---

엑셀 유효성 검사로 잘못 입력된 데이터 한 번에 찾는 방법

엑셀로 거래처 관리대장이나 회원 명단, 제품 목록 등을 작성하다 보면 입력 실수가 발생하는 경우가 많습니다. 예를 들어 ‘가나다상사’라고 입력해야 하는데 ‘가나다 상사’처럼 띄어쓰기가 들어가거나, 목록에 없는 거래처명을 입력하는 경우가 대표적입니다.

이처럼 입력된 데이터가 많아질수록 하나씩 확인하는 것은 쉽지 않습니다. 이럴 때 활용하면 좋은 기능이 바로 데이터 유효성 검사입니다.

많은 분들이 데이터 유효성 검사는 입력할 수 있는 값을 제한하는 기능으로만 알고 있지만, 이미 입력되어 있는 데이터 중 잘못 입력된 값까지 찾아주는 기능도 제공합니다.

이번 글에서는 엑셀 데이터 유효성 검사를 이용하여 잘못 입력된 데이터를 한 번에 찾아내는 방법을 알아보겠습니다.

데이터 유효성 검사란?

데이터 유효성 검사는 사용자가 입력할 수 있는 값을 제한하는 기능입니다.

예를 들어 거래처 목록이 정해져 있다면 지정된 거래처만 입력하도록 설정할 수 있으며, 숫자만 입력하거나 날짜만 입력하도록 제한하는 것도 가능합니다.

여기에 더해 이미 입력되어 있는 데이터 중 규칙에 맞지 않는 값을 찾아주는 기능까지 제공하기 때문에 실무에서 매우 유용하게 활용됩니다.

특히 다음과 같은 업무에서 활용도가 높습니다.

거래처 관리
고객 명단 관리
사원 정보 관리
제품 코드 관리
재고 관리
설문조사 데이터 정리

잘못 입력된 거래처 찾아보기

이번 예제에서는 거래처명이 입력된 데이터를 확인해 보겠습니다.

일부 셀에는 정상적인 거래처명이 입력되어 있지만, 몇몇 셀에는 띄어쓰기 오류가 있거나 목록에 없는 거래처명이 입력되어 있습니다.

이런 오류를 하나씩 찾지 않고 엑셀 기능으로 확인해 보겠습니다.

먼저 거래처가 입력된 G4:G18 영역을 모두 선택합니다.

이후 다음 순서대로 이동합니다.

데이터 탭 → 데이터 도구 그룹 → 데이터 유효성 검사

데이터 유효성 검사 창이 열리면 설정 탭에서 제한 대상을 목록으로 변경합니다.

목록으로 제한하기

제한 대상을 ‘목록’으로 선택한 뒤 원본 입력란을 클릭합니다.

이제 정상적인 거래처 목록이 입력되어 있는 I4:I7 범위를 선택합니다.

목록 범위를 지정한 후 확인을 누르면 해당 목록만 올바른 값으로 인정하게 됩니다.

즉, 목록에 없는 거래처명이 입력되어 있다면 엑셀이 이를 잘못된 데이터로 인식하게 됩니다.

잘못된 데이터 한 번에 찾기

이제 입력 오류를 확인해 보겠습니다.

다시 데이터 탭으로 이동한 뒤

데이터 유효성 검사 ▼ → 잘못된 데이터를 선택합니다.

그러면 현재 선택된 범위에서 유효성 검사 규칙을 만족하지 않는 셀을 자동으로 찾아줍니다.

잘못 입력된 셀에는 빨간색 원이 표시되므로 어느 위치가 잘못되었는지 한눈에 확인할 수 있습니다.

예를 들어

가나다상사 → 정상
가나다 상사 → 오류
목록에 없는 거래처명 → 오류

처럼 표시됩니다.

입력 내용을 수정하면 빨간 원도 자동으로 사라집니다.

왜 이 기능이 중요할까요?

실무에서는 수백 개, 수천 개의 데이터를 입력하는 경우가 많습니다.

특히 여러 사람이 동시에 파일을 작성하는 경우에는 입력 방식이 서로 달라 오류가 자주 발생합니다.

예를 들어

띄어쓰기 차이
오타
약어 사용
동일 거래처를 다른 이름으로 입력
목록에 없는 데이터 입력

이런 문제들은 피벗 테이블이나 함수 계산 시 예상하지 못한 오류를 만들 수 있습니다.

하지만 데이터 유효성 검사를 활용하면 이러한 오류를 미리 찾아 수정할 수 있어 데이터의 신뢰도를 크게 높일 수 있습니다.

실무에서 활용하기 좋은 사례

이 기능은 거래처 관리뿐 아니라 다양한 업무에 활용할 수 있습니다.

• 부서명을 지정된 목록으로 관리하기
• 직급 입력 오류 확인하기
• 제품명 통일하기
• 지역명 입력 실수 찾기
• 담당자 이름 오류 확인하기
• 회원 등급 관리
• 학교명 또는 학과명 입력 검증
• 재고 품목 관리

특히 VLOOKUP, XLOOKUP, SUMIF, COUNTIF 같은 함수를 사용하는 데이터에서는 입력값이 정확해야 원하는 결과를 얻을 수 있기 때문에 데이터 유효성 검사는 매우 중요한 기능입니다.

알아두면 좋은 팁

데이터 유효성 검사는 입력을 제한하는 기능과 오류를 찾는 기능을 함께 사용할 때 더욱 효과적입니다.

처음부터 목록만 선택하도록 설정해 두면 잘못된 입력을 예방할 수 있고, 이미 작성된 데이터는 ‘잘못된 데이터’ 기능을 이용해 한 번에 점검할 수 있습니다.

데이터를 자주 수정하거나 여러 사람이 함께 사용하는 엑셀 파일이라면 반드시 활용해 보시기 바랍니다.

마무리

엑셀 데이터 유효성 검사는 단순히 입력을 제한하는 기능이 아니라, 이미 입력된 데이터의 오류까지 손쉽게 찾아주는 강력한 기능입니다.

특히 거래처명, 부서명, 제품명처럼 동일한 데이터를 반복 입력하는 업무에서는 입력 오류가 발생하기 쉽습니다. 이럴 때 데이터 유효성 검사의 ‘잘못된 데이터’ 기능을 활용하면 오류를 빠르게 찾아 수정할 수 있어 데이터의 정확성과 업무 효율을 동시에 높일 수 있습니다.

엑셀을 실무에서 자주 사용하신다면 이번 기능도 꼭 익혀두시길 추천드립니다. 작은 입력 실수를 미리 잡아주는 것만으로도 보고서의 완성도와 데이터의 신뢰도가 크게 향상될 것입니다.