Home Courses Microsoft Excel Day 22: Data Validation and Drop-down Lists
WEEK 4 DAY 22 OF 30 60 MINUTES WORKBOOKS INCLUDED

Day 22: Data Validation and Drop-down Lists

Prevent human data entry mistakes: build in-cell dropdown lists, number limits, input prompts, and error alerts.

Easy-Learn Framework

Mastering Day 22: What, Why & How

Memory Rule Included
Day 22: Data Validation and Drop-down Lists Visual Diagram
? WHAT Is It?

Restricting what users can enter into a cell by providing an in-cell clickable dropdown list or numeric range limit (e.g., Age must be between 18 and 60, or City must be picked from a list).

! WHY Does It Matter?

Prevention is 100x better than cure! If data entry staff select from a standardized dropdown ('Cash', 'UPI', 'Card'), reports and formulas will never fail due to spelling variations.

⚙ HOW Does It Work?
Step 1 Select the cells you want to restrict.
Step 2 Go to Data Tab → Data Validation → Under 'Allow', select 'List'.
Step 3 In 'Source', type comma-separated items: Cash, UPI, Card, NetBanking (or select a reference range on your sheet).
Memory Hook & Golden Rule (Remember This Forever):

"Garbage prevention is better than garbage cleaning! Dropdowns guarantee clean, standardized entries every time."

Learning Objectives

What You Will Master Today

  • Data validation criteria: List, Whole Number, Decimal, Date, Text Length
  • Creating in-cell dropdown lists using comma-separated values or sheet cell ranges
  • Input Messages: helpful hover tooltips guiding data operators on required format
  • Error Alerts: 'Stop' (blocks invalid data), 'Warning', and 'Information' dialogs
Important Keyboard Shortcuts:
Alt + A + V + V (Data Validation Dialog)Alt + Down Arrow (Open In-Cell Dropdown)
Guided Tutorial

Step-by-Step Practical Walkthrough

Step 1

Select the cell or column where you want to restrict user entry.

Step 2

Go to Data tab > Data Validation.

Step 3

In Settings, under 'Allow', choose 'List'.

Step 4

In 'Source', type comma-separated values (e.g., 'Cash, UPI, Credit Card, Bank Transfer') and click OK.

Solved Classroom Example

Haldwani Hotel Mountain View - Guest Room Reservation Form

Pre-filled Data & Answers

Study the completed reference worksheet below. Every column illustrates the standard formatting, formula structure, and alignment applied by professional office operators.

Booking IDGuest NameRoom Category [Dropdown]Nights Stay [1-30]Payment Mode [Dropdown]Check-In Status
BKG-701Rohan MehtaDeluxe AC3Credit CardConfirmed
BKG-702Pooja VermaExecutive Suite2UPIConfirmed
BKG-703Amit KumaonStandard Non-AC1CashPending Check-In
BKG-704Kavita BishtDeluxe AC4Bank TransferConfirmed
Download Solved Sample Sheet Includes complete working formula cells, proper currency symbols, and clean borders.
Download Solved Sheet (.xlsx)
Student Assignment

Corporate HR Employee Leave Request Application

Your Turn to Practice
Exercise Tasks Checklist:
  • Task 1: Apply List Validation to Department: 'Accounts, Sales, Operations, IT Support, Human Resources'.
  • Task 2: Apply List Validation to Leave Type: 'Casual Leave, Sick Leave, Earned Leave, Maternity Leave'.
  • Task 3: Restrict 'No of Days' to Whole Numbers between 1 and 15 with an Error Alert message.
  • Task 4: Test typing '25' or 'Vacation' to confirm Excel rejects invalid inputs with your custom error message.

Exercise Data Template Preview: Download the .xlsx file below and execute each task independently.

Employee NameDepartment [Dropdown]Leave Type [Dropdown]No of Days [1-15]Emergency Contact
Rajesh SharmaAccountsCasual Leave29876543210
Meena RawatSalesSick Leave39812345678
Sunil BishtOperationsEarned Leave59012398765
Anita PandeyIT SupportCasual Leave19411122334
Download Practice Exercise Sheet Pre-structured practice workbook with raw datasets ready for hands-on classroom lab solving.
Download Practice Sheet (.xlsx)
Mentor Pro Tip from Haldwani Classroom Lab:

If your list options might change in the future, put them in a dedicated reference column and point the Data Validation source to that range instead of hardcoding commas!

Day 21: Conditional Formatting... All 30 Topics Day 23: Text Functions — Part ...

Need 1-on-1 Guidance on Day 22?

Join our hands-on classroom lab sessions at Digital Skills Academy in Haldwani. Every student receives a dedicated PC workstation, live instructor reviews, and interview prep.