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

잘못 들어온 데이터를 나중에 정리하는 것보다 처음부터 막는 편이 훨씬 빨라요. 데이터 유효성 검사(Data Validation)를 쓰면 셀에 어떤 값을 받을지 정할 수 있어요. 드롭다운 목록에서 고른 값만, 정해진 범위의 정수만, 이번 분기의 날짜만 받는 식이죠. 이 글에서는 이런 규칙을 만들고, 무엇을 입력해야 하는지 알려 주는 메시지를 붙이고, 동료가 시트를 채우다가 수식을 망가뜨리지 않도록 수식을 보호하는 방법을 배워요.

예시는 간단한 작업 관리표예요. 작업마다 부서, 팀, 상태, 시작일을 입력하면 F열이 그 작업이 4분기의 며칠째에 시작하는지 계산해요.

fx

	A	B	C	D	E	F
1	작업	부서	팀	상태	시작일	4분기 경과일
2	가격표 업데이트	영업	내부	승인	2026-10-05	5
3	로그인 버그 수정	개발	플랫폼	대기	2026-11-12	43
4	신입 교육	지원	채팅	승인	2026-10-20	20
5	3분기 결산	재무	회계	거절	2026-12-01	62

드롭다운 목록 만들기

가장 쓸모 있는 규칙은 드롭다운 목록(drop-down list)이에요. 값을 직접 입력하는 대신 목록에서 고르기 때문에 "영업", "영엽", "영업부"가 한 열에 섞일 일이 없어요.

1. 목록을 넣을 셀을 선택하세요

   상태 칸인 D2:D5를 선택하세요. 같은 규칙이 모두 적용되도록 한 번에 선택하세요.

2. 데이터 유효성 대화 상자를 여세요

   데이터› then 데이터 도구› then 데이터 유효성 검사를 클릭하세요. 데이터 유효성 대화 상자가 설정(Settings) 탭이 열린 상태로 나타나요.

3. 목록을 고르고 항목을 입력하세요

   제한 대상(Allow)에서 목록(List)을 고르세요. 그러면 나타나는 원본(Source) 상자에 항목을 쉼표로 구분해서 입력하세요.

   승인,대기,거절
   

   드롭다운 표시는 체크된 그대로 두세요. 체크를 해제해도 규칙은 작동하지만 화살표가 나타나지 않아요.

4. 확인을 클릭하고 써 보세요

   확인을 클릭한 다음 D2를 클릭하세요. 셀 오른쪽에 화살표가 나타나요. 화살표를 클릭하거나 Alt+Down Arrow를 눌러 상태를 고르세요. 이번에는 완료를 직접 입력해 보세요. 목록에 없는 값이라서 엑셀이 받아들이지 않아요.

긴 목록은 범위로 지정하기

고정된 항목 세 개 정도라면 직접 입력해도 괜찮아요. 하지만 항목이 많거나 자주 바뀐다면 항목을 셀에 적어 두고 규칙이 그 셀을 가리키게 하세요. 흔히 쓰는 방법은 항목만 모아 두는 시트를 따로 만드는 거예요. 여기서는 시트 이름을 Lists로 했어요.

fx

	A	B	C	D	E	F	G
1	부서	상태		영업	개발	지원	재무
2	영업	승인		현장	플랫폼	전화	급여
3	개발	대기		내부	모바일	이메일	회계
4	지원	거절		파트너	QA	채팅	감사
5	재무

관리표에서 B2:B5를 선택하고 데이터 유효성 대화 상자를 여세요. 목록을 고르고 원본 상자를 클릭한 다음, Lists 시트로 가서 A2:A5를 선택하세요. 엑셀이 참조를 대신 입력해 줘요.

=Lists!$A$2:$A$5복사

이제 부서 이름을 바꾸거나 새 부서를 추가할 때 규칙을 하나하나 고칠 필요 없이 Lists 시트만 고치면 돼요. 단, A5 아래에 항목을 추가했다면 원본 범위도 그만큼 늘려야 해요.

일정 범위의 정수나 날짜만 받기

