Data Validation in Sheets?
Data validation is what turns a messy spreadsheet column into a clean dropdown list — no more 'yes', 'Yes', 'YES', and 'yess' in the same report. It restricts what people can type into cells, which keeps shared sheets consistent and formulas working. Setting it up takes a minute, and this guide covers the whole thing, from simple lists to the gotchas.
TL;DR / Quick Fix
Quick fix: select your cells, go to Data then Data validation, and choose Dropdown to offer a fixed list of choices. Use a range on a hidden settings tab for lists you might edit later. Validation guides entry — it does not fix data already typed wrong.
Follow these steps
- Select the cells to control. Click and drag over the cells that should only accept certain values. Validation applies to the range you select — get the range right first, because editing it later is fiddly.
- Open Data validation. Go to Data in the menu, then Data validation. The dropdown list walkthrough shows the exact clicks if you want the visual version — the key choice is the criteria type.
- Choose Dropdown for fixed choices. Pick 'Dropdown' as the criteria and type your options separated by commas, like Approved, Pending, Rejected. Everyone editing those cells now gets a neat arrow with exactly those choices.
- Use a range for lists that change. If the options live in cells (say A1:A10 on a Settings tab), choose 'Dropdown (from a range)' instead. Edit the list cells and every dropdown updates automatically — no retyping.
- Decide how strict to be. The 'reject input' option blocks anything not on the list; 'show warning' just flags it. Use reject for codes and categories, warning for free-text fields where exceptions are legitimate.
- Test it as another user would. Click a validated cell and try typing something wrong. Confirm the dropdown appears and the rule behaves — catching a misconfigured range now beats discovering it in a hundred-row report.
What NOT to do
- Do not apply validation to cells that already hold bad data expecting it to clean them. Validation only governs new entries — old mess stays until you fix it by hand.
- Do not use a typed list for options that will grow. Re-typing the list every time is how dropdowns go stale; point at a range instead.
- Do not forget the range when you copy a sheet. Validated ranges can break or point at the wrong place after copying — verify the rules survived the move.
- Do not set 'reject input' on cells where exceptions are normal. Over-strict validation trains people to work around your sheet instead of in it.
Frequently asked questions
Can I have different dropdowns in the same column?
Yes — apply validation to separate ranges within the column. Each range keeps its own rule, so row groups can have different option lists.
Why is my dropdown arrow missing?
The rule may point at an empty or deleted range, or validation was pasted over. Reopen Data validation for those cells and check the criteria still point somewhere real.
Does validation work in the Sheets mobile app?
Mostly yes — dropdowns appear and rules apply. Setting up the rules themselves is much easier on a computer, though.