Dropdowns and data validation
7 minutes · 4 steps · free, no sign-up
Free typing is where messy data starts: "Done", "done", "Finished" and "DONE" all mean the same thing but a formula sees four different answers. A drop-down list lets people pick from fixed choices, which keeps every entry identical and makes the data easy to count.
- Add a drop-down list to a range of cells
- Pick an answer from the list
- Make a second list for a different column
- Count answers with COUNTIF, which now works reliably
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceHow it works
Select the cells then choose Drop-down
Choices Not started, In progress, Done
Pick click the arrow in the cell
Count =COUNTIF(B2:B6, "Done")The key ideasSummary: Dropdowns and data validation
Data validation controls what can go into a cell. A drop-down list is the friendliest form: it shows the choices, and anything else is refused. That keeps your data consistent, which in turn makes COUNTIF, filters and charts work properly.
Set the list on the whole range at once, and keep the choices short and unambiguous. If the choices will change often, real Google Sheets can read them from a range of cells instead of typing them in, so you can edit the list in one place.
Watch out forCommon mistakes
- Setting validation on one cell only. Select the whole column of answers first, so every row gets the list.
- Choices that overlap. "Done" and "Finished" mean the same thing. Pick one word for each meaning.
- Typing extra spaces in a choice. "Done " with a trailing space is a different answer. Keep the list tidy.
- Assuming validation fixes existing data. It only controls new entries. Old messy values stay until you change them.
Next stepTry it in the real Google Sheets
In Google Sheets, choose Data → Data validation and add a rule of type "Dropdown". You can also tick the option to show a warning instead of rejecting a bad value, and set colours for each choice.