같은 대화 상자로 숫자와 날짜도 제한할 수 있어요. E열의 시작일은 모두 2026년 4분기 안에 있어야 해요.

1. 날짜 셀을 선택하고 대화 상자를 여세요

   E2:E5를 선택하고 데이터› then 데이터 도구› then 데이터 유효성 검사를 클릭하세요.

2. 규칙을 정하세요

   제한 대상에서 날짜를, 제한 방법에서 해당 범위를 고르세요. 그러면 나타나는 두 상자를 채우세요.

   • 시작 날짜: =DATE(2026,10,1)
   • 끝 날짜: =DATE(2026,12,31)

   평소처럼 2026-10-01 형식으로 날짜를 입력하거나 날짜가 들어 있는 셀을 가리켜도 돼요. DATE 수식은 지역 설정과 상관없이 똑같이 작동해요.

3. 확인을 클릭하고 시험해 보세요

   E2에 2027-01-15를 입력해 보세요. 엑셀이 오류를 표시하고 값을 받아들이지 않아요. 2026-11-03을 입력하면 그대로 들어가요.

정수도 같은 방식이에요. 수량 열이라면 제한 대상에서 정수, 제한 방법에서 해당 범위를 고르고 최소값에 1, 최대값에 500을 입력하세요. 0, -3, 2.5, 열 같은 입력은 모두 거부돼요. 제한 대상에는 이 밖에도 소수점, 시간, 텍스트 길이(반드시 여섯 글자여야 하는 코드 같은 경우), 그리고 올바른 입력일 때 TRUE를 돌려주는 수식이라면 무엇이든 쓸 수 있는 사용자 지정이 있어요.

F열의 수식은 4분기의 며칠째인지 계산해요. 나중에 보호할 셀이 바로 이런 셀이에요.

=E2-DATE(2026,10,1)+1복사

F2에 5 대신 날짜가 표시되면 F열의 표시 형식을 일반이나 숫자로 바꾸세요.

설명 메시지와 오류 메시지 추가하기

이유도 알려 주지 않고 입력을 거부하는 규칙은 쓰는 사람을 답답하게 만들어요. 같은 대화 상자의 다른 두 탭으로 이 문제를 해결할 수 있어요. E2:E5를 다시 선택하고 데이터 유효성 대화 상자를 여세요.

1. 설명 메시지를 쓰세요

   설명 메시지(Input Message) 탭에서 셀을 선택하면 설명 메시지 표시는 체크된 그대로 두세요. 제목에 시작일, 설명 메시지에 2026년 10월 1일부터 12월 31일 사이의 날짜를 입력하세요.처럼 입력하세요. 누군가 셀을 선택하는 순간, 입력하기도 전에 셀 옆에 이 메시지가 나타나요.

2. 오류 메시지를 쓰세요

   오류 메시지(Error Alert) 탭에서 유효하지 않은 데이터를 입력하면 오류 메시지 표시는 체크된 그대로 두세요. 스타일을 고르고, 제목에 4분기가 아닌 날짜, 오류 메시지에 이 관리표는 2026년 4분기만 다뤄요. 10월 1일부터 12월 31일 사이의 날짜를 입력하세요.처럼 입력하세요.

3. 확인을 클릭하세요

   잘못된 날짜를 입력해 보세요. 엑셀의 기본 문구 대신 직접 쓴 메시지가 나타나요.

스타일에 따라 규칙이 얼마나 엄격한지가 달라져요.

보고서에 쓰이는 부서처럼 수식이 의존하는 값에는 중지를 쓰세요. 수량이 500을 넘는 경우처럼 드물지만 허용할 수는 있고 한 번 더 확인하면 좋은 값에는 경고를 쓰세요.

첫 번째 목록에 따라 바뀌는 두 번째 목록 만들기

관리표의 팀 목록에는 B열에서 고른 부서의 팀만 나와야 해요. 이런 목록을 종속 드롭다운 목록이라고 해요. 이름 있는 범위와 INDIRECT 함수를 써서 만들어요.

