Data Validation is a feature that controls what type of data can be entered into a cell.
It helps prevent errors and ensures accurate data entry.
It is commonly used for:
- Creating dropdown lists
- Restricting number ranges
- Allowing specific dates
- Limiting text length
- Preventing duplicate entries
How to Apply Data Validation
Steps:
- Select the cell or range
- Go to Data tab
- Click Data Validation
- Choose validation criteria
- Click OK
Types of Data Validation
Whole Number
Allow only numbers within a range.
Example: Allow numbers between 1 and 100
Decimal
Restrict decimal numbers within a range.
List (Dropdown Menu)
Create a dropdown selection list.
Example:
Ali
Sara
Ahmed
Steps:
- Choose List
- Select source range or type values separated by commas
Date
Restrict date between specific dates.
Example:
Allow dates between 01-Jan-2026 and 31-Dec-2026
Time
Allow only specific time range.
Text Length
Limit number of characters.
Example:
Allow maximum 10 characters.
Custom Formula
Create advanced validation using formulas.
Example: Prevent duplicate entries
=COUNTIF(A:A, A1)=1
Input Message & Error Alert
You can customize:
- Input Message → Shows instructions when cell is selected
- Error Alert → Shows warning if invalid data is entered
Error types:
- Stop (Strict)
- Warning
- Information
Practical Uses
- Attendance systems
- Invoice forms
- HR data entry
- Student result sheets
- Survey forms
Important Tips
- Use dropdown lists to avoid spelling mistakes
- Always test validation rules
- Protect sheet to prevent rule removal
- Combine with conditional formatting for better control
Why Data Validation is Important
It improves data accuracy, reduces human error, and makes spreadsheets more professional and user-friendly.
Data Validation is one of the most powerful data control tools in Excel.