Chapter 7: Data Validation in Excel – Restrict Input & Add Dropdowns

🛡️ Chapter 7: Data Validation in Excel

Data validation in Excel helps ensure that the information entered into cells is accurate, consistent, and within expected limits. Whether you’re building a data entry form or managing large datasets, applying validation rules improves data quality and reduces errors. In this chapter, we’ll explore how to set restrictions, add dropdowns, show input messages, and handle invalid entries smartly.

✅ What is Data Validation?

Data validation restricts the type of data or values that users can enter into a cell. It prevents wrong or invalid data from being entered, which helps maintain the integrity of your worksheet.

⚙️ How to Apply Data Validation

1. Select the cells you want to restrict
2. Go to Data > Data Validation
3. In the dialog box, set your validation criteria (e.g., Whole numbers, List, Date)
4. Optionally, add an Input Message and Error Alert
5. Click OK to apply

📋 Types of Validation Rules

• Whole Number: Only allows integers (e.g., 1 to 100)
• Decimal: Accepts decimal numbers (e.g., 0.5 to 10.0)
• List: Creates a dropdown menu from a range or manually entered list
• Date: Accepts only specific date ranges
• Time: Restricts entry to specified time range
• Custom Formula: Advanced rules using logical expressions (e.g., =ISNUMBER(A1))

🔽 Creating a Dropdown List

Dropdowns simplify data entry and eliminate spelling mistakes.

1. Select the cells
2. Choose Data > Data Validation
3. Select List under Allow
4. Enter values separated by commas (e.g., Yes,No,Maybe) or select a range
5. Click OK

ℹ️ Input Message & Error Alert

• Input Message: Displays a note when the cell is selected
• Error Alert: Pops up if a user tries to enter invalid data

You can choose the error alert type: Stop, Warning, or Information.

🧠 Example Use Cases

• Marks Entry: Allow only numbers between 0 and 100
• Employee Form: Create dropdown for department (HR, Sales, IT)
• Project Tracker: Restrict dates to within current year

🛠️ Advanced Data Validation (Custom Formula)

• =LEN(A1)=10 → Accept only 10-character entries
• =AND(A1>=1, A1<=100) → Accept values between 1–100
• =ISNUMBER(A1) → Accept numbers only

📝 Practice Task

1. Create a form with columns: Name, Age, Department, Joining Date
2. Apply data validation:
   • Age: 18–60 (Whole Number)
   • Department: Dropdown list (HR, Sales, IT, Admin)
   • Joining Date: Between Jan 1, 2023 and Dec 31, 2025
3. Add input messages and error alerts for each

✅ Conclusion

Data validation adds a strong layer of control and reliability to your Excel worksheets. By guiding users with dropdowns and alerts, it improves the data entry process and ensures consistency. In the next chapter, we’ll move into Pivot Tables—Excel’s secret weapon for data summarization and reporting.