엑셀 데이터 유효성 검사로 입력 실수 줄이기 (드롭다운 목록 활용법)

매번 오타로 골치 아픈 입력 시트, 원인은 자유 입력입니다

관공서나 중소기업에서 여러 담당자가 함께 쓰는 엑셀 시트를 열어보면 같은 항목인데도 표기가 제각각인 경우가 많습니다. “완료”, “완료함”, “처리완료”, “Done”처럼 같은 의미를 다르게 적어 놓으면 나중에 필터링이나 집계를 할 때 하나하나 다시 통일해야 합니다. 부서명이나 담당자명도 마찬가지입니다. “경기도청”과 “경기도”를 섞어 쓰면 피벗 테이블에서 별개 항목으로 잡혀 집계 자체가 틀어집니다.

이런 문제의 상당수는 셀에 자유롭게 텍스트를 입력하게 해두었기 때문에 생깁니다. 엑셀의 데이터 유효성 검사 기능을 활용하면 입력 가능한 값을 미리 정해두고, 담당자는 목록에서 클릭만 하도록 만들 수 있습니다. 오늘은 이 기능을 실무에서 바로 쓸 수 있는 수준으로 정리해보겠습니다.

기본 드롭다운 목록 만들기

가장 간단한 방법은 셀 범위를 지정하고 목록을 직접 입력하는 것입니다.

1. 드롭다운을 적용할 셀 또는 셀 범위를 선택합니다.
2. 상단 메뉴에서 데이터 탭 - 데이터 유효성 검사를 클릭합니다.
3. 제한 대상을 “목록”으로 선택합니다.
4. 원본 칸에 쉼표로 구분한 값을 직접 입력합니다. 예를 들어 진행 상태 열이라면 다음과 같이 입력할 수 있습니다.

접수,검토중,보완요청,완료,반려

이렇게 설정하면 해당 셀을 클릭했을 때 오른쪽에 화살표가 나타나고, 클릭하면 다섯 개 항목만 선택할 수 있습니다. 담당자가 직접 타이핑할 필요가 없으니 오타가 원천적으로 발생하지 않습니다.

목록이 자주 바뀐다면 별도 시트로 관리하세요

담당 부서나 거래처 목록처럼 항목이 자주 추가되거나 변경되는 경우에는 직접 입력 방식보다 별도 시트에 목록을 만들어두고 그 범위를 참조하는 방식이 훨씬 관리하기 편합니다.

1. 새 시트를 하나 만들고 이름을 “목록”으로 지정합니다.
2. A열에 부서명을 하나씩 나열합니다. 예를 들면 다음과 같습니다.

A1: 총무과
A2: 기획예산과
A3: 정보통신과
A4: 민원봉사과

3. 이 범위를 선택한 뒤 이름 상자에 “부서목록”처럼 이름을 지정합니다.
4. 원래 시트로 돌아와 데이터 유효성 검사의 원본 칸에 =부서목록을 입력합니다.

이렇게 하면 나중에 부서가 추가되거나 이름이 바뀌어도 목록 시트만 수정하면 모든 드롭다운에 자동으로 반영됩니다. 수십 개 셀에 걸쳐 적용된 드롭다운을 하나하나 고칠 필요가 없습니다.

조건에 따라 목록이 달라지는 종속 드롭다운

한 단계 더 나아가면 앞 셀에서 선택한 값에 따라 다음 셀의 목록이 달라지는 종속 드롭다운도 만들 수 있습니다. 예를 들어 대분류에서 “하드웨어”를 선택하면 소분류에는 “PC”, “모니터”, “프린터”만 나오고, “소프트웨어”를 선택하면 “OS”, “업무프로그램”, “보안프로그램”만 나오게 하는 방식입니다.

이를 위해서는 대분류별로 소분류 목록을 각각 이름 정의해두고, INDIRECT 함수를 원본 칸에 활용합니다.

=INDIRECT($A2)

