엑셀 데이터 유효성 검사는 셀에 입력할 수 있는 값을 제한해서 오타나 잘못된 형식이 섞이는 걸 막는 기능이다. 목록·숫자 범위·날짜처럼 제한 대상을 정하고 오류메시지까지 설정하는 건 몇 번 해보면 금방 익숙해지는데, 실무에서는 오히려 “분명히 설정했는데 왜 안 먹히지” 하는 순간에 더 자주 막힌다. 설정법부터 안 먹히는 이유까지 한 번에 정리했다.
엑셀 데이터 유효성 검사는 어떻게 설정하나요?
- 셀(범위) 선택: 유효성 검사를 적용할 셀 또는 범위를 드래그
- 데이터 탭 진입: 상단 메뉴 [데이터] → [데이터 유효성 검사] 클릭
- 제한 대상 지정: 설정 탭에서 목록·정수·소수·날짜/시간·텍스트 길이·사용자 지정 중 선택
- 원본 입력: ‘목록’을 골랐다면 원본 칸에 값을 콤마로 직접 쓰거나 셀 범위를 지정
- 오류 메시지 설정: [오류 메시지] 탭에서 스타일(중지/경고/정보)과 안내 문구 작성
제한 대상은 실무에서 ‘목록’(드롭다운)과 ‘사용자 지정’(수식 조건)을 가장 많이 쓴다. 목록은 부서명·상태값처럼 정해진 항목 중에서만 고르게 할 때, 사용자 지정은 “다른 셀 값보다 커야 한다”처럼 조건을 수식으로 걸 때 쓴다.
오류 메시지 스타일은 세 가지로 나뉘는데 의미가 꽤 다르다.
| 스타일 | 동작 | 실무에서 쓸 상황 |
|---|---|---|
| 중지 | 잘못된 값은 아예 입력 자체가 막힘 | 반드시 지켜야 하는 규칙(사번 형식, 예산 상한 등) |
| 경고 | 입력은 되지만 확인창이 뜨고 그대로 두거나 수정 선택 가능 | 권장 범위는 있지만 예외를 허용해야 할 때 |
| 정보 | 알림만 뜨고 바로 입력됨 | 참고용 안내만 필요할 때 |
드롭다운 목록, 다른 시트를 참조하면 왜 오류가 날까요?
목록을 만들 때 원본에 다른 시트의 셀 범위를 직접 타이핑하면(=Sheet2!A1:A10처럼) 엑셀이 오류를 낸다. 같은 시트 안에서는 되는데 다른 시트로 넘어가는 순간 막히는 게 데이터 유효성 검사 목록의 대표적인 함정이다.
해결 방법은 이름을 먼저 정의하는 것이다. [수식] 탭 → [정의된 이름] 그룹 → [이름 정의]에서 그 범위에 이름(예: 부서목록)을 지정한 뒤, 데이터 유효성 검사 원본 칸에는 셀 범위 대신 =부서목록처럼 그 이름을 입력한다. 목록이 담긴 시트를 숨기고 통합 문서를 보호하면 사용자가 원본 목록을 건드리지 못하게 막을 수도 있다.
엑셀 데이터 유효성 검사, 분명히 설정했는데 왜 안 먹힐까?
가장 흔하게 겪는 상황부터 짚어보면, 데이터 유효성 검사는 사용자가 셀에 직접 타이핑할 때만 작동하도록 설계돼 있다. 즉 다른 셀 값을 복사해서 붙여넣거나 채우기 핸들로 드래그해서 채우면 유효성 검사를 그냥 통과해버린다. 협업 문서에서 “왜 규칙을 벗어난 값이 섞여 있지”라는 상황 대부분이 이 경우다. 막으려면 [파일] → [옵션] → [고급] → 편집 옵션에서 ‘채우기 핸들 및 셀 끌어서 놓기 사용’을 끄고, 워크시트까지 보호해야 확실히 막힌다.
그 외에 Microsoft 공식 문서가 안내하는 원인은 이렇다.
| 증상 | 원인 | 확인할 것 |
|---|---|---|
| 값을 넣어도 검사가 아예 안 뜸 | 복사·채우기로 입력함 | 직접 타이핑했는지 확인, 필요하면 채우기 핸들 옵션 끄기 |
| 조건이 맞는데도 오류가 뜸 | 참조 수식에 #REF!·#DIV/0! 등 오류 있음 | 유효성 검사 대상 수식이 정상 계산되는지 확인 |
| 워크시트 보호 후 입력 자체가 안 됨 | 보호 전에 셀 잠금을 안 풀어둠 | 유효성 검사 셀은 보호 걸기 전에 잠금 해제 |
| 수동 계산 모드에서 검사가 어긋남 | 계산 옵션이 수동으로 꺼져 있음 | 수식 탭 → 계산 옵션 → 자동으로 전환 |
표에 이미 데이터가 들어있는 상태에서 유효성 검사를 나중에 걸면, 기존 값이 규칙을 어겨도 자동으로 알려주지 않는다. 이럴 땐 [데이터] → [데이터 유효성 검사] → 드롭다운 화살표 → [원 잘못된 데이터]를 누르면 조건에 안 맞는 셀 주위에 빨간 원이 그려진다. 수정하면 원은 자동으로 사라진다.
엑셀 데이터 유효성 검사, 해제는 어떻게 하나요?
더 이상 필요 없거나 조건을 바꾸고 싶으면 해당 셀(또는 범위)을 선택한 뒤 [데이터] → [데이터 유효성 검사] → 설정 탭 → [모두 지우기]를 누르면 된다. 여러 셀에 서로 다른 조건이 섞여 있어도 선택한 범위 안의 설정만 한 번에 정리된다.
자주 묻는 질문
Q. 드롭다운 목록에 다른 시트의 셀 범위를 넣었는데 오류가 나요
제한 대상을 목록으로 설정할 때 원본에 다른 시트 범위를 직접 입력하면 오류가 난다. 수식 탭에서 이름 정의로 해당 범위에 이름을 먼저 지정한 뒤, 원본 칸에 그 이름을 입력해야 한다.
Q. 분명히 유효성 검사를 설정했는데 아무 값이나 막 입력돼요
셀을 복사해서 붙여넣거나 채우기 핸들로 드래그하면 유효성 검사가 무시된다. 직접 타이핑할 때만 검사가 작동하도록 설계돼 있어서, 막으려면 파일 > 옵션 > 고급에서 채우기 핸들 사용을 끄고 워크시트를 보호해야 한다.
Q. 유효성 검사를 한 번에 다 지우고 싶어요
지울 셀 범위를 선택한 뒤 데이터 탭 > 데이터 유효성 검사 > 설정 탭 > 모두 지우기를 누르면 된다.
Q. 워크시트를 보호했더니 유효성 검사 셀에 아예 입력이 안 돼요
보호를 걸기 전에 유효성 검사를 적용한 셀의 잠금을 먼저 해제해야 한다. 순서가 바뀌면 보호가 걸린 뒤엔 어떤 값도 입력할 수 없다.
정리하면, 엑셀 데이터 유효성 검사는 설정 자체보다 “왜 안 먹히는지”에서 막히는 경우가 훨씬 많다. 복사·채우기로 우회되는 구조라는 것과, 워크시트 보호는 반드시 셀 잠금 해제 후에 걸어야 한다는 두 가지만 기억해도 대부분의 문제는 바로 풀린다. 유효성 검사만으로 부족하고 셀 자체를 아예 못 건드리게 막고 싶다면 엑셀 시트 보호를 같이 검토하면 된다.