Excel functions / References & tools
Drop-down lists
Let people pick a value from a list, so entries are consistent and spelled correctly.
How it works
A drop-down list stops typos such as "Nrth" and "north " creeping into your data, which would break totals and lookups.
To add one in Excel: select the cells, go to Data > Data Validation, choose List under Allow, and in Source either type the options separated by commas (North,South,East,West) or select a range that holds them. Click OK, and each cell shows an arrow to pick from.
Keeping the options in a range, ideally a table, means you can add new options later without editing the rule.
Tips
- Under the Error Alert tab you can choose whether other entries are blocked or just warned about.
- Drop-down values are ordinary text, so COUNTIF and SUMIF work with them as normal.