Half of all messy data is born messy: someone typed dub, someone typed Dublin , someone typed DUBLIN., and three months later a report insists you operate in four cities. You can clean that up every time it happens — the 4:45pm routine exists for a reason — or you can stop it at the door.

Drop-down lists are the door.

The two-minute version

  1. Put your allowed values somewhere sensible — a small Table on a sheet called Lists (say Dublin, Cork, Galway).
  2. Select the cells people will type in.
  3. Data → Data Validation → Allow: List, and point Source at your list.

Now the cell offers a drop-down, and anything outside the list is politely refused. The typo problem doesn’t get cleaned — it never happens.

Lists sheet Region Dublin Cork Galway validation Entry sheet Dublin ▾ Dublin Cork Galway "dub", "DUBLIN." → refused at the door
One small source Table, one validation rule — and the data is born clean.

Do it properly: source from a Table

Most tutorials have you type values straight into the Source box, or point at a fixed range like $A$2:$A$4. Both rot: add “Belfast” later and your drop-downs don’t know.

Instead, make the source list a Table (this is why Tables come first), then point the validation at it. In current Excel you can reference it directly in the Source box:

=Regions[Region]

(In older versions, wrap the column in a named range and use the name.) Now the drop-down grows when the list grows. Nobody maintains anything.

Cascading drop-downs — the party trick that’s actually useful

Choose a region, then see only that region’s branches. Two validations, where the second list depends on the first choice. The modern way is beautifully clean — the second validation’s source is:

=FILTER(Branches[Branch], Branches[Region]=$A2)

One Table of branches with their regions, one FILTER, and the second drop-down reshapes itself to match whatever was picked in A2. (This leans on dynamic arrays — Excel 2021/365. The old INDIRECT-and-named- ranges dance still works, but if you have FILTER, use FILTER.)

Three finishing touches that separate pro sheets

  • Input message (second tab of the validation dialog): a small hint that appears on selection — “Pick the region from the list.” Cheaper than answering the same question weekly.
  • Reject vs warn (third tab): Stop forbids, Warning allows with a nudge. Use Stop for categories, Warning for things with rare legitimate exceptions.
  • Guard the numbers too. Validation isn’t just lists: Allow → Whole Number between 0 and 100 ends the negative-percentage genre of bug at the door as well.

The honest limits

Validation checks what’s typed. Pasting over a validated cell can smuggle anything in — so for sheets other people fill in, protect the layout (Review → Protect Sheet, leaving entry cells unlocked). And validation is not security; it’s ergonomics. Its job is to make the right entry the easy entry.

That’s the real lesson of this stage of the path: the best data cleaning is the cleaning that never needs to happen.