엑셀 데이터 유효성 검사 사용법 - 드롭다운 목록과 입력 제한

여러 명이 함께 사용하는 엑셀 파일에서 가장 흔한 문제가 잘못된 데이터 입력이에요.
부서명을 "영업팀"으로 써야 하는데 "영업 팀", "영업1팀", "영엽팀" 등 제각각으로 입력되면 SUMIF나 피벗테이블이 제대로 작동하지 않죠.

 

데이터 유효성 검사는 셀에 입력할 수 있는 값을 미리 제한하는 기능이에요.
드롭다운 목록, 숫자 범위 제한, 날짜 범위 제한 등을 설정할 수 있습니다.

 

이 글에서는 드롭다운 목록 만들기, 숫자/날짜 제한, 입력 메시지와 오류 경고 설정까지 다룹니다.

데이터 유효성 검사 메뉴 위치

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

 

대화상자가 열리면 [설정], [입력 메시지], [오류 경고] 세 개 탭이 있어요.
[설정] 탭에서 제한 조건을 지정하고, 나머지 탭은 선택사항입니다.

드롭다운 목록 만들기 (가장 많이 쓰는 기능)

방법 1 - 값을 직접 입력

항목이 적을 때 간편한 방법이에요.

 

1. 드롭다운을 넣을 셀(또는 범위)을 선택
2. [데이터] → [데이터 유효성 검사] 클릭
3. [설정] 탭 → 제한 대상: "목록" 선택
4. 원본에 항목을 쉼표로 구분해서 입력 (예: 영업팀,인사팀,개발팀,기획팀)
5. [확인]

 

이제 해당 셀을 클릭하면 드롭다운 화살표가 나타나고, 목록에서 선택할 수 있어요.
단축키 Alt + 아래방향키로도 목록을 열 수 있습니다.

방법 2 - 셀 범위 참조

항목이 많거나, 나중에 항목을 추가/삭제해야 할 때 유용해요.

 

먼저 별도 위치(같은 시트 또는 다른 시트)에 목록 항목을 세로로 입력합니다.

	H
1	부서목록
2	영업팀
3	인사팀
4	개발팀
5	기획팀

 

1. 드롭다운을 넣을 셀 범위를 선택
2. [데이터] → [데이터 유효성 검사]
3. 제한 대상: "목록"
4. 원본 입력란을 클릭한 뒤, 목록이 입력된 범위(H2:H5)를 마우스로 선택
5. [확인]

 

이 방법은 H열의 값을 수정하면 드롭다운 목록도 자동으로 바뀌는 장점이 있어요.
단, 항목을 추가해서 범위가 늘어나면 원본 범위도 수정해야 합니다.

방법 2 보완 - 표(Table)로 만들면 자동 확장

목록 데이터를 "표"(Ctrl+T)로 변환해두면, 항목을 추가할 때 범위가 자동으로 확장돼요.
원본 범위를 매번 수정할 필요가 없어서 편리합니다.

 

1. 목록 범위(H1:H5)를 선택 → Ctrl+T → [확인]으로 표 생성
2. 표 이름을 확인 (기본값: "표1" 등, [표 디자인] 탭에서 변경 가능)
3. 유효성 검사 원본에 =INDIRECT("표1[부서목록]") 형태로 입력

 

또는 이름 관리자([수식] → [이름 관리자])에서 범위에 이름을 지정하고, 원본에 =이름 형태로 넣어도 됩니다.

숫자 범위 제한

특정 셀에 0~100 사이의 숫자만 입력 가능하도록 제한하는 예제입니다.

 

1. 대상 셀 범위 선택
2. [데이터] → [데이터 유효성 검사]
3. 제한 대상: "정수" (소수점을 허용하려면 "소수")
4. 데이터: "사이"
5. 최소값: 0, 최대값: 100
6. [확인]

 

이 범위를 벗어나는 값을 입력하면 오류 메시지가 나타나요.
평가 점수, 할인율, 수량 등에 활용할 수 있습니다.

날짜 범위 제한

특정 기간의 날짜만 입력할 수 있도록 제한하는 방법이에요.

 

