Excel Data Validation: Dropdown Lists and Rules That Stop Bad Data

Every broken report starts as a typo. Someone types Nrth instead of North, and a SUMIFS quietly returns a number that's 40% too low. Someone types 500 where they meant 5, and a forecast goes out with an extra zero. Someone types 12/01/2026 and Excel stores it as text, and the whole date axis collapses.

The instinct is to fix it afterwards — a cleaning pass, a TRIM, a helper column. Data Validation moves the fix upstream. It puts the rule at the point of entry, where a wrong value costs one second to correct instead of an hour to hunt down.

Tip: Data Validation lives on the Data tab, in the Data Tools group. The keyboard route is Alt → A → V → V, which is worth learning if you set up more than a handful of rules.

1) What Data Validation Actually Does (and Doesn't)

Data Validation is a rule attached to a cell that is checked when someone types into that cell and presses Enter. That sentence contains every limitation worth knowing.

What it does:

• Blocks or warns about typed entries that fail the rule
• Offers a dropdown list so the correct value is one click away
• Shows a tooltip before entry and a message after a failure

What it does not do:

That table is not a list of bugs; it's the design. Validation is a guide rail, not a lock. If you need a genuine guarantee — nobody can ever store a wrong value here — you need sheet protection, and even then a determined user with copy-paste will get around it.

Used for what it's good at, though, it eliminates the overwhelming majority of real-world data entry errors, because the overwhelming majority of them are honest typos by people who wanted to enter the right thing.

2) Your First Dropdown List

🎯 Scenario: The Region column should only ever contain North, South, East or West. Right now it's free text.

Data Setup:

• Column A: Order ID
• Column B: Date
• Column C: Region
• Column D: Rep
• Column E: Product
• Column F: Qty

Select C2:C9, open Data → Data Validation, and on the Settings tab set Allow to List. In the Source box, type the values separated by commas:

North,South,East,West

Leave In-cell dropdown ticked, click OK, and every cell in the range now has an arrow. Press Alt + ↓ on a validated cell to open the list from the keyboard — no mouse needed.

Three things people get wrong immediately:

1. Use commas, not semicolons. The Source box uses commas as separators regardless of your regional list separator setting. A typed North;South becomes one single option called "North;South".
2. No spaces after the commas. North, South creates an option that literally starts with a space, which then fails to match "South" in every lookup you write later.
3. 255 characters, maximum. The typed Source box is a text field with a hard limit. Four regions fit comfortably; forty products do not.

Pitfall: A typed list is invisible to the rest of the workbook. Nothing else can count it, sort it, or check against it, and updating it means reopening the dialog on every range that uses it. Type a list only when it is genuinely permanent and genuinely short — Yes,No is a good candidate. Everything else belongs in cells.

Mini exercise: Add a Paid,Pending,Refunded dropdown to a new Status column in G. Then try typing paid in lower case — it will be accepted, because validation lists are not case sensitive.

3) Lists That Live in Cells (and Grow by Themselves)

🎯 Scenario: The product list changes every quarter. You want to add a product in one place and have every dropdown in the workbook pick it up.

Put the list somewhere real — ideally a sheet called Lists that nobody scrolls to. Say products sit in Lists!$A$2:$A$6. In the Source box, reference them:

=Lists!$A$2:$A$6

Since Excel 2010 you can point at another sheet directly like this. The catch is the range is fixed: add a sixth product in A7 and no dropdown will see it.

Option A — a Table (the one to use). Convert the list range to a Table with Ctrl + T and name it tblProducts. A Table grows automatically when you type below it. The Source box, however, refuses structured references typed directly — =tblProducts[Product] gets rejected with a complaint about reference operators. The reliable route is one extra step:

1. Formulas → Name Manager → New
2. Name: ProductList
3. Refers to: =tblProducts[Product]

Then use the name in the Source box:

=ProductList

Now the Table grows, the name follows it, and every dropdown in the workbook updates with no further work.

Option B — a spill range (Microsoft 365). If your list should be de-duplicated or sorted, generate it with a formula:

=SORT(UNIQUE(tblOrders[Product]))

