엑셀365 데이터 유효성 검사란?
엑셀365의 데이터 유효성 검사(Data Validation) 기능은 사용자가 셀에 입력할 수 있는 값을 제한하거나 조건을 설정하여 입력 오류를 방지하고, 데이터 일관성을 유지할 수 있게 도와주는 기능입니다.
실무에서는 다음과 같은 상황에 자주 사용됩니다:
- 입력 항목을 드롭다운 목록으로 제한하고 싶을 때
- 숫자 범위 제한 (예: 1~100 사이 점수만 허용)
- 날짜 조건 (예: 오늘 이후의 날짜만 입력 가능)
- 텍스트 길이 제한, 중복 입력 방지 등
즉, 데이터 유효성 검사는 폼을 만들거나, 사용자 입력을 제어할 때 반드시 필요한 기능으로, 업무 자동화와 실수 예방에 큰 효과를 줍니다.
데이터 유효성 검사 설정 방법
기본 경로:
- 제한할 셀 또는 셀 범위 선택
- 상단 메뉴
데이터 > 데이터 유효성 검사클릭 - ‘설정’ 탭에서 원하는 조건 지정
주요 유효성 검사 조건 및 예시
| 유형 | 설명 | 예시 |
|---|---|---|
| 목록 | 지정한 항목만 선택 | ‘남성, 여성’ 드롭다운 |
| 정수 | 특정 범위 내 정수만 허용 | 1~100점 |
| 십진수 | 소수 포함된 숫자 제한 | 0.0~5.0 |
| 날짜 | 날짜 범위 제한 | 오늘 이후만 입력 가능 |
| 시간 | 특정 시간대 입력 제한 | 오전 9시~오후 6시 |
| 텍스트 길이 | 입력 글자 수 제한 | 10자 이하만 입력 |
| 사용자 지정 | 수식으로 자유롭게 제한 | =LEN(A1)=8 (8자리만 허용) |
실무 활용 예제 1: 드롭다운 목록 만들기
목적:
‘지점명’ 입력을 실수 없이 하도록 드롭다운으로 제한
방법:
- 셀 범위 선택 (예: B2:B100)
데이터 > 데이터 유효성 검사 > 설정- ‘허용’ → 목록
- ‘원본’ 입력란에:
서울지점,부산지점,대구지점 - 확인 클릭
활용 팁:
- 드롭다운 목록을 셀 범위로 지정할 수도 있음
- 예:
=지점목록(이름 정의 사용)
- 예:
- 목록을 숨기고 관리하려면 다른 시트에 원본 두고 이름 정의 사용
실무 활용 예제 2: 점수 범위 제한 (0~100)
목적:
점수를 입력할 때 0~100 사이 숫자만 입력하도록 제한
방법:
- 셀 선택 (예: C2:C100)
- 유효성 검사 → 허용: 정수
- 데이터: 사이
- 최소값: 0 / 최대값: 100
팁: 음수나 100 초과 점수 입력 시 자동으로 차단됨
실무 활용 예제 3: 오늘 이후의 날짜만 입력 가능하게 하기
목적:
예약일자를 입력할 때, 오늘 이전 날짜는 입력하지 못하게 설정
방법:
- 셀 선택 (예: D2:D100)
- 유효성 검사 → 허용: 날짜
- 데이터: 크거나 같다
- 시작일:
=TODAY()
효과: 오늘 이전 날짜 입력 시 경고창 표시
실무 활용 예제 4: 셀 길이 제한 (8자리 사번)
목적:
사번 입력 시 8자리로 정확하게 입력하도록 제한
방법:
- 셀 선택 (예: A2:A100)
- 유효성 검사 → 허용: 사용자 지정
- 수식 입력:
=LEN(A2)=8
→ 입력 값이 8자리가 아니면 오류 메시지 발생
오류 메시지와 입력 메시지 설정하기
입력 메시지:
입력 시 나타나는 안내 텍스트 (힌트 역할)
- 탭: ‘입력 메시지’
- 제목: “입력 안내”
- 메시지: “0~100 사이의 숫자를 입력하세요”
오류 메시지:
조건 위반 시 팝업으로 뜨는 경고창
- 탭: ‘오류 메시지’
- 스타일 선택:
- 정지: 입력 차단 (기본값)
- 경고: 경고 후 입력 허용 여부 선택 가능
- 정보: 정보 제공만, 입력은 허용
- 제목: “입력 오류”
- 메시지: “입력 값이 허용되지 않습니다.”
Q&A
Q. 데이터 유효성 검사를 복사한 셀에도 적용할 수 있나요?
네, 가능합니다.
방법:
- 유효성 검사를 설정한 셀을 복사
붙여넣기 옵션 > 유효성 검사만 붙여넣기선택
또는Ctrl + Alt + V > V선택 → 유효성만 붙여넣기
이 방법으로 다른 범위에도 손쉽게 동일 조건을 적용할 수 있습니다.
Q. 유효성 검사 목록에 셀 범위를 동적으로 연결하려면?
- 드롭다운 원본을 다른 셀 범위로 설정:
=지점목록 - ‘지점목록’이라는 이름 정의를 동적 범위로 지정:
=OFFSET(목록시트!$A$1,0,0,COUNTA(목록시트!$A:$A),1)
→ 목록에 항목이 추가되어도 자동 반영됨
활용 예시: 제품명, 지역명, 코드 목록 등 자주 바뀌는 리스트 관리
Q. 유효성 검사로 중복값을 방지할 수 있나요?
기본 유효성 검사에서는 중복 방지 기능이 직접 지원되지 않습니다.
하지만 사용자 지정 수식으로 다음과 같이 설정 가능합니다:
=COUNTIF($A$2:$A$100, A2)=1
→ A2:A100 범위에서 중복이 입력되면 오류 발생
주의: 반드시 범위 앞에 $를 붙여 절대 참조로 지정해야 전체 범위에 적용됩니다.
Q. 유효성 검사 기능이 비활성화돼서 클릭이 안 돼요. 왜 그럴까요?
다음과 같은 경우에는 유효성 검사 기능이 제한되거나 비활성화될 수 있습니다.
- 셀이 표(Tables) 형식일 때 일부 제한 있음
- 셀에 병합된 영역이 포함된 경우
- 다중 선택된 시트에서 설정할 경우
- 워크시트가 보호 상태일 경우 (해제 후 재시도)
해결 방법:
- 셀 병합 해제
- 표를 일반 범위로 변환 (
표 도구 > 표 변환) - 시트 보호 해제 후 설정
엑셀365의 데이터 유효성 검사 기능은 입력 오류를 줄이고, 데이터의 신뢰도를 높이며, 양식을 체계적으로 관리할 수 있는 강력한 기능입니다. 입력 제한부터 안내 메시지, 동적 목록, 중복 방지까지 다양한 활용법을 익혀두면, 폼 설계와 데이터 수집 업무에서 품질이 한 단계 업그레이드될 수 있습니다. 지금부터 실무에 바로 적용해보세요!