Lesson 6.1 – Data Validation Rules & Techniques

Lesson Overview

Data Validation ensures that only accurate and appropriate data is entered into Excel cells. It helps prevent errors, maintain consistency, and improve overall workbook reliability.

1. What is Data Validation?

• Tool to restrict the type, format, or range of data users can enter

• Prevents incorrect or invalid entries that could break calculations

2. Setting Up Basic Data Validation

2.1 Steps to Apply Data Validation

1. Select the cell or range where validation is needed

2. Go to Data → Data Validation → Data Validation

3. Under Settings tab, choose Allow:

   • Whole Number

   • Decimal

   • List

   • Date

   • Time

   • Text Length

   • Custom (for formulas)

2.2 Example – Whole Number Validation

• Restrict input to numbers between 1 and 100:

3. Using Drop-Down Lists

3.1 Purpose

• Limits user to predefined options

• Improves consistency and reduces typing errors

3.2 Steps to Create a Drop-Down

1. Select the cell(s)

2. Go to Data → Data Validation → Allow: List

3. Enter source values separated by commas or select a range

4. Click OK

3.3 Example

• Choose Department: HR, Finance, Sales, IT

4. Custom Formulas in Data Validation

4.1 Purpose

• Apply complex rules not covered by default options

4.2 Example – Prevent Future Dates

• Only allows dates today or earlier

4.3 Example – Restrict Text Length

• Allows maximum 10 characters

5. Input Messages & Error Alerts

5.1 Input Message

• Displays a tip when cell is selected

• Example: “Enter a number between 1 and 100”

5.2 Error Alert Types

1. Stop: Prevent entry if invalid

2. Warning: Warn user but allow override

3. Information: Alert without preventing entry

6. Tips for Data Validation

• Combine drop-downs with formulas for dynamic lists

• Always copy validation rules when applying to multiple cells

• Test validation rules before sharing the workbook

7. Real-Life Examples

• HR: Restrict leave days to 1–30

• Finance: Allow only positive numbers for expenses

• Sales: Choose products from drop-down lists

• Project Management: Prevent future start dates

8. Practice Questions

1. Restrict a cell to accept only numbers between 10 and 500.

2. Create a drop-down list for payment status: Pending, Paid, Overdue.

3. Write a formula to prevent future dates in a Date column.

4. Apply an input message for a score entry cell.

5. Set an error alert that stops users from entering negative values.