Put that in Lists!$C$2, let it spill, and point the Source box at the spill range using the # operator:

=Lists!$C$2#

The dropdown now resizes itself as the underlying data changes — a genuinely self-maintaining list.

Result: Adding "Docking Station" to the source updates every dropdown in the file, instantly, with no dialog boxes reopened.

Pitfall: Named ranges and spill references work across sheets. Direct =Lists!$A$2:$A$6 references work too — but if someone later deletes the Lists sheet, every rule silently degrades to accepting anything. Naming the range at least makes the dependency visible in Name Manager.

4) Dependent Dropdowns: Second List Follows the First

🎯 Scenario: Pick North in column C, and the Rep dropdown in column D should offer only Alice and Dana — not every rep in the company.

The classic INDIRECT method. It works in every version of Excel, and it depends on a naming convention:

1. On the Lists sheet, put each region's reps in its own column.
2. Name each column range exactly after the region it belongs to: a range named North, one named South, one named East.
3. Set the Region dropdown in C2:C9 as usual.
4. For D2:D9, set Allow to List and use:

=INDIRECT($C2)

Note the mixed reference: $C2 locks the column but lets the row travel, so row 5's rep list reads row 5's region. Getting this wrong — writing $C$2 — is the single most common failure, and it produces a sheet where every row offers the reps of whatever is in C2.

Two rules the naming convention imposes:

• Names cannot contain spaces. A region called "North East" needs a range named North_East, and the formula becomes =INDIRECT(SUBSTITUTE($C2," ","_")).
• Names cannot start with a digit. A category called "2026 Range" needs a prefix, and INDIRECT has to add it back: =INDIRECT("cat_"&$C2).

The modern FILTER method (Microsoft 365). No naming convention at all. In Lists!$E$2, spill the matching reps out of a lookup table:

=FILTER(tblReps[Rep], tblReps[Region]=$C2)

Then point the Source box at =Lists!$E$2#. It's cleaner, it handles spaces and digits without ceremony, and it survives someone renaming a region. The cost is one helper cell per validated column, and it only works if every row shares the same "current" region — for a row-by-row dependent list, INDIRECT remains the more practical choice.

Pitfall: Neither method clears a stale value. Set the region to North, pick Alice, then change the region to South — Alice stays in column D, now invalid, and no alert fires. Section 8 covers how to find those.

5) Numbers, Dates and Text Length

🎯 Scenario: Quantity must be a whole number between 1 and 100. Dates must fall inside the current year and not be in the future.

Not everything needs a list. The Allow dropdown has five more rule types, and they all take a minimum and maximum — either typed, or read from a cell.

Whole number for F2:F9:

Allow:   Whole number
Data:    between
Minimum: 1
Maximum: 100

Typing 0, 250 or 2.5 is now rejected. Choose Whole number rather than Decimal deliberately — an order of 2.5 laptops is exactly the kind of fat-finger error worth blocking.

Date for B2:B9, using formulas as the bounds:

Allow:   Date
Data:    between
Start:   =DATE(2026,1,1)
End:     =TODAY()

Building the bounds with DATE() rather than typing 01/01/2026 avoids the day/month ambiguity that bites international teams. TODAY() is evaluated at the moment of entry, so this rule quietly moves forward every day — no maintenance.

This rule has a useful side effect: it rejects text that looks like a date. If someone pastes a value that Excel stored as text, it isn't a date, so it fails. That single rule prevents most of the "why won't my dates sort" problems in a shared workbook.

Text length for Order ID in A2:A9:

Allow:   Text length
Data:    equal to
Length:  8

Crude but effective — ORD-1001 is eight characters, ORD-101 is seven, and the second one gets stopped.

About "Ignore blank". It's ticked by default, and it means: an empty cell passes the rule. It does not mean the cell can be left empty only sometimes, and unticking it does not make entry mandatory — a cell nobody touches is never validated at all. There is no way to force entry with Data Validation; that requires a formula check elsewhere or VBA.

The related surprise: if your list Source points at a range containing blank cells, those blanks appear as empty options in the dropdown, and unticking "Ignore blank" does not remove them. Trim the source range instead, or generate it with UNIQUE/FILTER.