여기서 A2 셀에 “하드웨어”라는 값이 들어 있다면, 이름이 “하드웨어”로 정의된 범위를 자동으로 불러옵니다. 다만 이름 정의 시 공백이나 특수문자가 들어가면 오류가 나므로 대분류 이름을 그대로 이름 정의에 쓸 수 있도록 미리 다듬어두는 것이 좋습니다.

잘못된 입력을 막는 경고 메시지 설정

목록 방식 외에도 데이터 유효성 검사는 숫자 범위 제한, 날짜 범위 제한, 글자 수 제한 등에도 활용할 수 있습니다. 예를 들어 예산 신청서에서 금액란에 음수나 지나치게 큰 값이 들어가지 않도록 제한하려면 제한 대상을 “정수” 또는 “소수점”으로 설정하고 최소값과 최대값을 지정하면 됩니다.

또한 데이터 유효성 검사 창의 “오류 메시지” 탭에서 잘못된 값을 입력했을 때 보여줄 안내 문구를 직접 작성할 수 있습니다. “0에서 1000만원 사이 금액만 입력 가능합니다”처럼 구체적으로 적어두면 입력하는 사람도 왜 막혔는지 바로 이해할 수 있어 문의가 줄어듭니다.

이미 입력된 데이터에서 잘못된 값 찾아내기

기존에 이미 자유 입력으로 채워진 시트에 뒤늦게 유효성 검사를 적용하는 경우도 많습니다. 이때는 데이터 탭의 “유효하지 않은 데이터”(원 표시) 기능을 사용하면 규칙에 맞지 않는 셀을 자동으로 찾아 빨간 원으로 표시해줍니다. 수백 행짜리 시트에서 눈으로 하나씩 찾는 것보다 훨씬 빠르고 확실합니다.

이렇게 정리한 데이터는 이후 피벗 테이블이나 파이썬으로 집계할 때도 품질이 훨씬 좋아집니다. 반복되는 파일 취합 작업을 자동화하는 방법은 엑셀 반복 업무, 파이썬으로 10분 만에 끝내기 글에서 다루고 있으니 함께 참고하시면 도움이 됩니다. 또한 여러 담당자가 나눠 작성한 파일을 한 번에 정리하는 상황이라면 파일명 규칙을 자동으로 맞추는 방법도 유용한데, 이는 관공서·중소기업 PC, 매일 반복되는 파일 정리 자동화하기 글에서 확인하실 수 있습니다.

실무에 적용할 때 유의할 점

드롭다운 목록을 도입할 때 한 번에 모든 셀에 적용하려고 하면 오히려 혼란스러울 수 있습니다. 우선 입력 오류가 가장 잦은 열, 예를 들어 상태값이나 담당자명, 부서명처럼 반복 입력되는 항목부터 적용해보는 것을 권장합니다. 그리고 목록을 별도 시트로 관리하는 방식을 기본으로 삼아두면, 나중에 조직 개편이나 항목 추가가 있어도 유지보수가 수월합니다.

또한 여러 사람이 함께 쓰는 공유 파일이라면 시트 보호 기능을 함께 걸어 유효성 검사 규칙 자체를 실수로 지우지 못하게 막아두는 것도 좋은 습관입니다. 데이터 탭 - 시트 보호에서 특정 셀만 잠그거나 편집을 허용하는 방식으로 세밀하게 설정할 수 있습니다.

마무리하며

데이터 유효성 검사는 화려한 기능은 아니지만, 여러 사람이 같은 파일을 나눠 쓰는 관공서와 중소기업 업무 환경에서는 오히려 가장 실속 있는 기능 중 하나입니다. 입력 단계에서 오류를 막아두면 이후 집계, 보고서 작성, 시스템 연동 과정에서 겪는 수정 작업이 크게 줄어듭니다. 픽셀앤코드는 이런 반복적인 문서 작업 환경을 진단하고, 필요하다면 엑셀 수준을 넘어선 데이터 정리 자동화나 맞춤형 프로그램 개발까지 함께 상담해드리고 있으니, 업무 효율화가 필요하시면 편하게 문의해 주시기 바랍니다.