Purpose
By the end of this lesson, you will be able to set up data validation rules that prevent bad data from entering a spreadsheet in the first place.
Lesson Explanation
Data validation restricts what can be typed into a cell, catching errors at the point of entry rather than after the fact. Found under Data → Data Validation in Excel, it can restrict a cell to a list of allowed values, a number within a range, a specific date range, or a fixed text length.
A dropdown list is the most common use: restricting a “Status” column to only “Open,” “In Progress,” or “Closed” prevents someone from typing “closed” (lowercase), “Done,” or a typo like “Cloesd” – all of which would break any COUNTIF or SUMIF formula relying on an exact match to “Closed.”
A whole number or decimal validation restricts a cell to a numeric range: setting a “Discount %” field to allow only whole numbers between 0 and 100 prevents someone from accidentally entering 150 or a negative number.
Validation can also include a custom input message (a tooltip shown when the cell is selected) and an error alert (a message shown if the entry violates the rule), making the constraint visible to whoever is entering data rather than a silent trap.
Practice Questions
1. A “Status” column should only ever contain “Open,” “In Progress,” or “Closed.” What Excel feature would best enforce this at the point of entry?
View Answer
Data validation with a dropdown list restricted to those three exact values.
2. Without validation, a “Status” column ends up containing “Closed,” “closed,” “CLOSED,” and “Done” for what should all be the same status. What is the practical consequence for a formula like =COUNTIF(C:C,”Closed”)?
View Answer
It will undercount, since COUNTIF with exact text matching won’t count “closed” (different case may or may not match depending on settings) or “Done” (a different word entirely) as equivalent to “Closed.”
3. A “Discount %” field should only accept whole numbers from 0 to 100. What type of data validation rule would enforce this?
View Answer
Whole number validation, with a minimum of 0 and a maximum of 100.
4. What is the difference between an “input message” and an “error alert” in data validation?
View Answer
An input message is a tooltip shown proactively when the cell is selected, guiding entry; an error alert appears only after an invalid entry is attempted, blocking or warning about it.
5. A colleague enters “N/A” into a numeric “Quantity” field that has no data validation applied. What problem could this create for a later SUM formula on that column?
View Answer
Text like “N/A” is ignored by SUM rather than causing an error, so the total might look plausible while actually being incomplete – a silent, hard-to-catch error.
6. A shared spreadsheet needs a “Region” column to only allow “North,” “South,” “East,” or “West” – with new team members likely to type variations otherwise. What validation approach addresses this, and why is it better than relying on a written instruction?
View Answer
A dropdown list validation – unlike a written instruction that can be missed or ignored, a dropdown makes typing an invalid value structurally impossible.
7. A date field for “Order Date” should never allow a date in the future. What kind of data validation rule would enforce this?
View Answer
Date validation restricting entries to be less than or equal to today’s date.
8. A team lead wants an error message to display “Please enter a value between 1 and 5” if someone tries entering an out-of-range satisfaction score. What data validation feature would show this specific message?
View Answer
A custom error alert, configured with that specific message text.
9. A “Priority” field uses text validation limited to 4 characters or fewer, intending to restrict it to values like “High,” “Med,” or “Low.” What problem might arise with this specific rule choice?
View Answer
“High” is exactly 4 characters, but this rule restricts by character count, not by an approved list, so it would still allow any random 4-character text like “abcd” rather than genuinely restricting to the intended values – a list-based dropdown would be more precise.
10. Why does this lesson describe data validation as catching errors “at the point of entry” rather than after the fact?
View Answer
It prevents invalid data from being entered in the first place, rather than requiring someone to later find and correct bad data that has already been saved into the spreadsheet.
11. A spreadsheet without any data validation has inconsistent entries in a “Yes/No” column: “Yes,” “yes,” “Y,” and “1” all appear for what should be the same answer. What single change would most directly prevent this going forward?
View Answer
Adding a dropdown-list data validation restricted to exactly “Yes” and “No,” preventing any other variant from being typed in.
12. A finance team wants to prevent negative numbers from ever being entered into an “Invoice Amount” column. What data validation setting accomplishes this?
View Answer
Decimal (or whole number) validation with a minimum value of 0.