Bad data almost always enters at the keyboard — a typo in a status field, a date in next century, “N/A” where a number belonged. Cleaning it up afterward costs far more than stopping it at the door, and stopping it is exactly what data validation does. It checks each entry against a rule the moment someone types, and either guides them or blocks the mistake outright. This walks through the validation techniques that keep a shared spreadsheet honest: dropdowns, type rules, helpful messages, custom-formula checks, and a way to catch whatever slipped in before you set the rules up.
The dropdown list: the workhorse
The most-used validation replaces free typing with a fixed list of choices. No more “Done” versus “done” versus “complete” — everyone picks from the same options.
- Select the cells, then go to Data → Data Validation.
- Set “Allow” to List.
- Type the options separated by commas, or point Source at a range of cells holding the list.
Pointing Source at a range rather than typing the items is the better habit: keep your valid options in a column somewhere (even a hidden sheet), and you can add or change them without reopening the validation dialog. A dropdown does two jobs at once — it speeds entry, since picking beats typing, and it guarantees consistency, since every value comes from the same controlled list. That consistency is what makes the column sortable, filterable, and safe to total later. Microsoft’s guide to applying data validation covers every “Allow” type in detail.
When you point Source at a range, put that list on its own sheet and convert it to a Table (Ctrl+T) — the dropdown then grows automatically as you add options to the bottom, with no source range to redefine. Keep the two checkboxes in mind too: “In-cell dropdown” shows the arrow users click, and “Ignore blank” decides whether an empty cell counts as valid. Both are on by default and rarely need changing, but knowing they exist saves confusion later.
Rules for numbers, dates, and text
Beyond lists, validation can constrain the kind and range of value a cell accepts. These catch the entries that are the wrong type entirely.
| Allow | Catches |
|---|---|
| Whole number, between 1 and 100 | Quantities outside a sensible range |
| Decimal, greater than 0 | Negative prices or amounts |
| Date, after today | Deadlines accidentally set in the past |
| Text length, less than 10 | Codes or IDs longer than they should be |
Each rule rejects values that fall outside its bounds — a “whole number between 1 and 100” rule won’t accept 150, a decimal, or the word “ten.” These type-and-range checks are the quiet workhorses of a clean sheet, because they stop the entries that would otherwise break a downstream formula. A SUM over a column that contains the text “TBD” returns an error or a wrong total; a rule requiring a number means that text never lands there in the first place.
Guide with messages, block with alerts
Validation has two communication channels, and using both turns a rule from a roadblock into a helpful guide.
| Error style | What happens on a bad entry |
|---|---|
| Stop | Blocks it entirely — the user must fix it or cancel |
| Warning | Warns, but “Yes” lets the entry through anyway |
| Information | Notifies only; the entry is always accepted |
The Input Message tab shows a tooltip when the cell is selected — “Enter a date after today” — guiding the user before they type. The Error Alert tab decides what happens when they get it wrong, and the style you pick matters. Use Stop for rules that must hold (a quantity has to be a number); use Warning when the rule is a strong recommendation but rare exceptions are legitimate. A good input message prevents most errors before they happen, which is gentler than catching them after.
Custom-formula validation: the flexible one
When the built-in types don’t cover your rule, “Custom” accepts any formula that returns TRUE for a valid entry and FALSE for an invalid one. This unlocks validation the menu can’t express:
=COUNTIF($A$2:$A$100, A2)=1 'no duplicates allowed
=A2>TODAY() 'date must be in the future
=EXACT(A2, UPPER(A2)) 'force uppercase entryThe first prevents duplicate entries by allowing a value only if it appears once in the column — perfect for an ID or email field. The second enforces a future date; the third rejects anything that isn’t all-caps. Because any formula that resolves to TRUE/FALSE works, custom validation borrows the full power of Excel’s formula language for data entry. If you can write the condition as a formula, you can enforce it as a rule.
One rule trips people up exactly as it does in conditional formatting: write the formula relative to the top-left cell of your selection, and Excel adjusts it for every other cell automatically. If you select A2:A100 and the active cell is A2, write =COUNTIF($A$2:$A$100, A2)=1 — lock the range with dollar signs, leave the single cell (A2) relative. Get that backwards and the rule checks the wrong cell on every row but the first, which is the source of nearly every “my custom validation isn’t working” puzzle.
Dependent dropdowns that narrow themselves
A more advanced pattern makes one dropdown depend on another — pick a category, and the second list shows only items in that category. It runs on the INDIRECT function pointing at named ranges.
=INDIRECT(A2), where A2 holds the chosen category. The second dropdown now shows only that category’s options. The full step-by-step lives in the guide to dependent dropdown lists.
Dependent dropdowns are what make a large set of options manageable: instead of one dropdown with 200 entries, you get a category picker and a short, relevant second list. They take a few minutes more to set up than a plain dropdown, but on any form where choices fall into groups — region then city, department then employee — they’re the difference between a tidy interface and an unusable scroll.
Catch what slipped in earlier
Validation only checks values typed after the rule exists — it doesn’t re-examine data already in the cells. For that, there’s a dedicated tool.
Apply your rule to the whole column, then go to Data → Data Validation dropdown → Circle Invalid Data, and Excel draws a red circle around every existing cell that breaks the rule. It’s the fastest way to audit a sheet you inherited or to check a column after importing data: set the rule you want, circle the violations, fix them, and the circles clear as each is corrected. This pairs naturally with validation’s preventive side — circle the historical mistakes, then let the rule stop new ones. The same validation tools exist in Google Sheets if your data lives there. Validate at the point of entry, guide with a message, block with Stop where it counts, and your data stays clean without a cleanup pass — which is always cheaper than fixing it after the fact.