A Drop-Down List in a Cell: No More Typos

“Done,” “Complete,” “finished,” “OK” — four different ways to write the same status are all it takes to break a filter, a PivotTable, or any summary based on that status. Data validation with a drop-down list solves the problem at the source: instead of people typing a status by hand, they pick it from a predefined set of options, so a different spelling simply can't happen.

A typical scenario

Filip keeps a team task sheet where five different people fill in the Status column. Without rules, everyone writes the status a little differently — some write “done,” some “Done,” some in Czech “hotovo.” When Filip then wants to filter for all completed tasks, the filter only returns the one spelling variant he picked, and the rest stay hidden.

With a drop-down list (Data Validation → List), the Status column only allows three values: New, In Progress, Done. Coworkers see an arrow in the cell, click it, and pick — Excel rejects anything else. Filtering and PivotTables then work reliably, because there's only ever one way the status is written.

How to do it

1. Select the cells where you want the drop-down list and go to Data → Data Validation.
2. In the Allow field, choose List.
3. In the Source field, enter the allowed values separated by commas, for example “New, In Progress, Done” — or instead of typing them out by hand, reference a range of cells where you've already written the list of options, handy when the options change over time.
4. Confirm — an arrow appears in the cells, clicking it shows the menu, and any value outside the list can't be entered; Excel rejects it and warns you.
5. On the Input Message and Error Alert tabs of the same dialog, you can add custom text that shows up when the cell is selected or when someone tries to enter an invalid value — it makes life easier for coworkers seeing the list for the first time.

The best tools

• Excel's built-in Data Validation — covers the vast majority of needs for consistent data entry, no add-ons required.
• A formatted table (Ctrl+T) combined with Data Validation — a new row added to the table automatically inherits the drop-down rule too.
• Google Sheets — a similar feature is found under Data → Data validation, letting you build a drop-down from a range or from manually entered values.

What you get out of it

• Time: roughly 10 minutes a week that would otherwise go to cleaning up inconsistent entries before every filter or summary.
• Reliable filters and totals: a PivotTable or filter by status catches every record that should count, because there's only ever one way the status is written.
• Faster entry for coworkers: picking from a list is faster than typing the whole word by hand, and nobody can typo it either.

Pro tip

It's worth writing the drop-down list's source into its own cells, say on a hidden sheet, instead of directly into the dialog — when the list of options grows over time, you just add a row to the source list and every cell using the rule picks it up automatically.