If you’ve ever spent an afternoon untangling a spreadsheet because someone typed “complete” where they should have typed “Complete” — or entered a date backwards — you already know the pain. One small inconsistency can quietly break formulas, corrupt reports, and eat hours you didn’t have to spare.
There’s a fix for this, and it’s built right into both Excel and Google Sheets. It’s called Data Validation, and once you start using it, you’ll wonder how you ever managed without it.
That’s why I use Data Validation on every sheet my team touches. It’s not fancy, but it saves me from dumb mistakes that cost real money.
What Data Validation Actually Does
At its core, Data Validation lets you put guardrails on a cell. You decide what kind of data is allowed in — a number, a date, a specific word — and anything else gets rejected before it can cause problems.
It does three things really well:
- Keeps your data consistent. Everyone picks from the same list, so you never end up with five versions of the word “Pending.”
- Blocks impossible entries. Negative ages, out-of-range dates, text in a numbers column — all stopped at the door.
- Guides the person entering data. You can show a small message the moment someone clicks a cell, telling them exactly what you need.
Setting Up a Dropdown List
The most practical use of Data Validation is creating a dropdown menu — a small list of approved options that pops up when someone clicks a cell. Here’s how to do it properly.
Step 1: Build Your Source List First
Before anything else, type out your approved options somewhere in the workbook — statuses like Pending, In Progress, Complete, On Hold, for example.
Pro tip I learned the hard way: Put this list on its own tab. I name mine “Lists” or “Config” and then hide it. If the list lives next to your data, someone will delete it by accident.
Step 2A: Setting up in Microsoft Excel
- Select the cells where you want the dropdown to appear — say, column B for a status tracker.
- Go to the Data tab and click Data Validation.
- Under “Allow,” choose List.
- In the Source box, click the arrow and select your list from the Config sheet (it’ll look like
='Config'!$A$2:$A$5). Those dollar signs matter — they lock the reference. - Hit OK.
A small arrow now appears in each cell. Users can only choose from your list.
Step 2B: Setting up in Google Sheets
- Select your target cells.
- Go to Data → Data Validation → Add Rule.
- Under Criteria, choose Dropdown (from a range).
- Click the grid icon and select your list from the Config sheet.
- Under “On invalid data,” set it to Reject input — this is important. Without it, users can still type whatever they want and bypass the dropdown entirely.
- Click Done.
Make Your Dropdowns User-Friendly with Messages
A dropdown alone isn’t always enough. Sometimes people ignore the arrow entirely and just type. Add messages so people know what to do.
Input Message (shown before someone types)
This is a small tooltip that appears the moment someone clicks the cell. Use it to set expectations:
- Excel: In the Data Validation dialog, click the “Input Message” tab. Add a title like Status Required and a short note: Please select from the dropdown — do not type manually.
- Google Sheets: Use the Help text field in the validation rule to say the same thing.
Error Alert (shown after a wrong entry)
This shows up if someone somehow enters bad data anyway.
- In Excel: Go to the “Error Alert” tab in the Data Validation dialog. Set the Style to Stop (this blocks the entry completely, rather than just warning). Write a clear message: Only pre-approved options are allowed. Please use the dropdown.
- In Google Sheets: This is handled automatically when you set “On invalid data” to Reject input.
Beyond Dropdowns: Other Validation Tricks I Use
Data Validation isn’t just for lists. Here are 4 rules I use every week:
| What You’re Controlling | Excel Setting | Google Sheets Setting |
|---|---|---|
| Date must fall within a specific range | Allow: Date → Between | Criteria: Date is between |
| Entry must be a whole number for IDs or quantities | Allow: Whole Number → Greater than 0 | Criteria: Number is greater than 0 |
| Text can’t exceed 255 characters | Allow: Text Length → Less than or equal to 255 | Criteria: Text length ≤ 255 |
| Cell B must be empty if Cell A says “Complete” | Allow: Custom → =IF(A1="Complete", B1="") |
Criteria: Custom formula |
These rules work quietly in the background and rarely slow anything down — even complex custom formulas have a negligible impact on spreadsheet performance.
Add Conditional Formatting, Make Invalid Data Impossible to Miss
Even the best validation setup occasionally gets bypassed — maybe someone pastes data from outside, or the rule was temporarily off. Conditional Formatting is your safety net.
Here’s a simple setup that turns any invalid entry bright red:
- Select the same cells your validation rule covers (e.g., column B).
- In Excel: Home → Conditional Formatting → New Rule → Use a formula.
- Enter this formula:
=ISERROR(MATCH(B1, Config!$A$2:$A$5, 0)) - Set the format to a red fill with bold text.
Now, even if something slips through, the spreadsheet flags it immediately. No more hunting through 500 rows to find why a SUMIF broke.
Common Questions
My dropdown in Google Sheets isn’t rejecting invalid entries. Why?
Check whether “On invalid data” is set to Reject input — not Show warning. Warning mode lets people override the rule. Also, Data Validation only checks new entries. It won’t fix what’s already there. Clear the bad cells first, or use Data > Clean up suggestions to find them. Then your rule will work going forward.
Can I pull my dropdown list from a different Excel file?
No — this is a real limitation. Data Validation lists must live in the same workbook. Copy your source list into a dedicated Config sheet within the same file.
Can I hide the dropdown arrow?
Not while the validation rule is active. The arrow is part of how users know the cell has a dropdown. If you need it gone, you’d have to remove the validation rule entirely via Data → Data Validation → Clear All.
Will strict validation rules slow down my file?
For lists and date ranges, practically no. Even custom formula rules are lightweight. If you notice slowdowns, the cause is almost certainly something else — large arrays, volatile functions like NOW(), or excessive conditional formatting.
The Bottom Line
Data Validation doesn’t require advanced Excel knowledge. It takes about five minutes to set up, and it quietly saves hours — sometimes days — of cleanup work down the line. A spreadsheet with proper validation isn’t just neater; it’s genuinely trustworthy. And that’s what makes the difference between a tool people rely on and one they quietly distrust.
Start with one column. Build a dropdown. See what happens. You’ll find reasons to use it everywhere.
Also worth reading: Learn How to Use Array Formulas to Summarize Thousands of Rows Instantly