Excel like a finance pro.
Give a cell a dropdown so nobody can mistype it
AltA, V, VEntry & fillA list of the allowed answers, enforced at the point of entry.
Difficulty
1What it does
Half of bad data is somebody typing "Marketng" once. Every report downstream then has two departments where there should be one, and nobody notices until the totals are wrong. Data Validation puts a dropdown on the cell and refuses anything that isn't on the list — the cheapest data-quality control there is, and it works on a sheet anyone else has to fill in.
2The shortcut
- 1Select the cells, then press
Alt + A,V,V. - 2Set **Allow** to
List, and point **Source** at a range holding the allowed values. - 3Use the **Error Alert** tab to say what's wrong in words, rather than leaving the default.
3Before → after
Press the shortcut to play it on a sample sheet.
The allowed list lives in a range; the cell only accepts what's on it
| A — Date | B — Amount | C — Department | |
|---|---|---|---|
| 1 | Sep 3 | 1,240 | Marketing |
| 2 | Sep 4 | 880 | Operations |
| 3 | Sep 5 | 2,150 | ▾ |
Pick from the dropdown, or try the misspelling to see what validation stops.
4When you use it
- Lock a department or class column to the values your chart of accounts actually uses.
- Restrict a date column to this fiscal year so a typo can't land in 2019.
- Give a handover sheet a dropdown so the next person doesn't have to guess.
Want these moves already done for you? Wauvel's free tools download as branded, formula-driven Excel models — Tables, named ranges, and all.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.