1. 팀 목록마다 부서 이름을 붙이세요

   Lists 시트에서 D1:G4(부서 머리글과 그 아래의 팀)를 선택하세요. 수식› then 정의된 이름› then 선택 영역에서 만들기를 클릭하세요. 선택 영역에서 이름 만들기(Create Names from Selection) 대화 상자에서 첫 행만 체크하고 확인을 클릭하세요. 엑셀이 이름 네 개를 만들어요. 영업은 D2:D4, 개발은 E2:E4를 가리키는 식이에요.

2. 팀 셀에 목록 규칙을 추가하세요

   관리표에서 C2가 활성 셀이 되도록 C2:C5를 선택하세요. 데이터 유효성 대화 상자를 열고 목록을 고른 다음 원본에 다음을 입력하세요.

   =INDIRECT(B2)복사

   INDIRECT는 B2에 들어 있는 영업 같은 텍스트를 그 이름의 범위로 바꿔 줘요. B2에 $ 기호가 없기 때문에 C3의 규칙은 B3을, C4의 규칙은 B4를 읽어요.

3. 확인을 클릭하세요

   B열이 아직 비어 있으면 원본이 현재 오류로 계산된다는 경고가 나와요. 예를 클릭해서 규칙을 그대로 두세요. 이제 B3에서 개발을 고르고 C3의 목록을 열어 보세요. 플랫폼, 모바일, QA만 나와요.

이름에는 공백을 넣을 수 없어요. 그래서 선택 영역에서 만들기는 고객 지원 같은 머리글을 고객_지원으로 바꾸고, INDIRECT는 그 이름을 찾지 못해요. 부서 이름을 한 단어로 하거나, 이름을 직접 붙여서 맞추세요. 또 C열을 채운 뒤에 B열을 바꿔도 C열은 지워지지 않아서, 한 행에 영업과 플랫폼이 같이 들어가는 일이 생길 수 있어요. 아래에 나오는 잘못된 데이터 기능으로 이런 행을 찾을 수 있어요.

수식 셀 보호하기

유효성 검사는 무엇을 입력할지는 제어하지만, F열의 수식 위에 다른 값을 덮어쓰는 것까지는 막지 못해요. 그건 시트 보호(Protect Sheet)가 해요. 시트 보호는 두 부분으로 작동해요. 모든 셀에는 잠금(Locked) 설정이 있고 기본으로 켜져 있지만, 이 설정은 시트를 보호해야 비로소 효과가 생겨요. 그래서 입력 셀의 잠금을 먼저 풀고 나서 시트를 보호해요.

1. 입력 셀의 잠금을 해제하세요

   B2:E5를 선택하세요. Ctrl+1을 눌러 셀 서식(Format Cells)을 열고 보호 탭에서 잠금의 체크를 해제한 다음 확인을 클릭하세요. F열과 머리글은 잠긴 채로 두세요.

2. 시트를 보호하세요

   검토› then 보호› then 시트 보호를 클릭하세요. 원하면 암호를 입력하고(엑셀이 한 번 더 입력하라고 해요), 허용할 내용 목록에서 잠긴 셀 선택과 잠기지 않은 셀 선택은 체크된 그대로 둔 다음 확인을 클릭하세요.

3. 시험해 보세요

   D3에 입력해 보세요. 그대로 입력되고 드롭다운 목록도 계속 나타나요. 이번에는 F3에 입력해 보세요. 보호된 시트에 있는 셀이라며 엑셀이 거부해요.

   F3fx=E3-DATE(2026,10,1)+1

   	A	B	C	D	E	F
   1	작업	부서	팀	상태	시작일	4분기 경과일
   2	가격표 업데이트	영업	내부	승인	2026-10-05	5
   3	로그인 버그 수정	개발	플랫폼	대기	2026-11-12	43
   4	신입 교육	지원	채팅	승인	2026-10-20	20
   5	3분기 결산	재무	회계	거절	2026-12-01	62

나중에 고칠 일이 생기면 검토› then 보호› then 시트 보호 해제를 클릭하세요. 암호를 정했다면 안전한 곳에 적어 두세요. 엑셀은 잊어버린 암호를 찾아 주지 않아요.