6) Custom Rules: Any Formula That Returns TRUE

🎯 Scenario: Order IDs must be unique, must start with ORD-, and must end in four digits.

Set Allow to Custom and you get a single formula box. The rule is simple: write a formula that returns TRUE for a valid entry, relative to the top-left cell of your selection.

Uniqueness — the most useful custom rule there is. Select A2:A9 and enter:

=COUNTIF($A$2:$A$1000, A2)=1

Result: Typing an Order ID that already exists is rejected. Read it as count how many times this value appears in the column; a valid entry appears exactly once — itself. Note the absolute source range and the relative A2: that pairing is what makes the rule travel correctly down the column.

Format checking — combine text functions with AND:

=AND(LEFT(A2,4)="ORD-", LEN(A2)=8, ISNUMBER(VALUE(RIGHT(A2,4))))

Three conditions, all of which must hold: the right prefix, the right total length, and four characters at the end that are genuinely numeric. ORD-1001 passes; ORD-100A and INV-1001 do not.

Blocking leading and trailing spaces — the invisible error that breaks every lookup you'll ever write:

=A2=TRIM(A2)

Cross-column logic — a rule that reads another cell in the same row. If Keyboard orders are capped at 10 but everything else at 100:

=IF($E2="Keyboard", AND(F2>=1, F2<=10), AND(F2>=1, F2<=100))

Blocking future dates without the Date rule:

=B2<=TODAY()

Pitfall: Custom rules are written relative to the active cell of the selection when you open the dialog. If you select A2:A9 but A5 happens to be the active cell, Excel interprets your formula as if written for A5 and offsets it for every other row. Always select the range starting from its top-left cell — click A2 first, then extend with Shift + click.

7) Input Messages and Error Alerts

A rule that silently rejects an entry is annoying. A rule that explains itself is helpful. The two remaining tabs of the dialog are where that happens, and both are routinely skipped.

Input Message appears as a small tooltip when the cell is selected, before anyone types. Use it for the thing people would otherwise get wrong:

Title:   Quantity
Message: Whole units only, 1 to 100. For bulk orders above 100, split into multiple lines.

Error Alert appears after a failed entry, and its Style setting is the important choice:

Most people leave everything on Stop, which is right for structural rules — a region that isn't a region will break a SUMIFS. But Stop applied to a soft rule teaches people to work around your sheet: they'll paste values in, or keep a separate untracked copy. If a quantity of 150 is unusual but legitimate, Warning is the honest setting.

Whatever you choose, write the message. The default is a generic "The value you entered is not valid", which tells the user nothing about what would be valid. Compare:

Default:  The value you entered is not valid.
Better:   Region must be one of: North, South, East, West.
          Use the dropdown arrow, or press Alt+Down Arrow.

The second one gets the sheet filled in correctly. The first one gets you a message asking what's wrong with the file.

8) Finding the Data That Already Broke the Rules

🎯 Scenario: You've added validation to a sheet with 4,000 existing rows. Rules don't check history — so how much of the old data is invalid?

Circle Invalid Data. Open the arrow next to the Data Validation button on the Data tab and choose Circle Invalid Data. Excel draws a red oval around every cell whose current value fails its rule. It is the fastest audit in Excel, and almost nobody knows it exists.

Three things to know about it:

• The circles are display-only. They vanish when you save and close, and disappear individually as each value is corrected.
• Only the first 255 invalid cells are circled. On a badly broken sheet, fix a batch and run it again.
• Clear Validation Circles on the same menu removes them all.

Finding which cells even have rules. Press F5 → Special → Data validation → All, and Excel selects every validated cell on the sheet. Choose Same instead, and it selects every cell sharing the rule of the active cell — the quickest way to answer "did that rule actually get applied to the whole column?"

Auditing without touching the sheet. For a permanent count rather than a one-off look, put a COUNTIFS in a corner:

=SUMPRODUCT(--(COUNTIF(ProductList, E2:E9)=0))

Result: The number of Product entries that don't appear in the approved list. Anything above 0 is a cleaning job, and unlike red circles this figure survives a save.

