Drop-Down List Builder
Free Drop-Down List Builder: select the cells, then Data ▸ Data Validation ▸ Allow: List, and type your options or point at a range.
Excel expects your system list separator — a comma on English locales, a semicolon on most European ones.
21 / 255 characters (8%)
Or paste the options into a column and use this range:
Select the cells ▸ Data ▸ Data Validation ▸ Allow: List ▸ paste into Source ▸ OK.
How it works
In Excel or Google Sheets, select the cells, then Data ▸ Data Validation ▸ Allow: List, and type your options or point at a range.
Select the cells that should have the list.
On the Data tab, click Data Validation.
Set Allow to List and reference the option cells, for example =$F$1:$F$3. If typing a source instead, check the local list separator with two trial options first.
Click OK; the drop-down arrow appears on each selected cell.
What this does
A drop-down list restricts a cell to a set of approved values, so people pick from a menu instead of typing free text that breaks your formulas and reports. It is built with Data Validation: you supply the allowed values, either typed in directly or held in a range you can edit later, and Excel shows the little arrow that opens the list.
A worked example
To limit column C to Open, In Progress or Done, put those three statuses in F1:F3, one per cell. Select C2:C10, open Data ▸ Data Validation, choose Allow: List, and set Source to =$F$1:$F$3. Each target cell now offers the three statuses. Change F2 and check the updated option in C2. For a typed source, use the list separator accepted by your local validation dialog; first test two options in an unused cell and confirm they appear as separate choices. Drop-downs are the simplest way to keep data clean at the point of entry. Constrained input means your lookups, filters and pivot tables all work, because every value is one of the ones you planned for.
Common mistakes
- Assuming a comma is the list separator in every regional setting, or adding spaces around a separator that become part of an option.
- Using a fixed typed list when the options change often — point at a range instead.
- Forgetting to lock the source range with $ signs, so it shifts when copied.
- Leaving “Ignore blank” unchecked behaviour misunderstood, allowing entries outside the list via paste.
FAQ
How do I make the list update automatically?
A list configured from an Excel Table can grow with the source. A fixed range, including a fixed named range, does not gain extra rows automatically: extend its reference or set up a growing source and check the resulting choices.
Can one drop-down depend on another?
Yes, but a dependent list needs a separate source design for the first selection, such as named ranges with INDIRECT or a filtered helper range. This ordinary-list example and source generator do not implement that dependency.
Does it work in Google Sheets?
Sheets has its own Data ▸ Data validation ▸ Add rule panel. Choose Dropdown from a range and select the source cells, or add the choices individually under Dropdown. Check its invalid-entry settings separately; this is a different setup from the Excel dialog.