Your HR team maintains a leave tracker. Some users type “HR”, some type “Human Resource”, and some misspell department names. Later, reports fail because the same department appears in multiple versions. Data Validation solves this by controlling what users can enter.

What is Data Validation?

Data Validation is an Excel feature that controls what users can enter in a cell. It can create drop-down lists, allow only dates, restrict numbers, show input messages, and display error alerts when users enter invalid data.

Simple rule: Use Data Validation wherever users repeatedly choose from a fixed set of options.

Employee Leave Request Form

Leave Request Form
Employee Name
Javed Nilgar
Department
Operations ▼
Leave Type
Paid Leave ▼
Priority
Medium ▼
Approval Status
Submitted ▼

Build a professional input form

For the leave request form, the best drop-down fields are Department, Leave Type, Priority, Reporting Manager and Approval Status. Date fields should use date validation so users cannot enter random text.

Validation workflow

1Create Lists
2Apply Validation
3Test Entries
4Share Form
💡
Professional habit: Keep allowed values on a separate Lists sheet. It becomes easier to update departments, managers, statuses or categories later.
⚠️
Common mistake: Typing drop-down options manually in many different cells. When the list changes, you will have to update every validation rule.
🧪 Practice Lab

Build an Employee Leave Request Form

Download one ZIP file containing the practice workbook, challenge workbook, solution workbook, ready template, cheat sheet, quiz and answer key.

📦 Download Lesson 12 ResourcesWorks as a standalone file and after website deployment

Mini assignment

  1. Create source lists for Department, Leave Type, Priority, Manager and Status.
  2. Apply drop-down validation to the form fields.
  3. Apply date validation to From Date and To Date.
  4. Try entering an invalid department and confirm Excel blocks or warns you.
  5. Save the final version as a reusable template.

Quick quiz

  1. What is Data Validation used for?
  2. Which validation type creates a drop-down?
  3. Why should source lists be kept separately?
  4. What is an Error Alert?

Answers: Controlling user input; List; easier maintenance; warning/block for invalid entries.

FAQ

No, but it reduces common input mistakes by restricting allowed entries.
No. Use them only where users must choose from predefined options.