A Data Validation Dropdown List in a Sheet
People type anything into a shared sheet, and the data turns into a mess of spellings. A dropdown list limits entries to choices you define, so the data stays clean.
TL;DR / Quick Fix
Quick fix: select the cells that need controlled entries, open data validation, and choose list with your allowed values. Then test the dropdown in one cell and confirm only your choices can be picked and add a short hint so people use it.
Follow these steps
- Select the cells to control. Highlight the column or range where entries must stay consistent. Validation applies to the selection, so select first and configure second.
- Open data validation. Find data validation in the data menu. The dialog is where you define what counts as an allowed entry.
- Choose the list option. Set the criteria to list and type your allowed values separated as the app expects. Those values become the dropdown choices.
- Add an input message. Write a short hint like Choose a region. The message appears when someone clicks the cell and guides them to the dropdown.
- Decide how strict to be. Choose whether to reject other entries or just warn. Reject keeps data perfectly clean, while warn allows rare exceptions.
- Test the dropdown. Click a cell in the range and confirm the dropdown appears with your choices. One live test catches most setup mistakes.
What NOT to do
- Do not leave the list range vague. Define the cells precisely, since validation on the wrong range misses the messy entries.
- Do not type values with inconsistent spelling. The list is only as clean as the values you put in it.
- Do not skip the input message. A dropdown with no hint gets ignored by people who never click the arrow.
- Do not over-validate every column. Validate the fields that must stay consistent, and leave free-text fields free.
Frequently asked questions
What is data validation?
A rule that limits what can be entered in cells, such as a dropdown list of allowed choices.
Can people still type their own value?
Only if you allow it. The strict setting rejects anything outside the list.
Why bother with a dropdown?
Because consistent entries make sorting, filtering, and formulas work, while free text creates a mess of spellings.