엑셀로 거래처 목록이나 고객 정보, 부서명, 직급 등을 입력하다 보면 같은 데이터를 사람마다 다르게 입력하는 경우가 자주 발생합니다.
예를 들어 거래처 이름을 입력할 때
- 삼성전자
- 삼성 전자
- 삼성전자㈜
- (주)삼성전자
처럼 조금씩 다르게 입력하면 정렬, 필터, 함수, 피벗 테이블을 사용할 때 원하는 결과를 얻기 어렵습니다.
이럴 때 가장 효과적인 기능이 바로 데이터 유효성 검사(Data Validation)입니다.
데이터 유효성 검사를 이용하면 사용자가 직접 입력하는 대신 드롭다운 목록에서 원하는 항목만 선택할 수 있어 입력 오류를 크게 줄일 수 있습니다.
이번 글에서는 엑셀 데이터 유효성 검사로 드롭다운 목록 만드는 방법과 실무 활용법까지 자세히 알아보겠습니다.
데이터 유효성 검사란?
데이터 유효성 검사는 특정 셀에 입력할 수 있는 데이터의 종류나 조건을 제한하는 기능입니다.
예를 들어 다음과 같은 설정이 가능합니다.
- 숫자만 입력
- 날짜만 입력
- 특정 범위의 숫자만 입력
- 글자 수 제한
- 시간만 입력
- 목록에서 선택
- 사용자 지정 수식 사용
그중에서도 가장 많이 사용하는 기능이 목록(List)을 이용한 드롭다운 메뉴입니다.
데이터 유효성 검사를 사용하는 이유
실무에서는 여러 사람이 하나의 엑셀 파일을 함께 사용하는 경우가 많습니다.
이때 직접 입력하도록 두면 오타나 입력 방식이 제각각이라 데이터 관리가 어려워질 수 있습니다.
데이터 유효성 검사를 사용하면 다음과 같은 장점이 있습니다.
입력 실수를 줄일 수 있습니다.
동일한 형식으로 데이터를 관리할 수 있습니다.
정렬과 필터가 정확하게 동작합니다.
함수 계산 오류를 줄일 수 있습니다.
데이터 관리가 훨씬 쉬워집니다.
드롭다운 목록 만들기
이번에는 거래처를 선택할 수 있는 드롭다운 목록을 만들어 보겠습니다.
먼저 G4:G18 범위를 선택합니다.
드롭다운 목록이 필요한 셀 범위를 먼저 선택하는 것이 중요합니다.
데이터 유효성 검사 실행하기
셀을 선택한 후 다음 메뉴를 클릭합니다.
데이터 탭 → 데이터 도구 → 데이터 유효성 검사
데이터 유효성 검사 창이 열리면 다양한 설정을 할 수 있습니다.
제한 대상 목록으로 변경하기
설정 탭에서
제한 대상을 클릭한 후 목록(List)을 선택합니다.
목록을 선택하면 사용자가 직접 입력하는 대신 미리 지정한 항목만 선택할 수 있게 됩니다.
목록 원본 지정하기
이제 원본(Source) 입력란을 클릭합니다.
예를 들어 거래처 목록이
H3:H66
셀에 입력되어 있다면 해당 범위를 드래그하여 지정합니다.
범위를 선택한 후 확인을 클릭하면 설정이 완료됩니다.
드롭다운 목록 확인하기
설정이 완료되면 G열 셀 오른쪽에 작은 화살표가 표시됩니다.
화살표를 클릭하면 미리 등록해 놓은 거래처 목록이 나타납니다.
이제 사용자는 목록에서 원하는 거래처만 선택하면 됩니다.
직접 입력할 필요가 없기 때문에 입력 속도도 빨라지고 오타도 거의 발생하지 않습니다.
데이터 유효성 검사에서 사용할 수 있는 제한 대상
데이터 유효성 검사는 목록만 지원하는 것이 아닙니다.
다양한 입력 조건을 설정할 수 있습니다.
정수
특정 범위의 정수만 입력하도록 제한합니다.
예)
1~100
0 이상
소수
실수 입력만 허용할 수 있습니다.
예)
0.1~99.9
날짜
특정 기간 안의 날짜만 입력하도록 설정할 수 있습니다.
예)
2026년만 입력 가능
시간
업무 시간만 입력하도록 제한할 수 있습니다.
예)
오전 9시 ~ 오후 6시
텍스트 길이
글자 수를 제한할 수 있습니다.
예)
최대 20자
목록
가장 많이 사용하는 기능으로 드롭다운 목록을 만들어 원하는 항목만 선택하게 합니다.
사용자 지정
수식을 이용하여 더욱 다양한 조건을 직접 만들 수 있습니다.
실무에서 가장 많이 사용하는 사례
데이터 유효성 검사는 거의 모든 업무에서 활용됩니다.
대표적인 예는 다음과 같습니다.
- 거래처 선택
- 부서명 선택
- 직급 선택
- 지역 선택
- 제품명 선택
- 담당자 선택
- 재고 상태 선택
- 결제 방법 선택
- 프로젝트 상태 관리
특히 관리대장이나 업무일지, 재고관리표에서는 반드시 사용하는 기능 중 하나입니다.
알아두면 좋은 팁
목록에 사용할 데이터가 변경될 가능성이 있다면 별도의 시트에 목록을 만들어 관리하는 것이 좋습니다.
또한 이름 정의(Name Manager)를 활용하면 목록 범위가 변경되어도 더욱 편리하게 관리할 수 있습니다.
데이터 유효성 검사는 입력 메시지와 오류 메시지 기능도 함께 제공하므로 사용자가 잘못된 값을 입력했을 때 안내 문구를 표시할 수도 있습니다.
마무리
엑셀 데이터 유효성 검사는 입력 가능한 데이터를 제한하여 문서의 정확성과 일관성을 높여주는 매우 유용한 기능입니다.
특히 드롭다운 목록을 활용하면 거래처, 부서명, 직급, 제품명 등 반복해서 입력하는 데이터를 클릭 한 번으로 선택할 수 있어 입력 속도는 빨라지고 오타는 크게 줄어듭니다.
엑셀을 자주 사용하는 분이라면 데이터 유효성 검사는 반드시 익혀야 하는 실무 필수 기능입니다. 한 번만 설정해 두면 여러 사람이 함께 사용하는 문서에서도 정확하고 체계적인 데이터 관리가 가능해집니다.
답글 남기기