규칙 점검하고 관리하기

• 이미 들어 있던 잘못된 값 찾기. 유효성 검사는 새로 입력하는 값만 확인해요. 규칙에 맞지 않는 기존 값을 찾으려면 데이터 유효성 검사 단추의 화살표를 클릭하고 잘못된 데이터(Circle Invalid Data)를 고르세요. 엑셀이 해당 값에 빨간 동그라미를 그려요. 유효성 표시 지우기를 고르면 동그라미가 사라져요.
• 규칙이 있는 셀 찾기. 홈› then 편집› then 찾기 및 선택› then 데이터 유효성 검사를 쓰면 시트에서 규칙이 있는 셀이 모두 선택돼요.
• 값은 빼고 규칙만 복사하기. 규칙이 있는 셀을 복사하고 붙여 넣을 셀을 선택한 다음, Ctrl+Alt+V를 눌러 선택하여 붙여넣기(Paste Special)를 여세요. 유효성 검사를 고르고 확인을 클릭하세요.
• 규칙 지우기. 셀을 선택하고 데이터 유효성 대화 상자를 연 다음 모두 지우기를 클릭하세요.

자주 하는 실수

• 유효성 검사가 걸린 셀 위에 붙여 넣기. 일반 붙여넣기를 하면 값과 함께 셀의 규칙까지 바뀌고, 붙여 넣은 값은 검사도 받지 않아요. 선택하여 붙여넣기로 값만 붙여 넣고, 많은 데이터를 붙여 넣은 뒤에는 잘못된 데이터로 확인하세요.
• 드롭다운 표시의 체크 해제하기. 규칙은 작동하지만 화살표가 없어서 사람들이 정확한 철자를 짐작해서 입력해야 해요.
• 목록 범위를 너무 좁게 잡기. 원본 범위 아래에 새 항목을 입력해도 드롭다운 목록에는 나타나지 않아요. 범위를 넓히거나 위의 팁처럼 표의 열에 이름을 붙여서 쓰세요.
• 잠금을 해제하기 전에 보호하기. 시트부터 보호하면 입력 셀까지 모든 셀이 잠겨요. 보호를 해제하고, 입력 셀의 잠금을 푼 다음 다시 보호하세요.
• 시트 보호를 보안 기능으로 여기기. 시트 보호는 실수를 막아 주는 기능이지, 작정한 사람까지 막지는 못해요. 게다가 암호를 잊어버리면 나도 못 고쳐요. 기밀 데이터는 공유하는 통합 문서에 아예 넣지 마세요.

연습

연습

실제 입력 양식에 유효성 검사 적용하기

1. 요청 양식, 신청자 명단, 진행 상황 관리표처럼 입력을 받는 데 쓰는 시트를 여세요. 마땅한 게 없다면 이 글의 작업 관리표를 입력하세요.
2. 한 열에는 항목을 직접 입력한 드롭다운 목록을, 다른 열에는 별도의 Lists 시트에 있는 범위를 쓰는 드롭다운 목록을 추가하세요.
3. 세 번째 열에 정수나 날짜 규칙을 추가하세요.
4. 규칙 하나 이상에 설명 메시지와 알기 쉬운 오류 메시지를 쓰세요. 잘못된 값을 입력해서 시험해 보세요.
5. 부서와 팀처럼 서로 관련된 열이 있다면 INDIRECT로 종속 드롭다운 목록을 만드세요.
6. 입력 셀의 잠금을 풀고 시트를 보호한 다음, 수식 셀에 덮어쓸 수 없는지 확인하세요.

실력 점검

• 셀에 정해진 값만 들어가도록 드롭다운 목록을 만들 수 있나요?
• 셀에 일정 범위의 정수나 날짜만 입력되도록 제한할 수 있나요?
• 설명 메시지와 사용자 지정 오류 메시지를 추가하고, 알맞은 오류 메시지 스타일을 고를 수 있나요?
• 입력 셀은 열어 두고 수식 셀만 보호할 수 있나요?