TL;DR:
Data validation limits what can be typed in a cell, and a list rule turns it into a dropdown.
Keep the choices on their own tab so you can change them in one place.
Write an error message that says what is allowed, because that is the one time people read it.
Anyone who has collected data by typing knows the result: Done, done, DONE, Finished and Done with a space at the end, all meaning the same thing. Counting or filtering by status then gives wrong answers. A dropdown list fixes it at the source by giving people a fixed set of choices.
The feature is called data validation in both Excel and Google Sheets. This guide uses it to build a status column.
What validation does
Data validation puts a rule on a cell or a range. The rule can allow only whole numbers between two values, dates after a certain day, text of a certain length, or a value from a list. The list type shows a small arrow in the cell, and clicking it shows the allowed choices.
The point is consistent data. Pivot tables, COUNTIF, filters and conditional formatting all match text exactly, so a status written two different ways counts as two different statuses.
Where the choices come from
You have two ways to supply a list. You can type the items straight into the rule, separated by commas. That is quick for something like Yes, No. Or you can point the rule at a range of cells holding the items.
Pointing at a range is better for anything you will change. If the choices live in cells on a separate tab named Lists, then adding a new status means typing it once in that list, not rewriting the rule. If you give the cells a clear order and keep the list free of blank rows, the dropdown shows them in that order.
Setting it up
Make the list: On a tab named Lists, put the header Status in A1 and the choices below it.
Select the cells to restrict: Select the column range, for example D2:D200, in your task tab.
Open data validation: Look under the Data menu for Data validation (Excel and Google Sheets both use this name, though the layout of the dialog differs by version).
Choose the list type and point to the range: In Excel choose Allow: List and set the Source to the cells. In Sheets choose Dropdown (from a range). Then pick the choices.
Set what happens on a bad entry and the message: Decide whether to reject a wrong entry or only warn, then write the message.
Test it: Click the arrow and pick one. Then try typing something not on the list, and check that you get your message.
Worked example: a status column with fixed choices
Your task sheet has Task in column A and Status in column D. On the Lists tab you type four choices in A2:A5.
Cell | Choice |
|---|---|
A2 | Not started |
A3 | In progress |
A4 | Blocked |
A5 | Done |
Select D2:D200 on the task tab, open data validation, choose the list type with the source =Lists!$A$2:$A$5, and in the error message write: Pick one of: Not started, In progress, Blocked, Done. Now every status cell offers exactly four choices.
This pays off immediately. =COUNTIF(D2:D200, "Blocked") counts every blocked task, and a conditional formatting rule on the word Blocked colors them. Both depend on every entry being spelled the same, which the dropdown now enforces.
Suppose you later need a fifth status, On hold. If the rule points at A2:A5, extend the range to A2:A6 or leave a spare row inside the range.
Error messages that people read
Both apps let you choose how strict to be. A rule can reject wrong entries outright (the Stop setting in Excel, Reject input in Sheets), or it can allow them with a warning. Strict is best for fields that feed counts and reports. A warning is gentler for notes fields where an exception is sometimes right.
Write the message for a person who is in the middle of typing. State what is allowed, not what went wrong. Pick one of: Not started, In progress, Blocked, Done is more useful than Invalid entry.
Mistakes to avoid
Pasting over the cells. Pasting copies the other cell's rules or removes yours, and the dropdown can disappear. Use paste values when you paste data in.
Existing bad data. Validation checks new entries. Old cells that are already wrong stay wrong. Both apps have a way to circle or find invalid data, or you can fix them with a filter.
A list that contains stray spaces. A choice written as
Donewith a trailing space will be matched exactly. Clean the list.A list on the same crowded tab. Moving it to its own tab protects it from accidental edits and sorting.
Too many choices. A dropdown of forty options is hard to use. Break it into two linked columns, or use a short code.
A few more rules worth knowing
The same dialog handles other rule types. A whole-number rule with a minimum of 0 stops negative quantities. A date rule stops a due date typed as 31/31/2026. A text-length rule can limit a field to a set number of characters. Each one is a tiny way to make bad data harder to enter than good data.
If you are not sure where the dialog is in your version, press DuckyHelper's key and ask out loud while you work, and it can see your screen. After that, setting up a dropdown takes a minute.
Frequently asked questions
Can a dropdown allow more than one choice?
A plain list rule allows one value per cell. Selecting several needs a different setup, which depends on your app and version, so check its help page.
Will a dropdown stop people typing a value?
If you set the rule to reject invalid entries, typing a value that is not on the list is refused. If you set it to warn, they can still choose to keep it.
Get started
