How to Create a Drop-Down List in Excel (2026)
A drop-down list keeps data consistent by letting people pick from preset options instead of typing free text — perfect for status, category, or assignee columns. Excel builds them through Data Validation. Here’s how.
1. Create a Drop-Down From a Typed List
- Select the cell or range where you want the drop-down.
- Go to the Data tab and click Data Validation.
- On the Settings tab, set Allow to List.
- In the Source box, type your options separated by commas:
To do, In progress, Done- Make sure In-cell dropdown is checked, then click OK.
A drop-down arrow appears in each selected cell.
2. Create a Drop-Down From a Range
If your options already live in cells (or you want them editable in one place):
- Put your options in a column somewhere — e.g.,
H1:H5, optionally on a separate sheet. - Select the target cells, then Data > Data Validation.
- Set Allow to List.
- Click in the Source box and select the range, e.g.
=$H$1:$H$5. - Click OK.
Editing the source cells now updates the drop-down options. To pull from another sheet, you can reference it directly (e.g. =Lists!$A$1:$A$20) or use a named range.
3. Add an Input Message or Error Alert
In the Data Validation dialog:
- Input Message tab — show a tooltip when the cell is selected (e.g., “Pick a status”).
- Error Alert tab — choose Stop to block off-list entries, or Warning/Information to allow them with a prompt.
4. Allow Blank Cells
Keep Ignore blank checked (the default) so users can clear a cell. Unchecking it forces a selection.
5. Edit or Remove a Drop-Down
- Edit: Select the cell(s), reopen Data > Data Validation, change the Source, and click OK.
- Apply changes everywhere: tick Apply these changes to all other cells with the same settings before clicking OK.
- Remove: Open Data Validation and click Clear All, then OK. Entered values stay; only the drop-down control is removed.
6. Mac and Web
- Excel for Mac: identical path — Data > Data Validation > Allow: List.
- Excel for the web: Data > Data Validation is supported; type the list or reference a range in Source.
7. Troubleshooting
The drop-down arrow doesn’t show
Confirm In-cell dropdown is checked in the Data Validation settings, and that you’re clicking a cell inside the validated range.
”The Source currently evaluates to an error”
A range Source must start with = and point to valid cells. For a typed list, remove any stray = and just separate items with commas.
Options from a range aren’t updating
The list uses a fixed range. If you added items below it, widen the Source range or convert the source to a Table so it expands automatically.
Related Excel guides: How to use conditional formatting · How to remove duplicates · How to lock cells · How to use VLOOKUP · How to wrap text
Ready to automate your busywork?
Carly schedules, researches, and briefs you—so you can focus on what matters.
Get Carly Today →Or explore our free tools