1. 대상 셀 범위 선택
2. [데이터] → [데이터 유효성 검사]
3. 제한 대상: "날짜"
4. 데이터: "사이"
5. 시작 날짜: 2025-01-01, 끝 날짜: 2025-12-31
6. [확인]

 

2025년 이외의 날짜를 입력하면 오류가 나요.
프로젝트 기간이나 회계연도에 맞춰 설정하면 입력 실수를 줄일 수 있습니다.

입력 메시지 설정

셀을 선택했을 때 안내 메시지를 표시할 수 있어요.
사용자에게 "어떤 값을 입력해야 하는지" 알려주는 용도입니다.

 

1. [데이터 유효성 검사] 대화상자에서 [입력 메시지] 탭 선택
2. "셀을 선택할 때 입력 메시지 표시" 체크
3. 제목: 부서 선택
4. 입력 메시지: 드롭다운에서 부서를 선택하세요.
5. [확인]

 

해당 셀을 클릭하면 노란색 말풍선 형태로 메시지가 표시돼요.
여러 사람이 사용하는 서식 파일에 넣어두면 입력 오류를 줄일 수 있습니다.

오류 경고 설정

유효하지 않은 값을 입력했을 때 나타나는 오류 메시지를 커스터마이즈할 수 있어요.

 

1. [데이터 유효성 검사] 대화상자에서 [오류 경고] 탭 선택
2. 스타일 선택:
   • 중지: 입력을 차단 (가장 엄격)
   • 경고: 경고 후 입력 허용 가능
   • 정보: 안내 메시지만 표시
3. 제목과 오류 메시지 입력
4. [확인]

 

"중지" 스타일은 목록에 없는 값의 입력을 완전히 차단해요.
"경고" 스타일은 사용자가 [예]를 누르면 목록 외의 값도 입력할 수 있습니다.

유효성 검사 삭제

설정된 유효성 검사를 제거하려면:

 

1. 대상 셀 선택
2. [데이터] → [데이터 유효성 검사]
3. [모두 지우기] 클릭
4. [확인]

 

같은 유효성 검사가 적용된 다른 셀도 함께 제거하려면, "변경 내용을 설정이 같은 모든 셀에 적용" 체크박스를 선택하세요.

잘못된 데이터 찾기

이미 입력된 데이터 중 유효성 검사 조건에 맞지 않는 값을 찾을 수 있어요.

 

[데이터] 탭 → [데이터 유효성 검사] 옆의 드롭다운 화살표 → [잘못된 데이터 표시]

 

조건에 맞지 않는 셀에 빨간색 원이 표시됩니다.
기존 데이터에 나중에 유효성 검사를 적용한 경우, 이 기능으로 문제 데이터를 빠르게 찾을 수 있어요.

자주 하는 실수 / 주의사항

1. 복사-붙여넣기로 유효성 검사가 우회됨

다른 셀의 값을 복사해서 붙여넣으면 유효성 검사를 무시하고 입력이 돼요.
완벽한 입력 차단 수단은 아니므로, 중요한 데이터는 "잘못된 데이터 표시" 기능으로 사후 검증하는 것을 권장합니다.

2. 시트 보호 상태에서 유효성 검사 수정 불가

시트가 보호되어 있으면 유효성 검사 설정을 변경할 수 없어요.
수정이 필요하면 먼저 시트 보호를 해제하세요.

3. 드롭다운 원본 범위에 빈 셀이 있으면 목록에 빈 항목이 표시됨

원본 범위 중간에 빈 셀이 있으면 드롭다운에 빈 줄이 나타나요.
목록 항목은 빈 셀 없이 연속으로 입력해야 합니다.

관련 기능 안내

4편에서 다룬 조건부 서식과 함께 사용하면 효과적이에요.
드롭다운으로 선택한 값에 따라 셀 색상을 자동으로 바꾸는 서식을 적용할 수 있습니다.

 

다음 글에서는 CONCATENATE와 TEXTJOIN 함수를 다룰 예정이에요.
여러 셀의 텍스트를 하나로 합치는 함수입니다.