9) Copy-Paste: How Rules Quietly Disappear

This is the section that explains why validation "stopped working" on a sheet nobody admits to changing.

Pasting into a validated cell replaces its rule. Not the value only — the entire set of cell attributes, validation included. Copy an unvalidated cell from anywhere, paste it over C5, and C5 no longer has a Region dropdown. No warning is shown. The rule is simply gone, and the next person to type Nrth there will succeed.

Worse, the pasted value is never checked, so a paste can both insert invalid data and delete the rule that would have caught it, in one keystroke.

What to do about it:

• Paste values only — Ctrl + Alt + V then V — pastes the value and leaves the destination's formatting and validation intact. Teach this one shortcut and most of the problem disappears.
• Paste Special → Validation copies only the rules from one range to another. This is the correct way to extend an existing rule to new rows, and much safer than reopening the dialog and re-typing the source.
• Use a Table. New rows added at the bottom of a Table inherit the validation of the rows above automatically — no dragging, no re-application.
• Protect the sheet (Review → Protect Sheet) with the validated cells locked. Protection blocks the paste itself, which is the only reliable defence. Note that this is a different tool doing the work: validation guides, protection enforces.

Rebuilding a rule someone destroyed: select a cell that still has the correct rule, copy it, select the damaged range, then Paste Special → Validation. Ten seconds, and no risk of the new rule differing subtly from the old one.

Quick Checklist (Before You Trust the Sheet)

• Lists live in cells, not typed into the Source box (unless they're two options and permanent)
• The list source is a Table column via a defined name, or a spill range, so it grows on its own
• Dependent dropdowns use $C2 — column locked, row relative
• Custom formulas were written with the top-left cell active when the dialog opened
• Custom formulas mix absolute source ranges with relative row references
• Date bounds are built with DATE() or TODAY(), never typed as text
• Every rule has an Error Alert message that says what is allowed
• The alert Style matches the rule's real strength — Warning for soft limits, Stop for structural ones
• Circle Invalid Data has been run once against the pre-existing rows
• The entry range is a Table, so new rows inherit the rules
• Anyone who pastes into this sheet knows about Ctrl + Alt + V → V

Common Pitfalls Summary

1. Expecting validation to catch pasted values: it never does. Paste bypasses the rule and overwrites it at the same time.
2. Semicolons in a typed list: North;South is one option, not two. The Source box always uses commas.
3. Spaces after commas: South looks identical on screen and matches nothing in any lookup.
4. Hitting the 255-character limit: a long typed list silently truncates. Move it to cells.
5. Structured references in the Source box: =tblProducts[Product] is rejected. Wrap it in a defined name.
6. $C$2 in a dependent dropdown: every row then shows the same sub-list. Use $C2.
7. Named ranges with spaces or leading digits: INDIRECT can't find them. Substitute underscores or add a prefix.
8. Stale dependent values: changing the first dropdown does not clear the second. Circle Invalid Data finds them.
9. A custom formula written against the wrong active cell: the rule works on one row and misbehaves on the rest.
10. Assuming "Ignore blank" makes a field required: nothing in Data Validation can force an entry.
11. Adding rules and never auditing history: existing values are never re-checked. Run Circle Invalid Data once.
12. Stop alerts on soft rules: people route around barriers. Warning keeps them inside the sheet.

Conclusion

Data Validation is the cheapest quality control in Excel. A dropdown takes thirty seconds to set up and removes an entire category of error permanently — not by finding typos faster, but by making them unavailable.

The mental model worth keeping is the one from section 1: validation is a guide rail, not a lock. It handles the honest mistake, which is almost all of them. It does nothing about paste, formulas, or history. Knowing exactly where the rail ends is what separates a sheet that stays clean from one that looks protected and isn't.

If you only take three things from this article: put your lists in a Table and reference them by name, write an error message that names the valid values, and run Circle Invalid Data on any sheet you inherit. Those three habits cover most of the distance between a spreadsheet people fight with and one they can just fill in.

If you want practice with clean data entry, try the exercises in the app — each scenario starts from a real, slightly broken sheet.