Data Validation

Data validation controls what people can type in a cell.

Problem: Someone types "abc" in a quantity field. Your formulas break.

Solution: Data validation only allows numbers. If someone types "abc", Excel shows an error.

Why Use Data Validation?

• Prevents typing mistakes
• Keeps data clean
• Makes forms easy to use
• No more fixing wrong entries

The Most Useful: Dropdown Lists

Dropdown lists let users pick from a list instead of typing.

Example: Instead of typing "Yes" or "No", user clicks and picks from a list.

How to create a dropdown:

Step 1: Select the cell where you want the dropdown

Step 2: Go to Data tab

Step 3: Click Data Validation

Step 4: In "Allow", choose "List"

Step 5: In "Source", type your options separated by commas: Yes,No,Maybe

Step 6: Click OK

Now the cell has a small arrow. Click it to see your options.

Example: Restrict Numbers

You want users to only enter numbers between 1 and 100.

Step 1: Select the cells

Step 2: Data tab > Data Validation

Step 3: Allow: Whole Number

Step 4: Data: between

Step 5: Minimum: 1, Maximum: 100

Step 6: Click OK

Now if someone types 150, Excel shows an error.

Types of Data Validation

Adding Error Messages

You can show a custom error when someone enters wrong data.

Step 1: In Data Validation dialog, click "Error Alert" tab

Step 2: Title: "Wrong Entry"

Step 3: Error message: "Please enter a number between 1 and 100"

Step 4: Click OK

Now users see a helpful message instead of a confusing error.

Real Examples

Employee Form:

• Department: Dropdown (HR, Sales, IT, Finance)
• Age: Number between 18-65
• Start Date: Date after today

Order Form:

• Product: Dropdown of products
• Quantity: Number between 1-1000
• Status: Dropdown (Pending, Shipped, Delivered)

Survey:

• Rating: Number 1-5
• Feedback: Text max 500 characters

How to Remove Validation

1. Select the cells
2. Data tab > Data Validation
3. Click "Clear All"
4. Click OK

Validation is now removed.

Summary

• Data validation controls what users can enter
• Dropdown lists are the most useful type
• Data tab > Data Validation
• Prevents mistakes and keeps data clean
• Add custom error messages to help users

This feature is essential when you share spreadsheets with others.