Business & Productivity · Guide
How to create a drop down list in Excel
Data validation turns a column of typos into a column of choices. Here is the five-click version, plus how to build a list that grows on its own and how to add dependent lists.
The Nextversity teamBusiness & Productivity schoolUpdated August 10, 20266 min read
On this page
The short answer
Select the cells, go to Data > Data Validation, set Allow to List, and type your options in the Source box separated by commas. Click OK. Every cell you selected now has an arrow with those choices in it.
That is the whole feature. What makes it worth ten minutes rather than one is the version that maintains itself, which we get to below.
The five-click version
- Select the cells that should carry the list. A whole column works: click the column letter.
- Data tab, then Data Validation (in the Data Tools group).
- On the Settings tab, change Allow from Any value to List.
- In Source, type your options with commas between them:
Open, In progress, Blocked, Done. - Leave In-cell dropdown ticked and click OK.
Typing options directly is fine for a short, fixed list like status values. For anything longer or anything that changes, use a range instead.
The version that maintains itself
Hard-coded options age badly. The moment someone adds a new region, you are back in the dialog. Do this instead:
- Put the options in a column, ideally on a separate sheet called something like
Lists. - Select them and press Ctrl+T to make an Excel table. Give it a name in the Table Design tab, for example
Regions. - Back in Data Validation, click the arrow next to Source and select the table's data column.
Now anyone can add a region to the bottom of that table and every drop down picks it up. No formulas, no editing the rule, and the list stays in one obvious place instead of hiding inside a validation dialog.
The best sheets are the ones a colleague can maintain without asking you how. A named table of options is the difference between a tool and a hostage situation.
Dependent drop downs
A second list that reacts to the first one takes two extra steps.
- Name each sub-list with the exact text of its parent option. Select the fruit options, click the Name Box to the left of the formula bar, and type
Fruit. Repeat forVegetable. - Give the first cell (say A2) a normal drop down containing Fruit and Vegetable.
- For the second cell, set the Source to
=INDIRECT(A2).
Pick Fruit in A2 and B2 shows only the fruit. The catch: names cannot contain spaces, so Soft Drinks needs to be Soft_Drinks in both places. Microsoft's Excel support documents the naming rules, and Microsoft Learn has guided modules if you would rather follow along.
Make the message helpful
Two tabs in the same dialog are worth a look.
- Input Message shows a tooltip when the cell is selected. Use it for the rule behind the list: "Pick the stage the deal is at today."
- Error Alert controls what happens when someone types something else. Stop blocks the entry, Warning lets them override, Information just tells them. Write the message in plain language, because "The value you entered is not valid" helps nobody.
If you want to catch stray values, set the alert to Warning rather than Stop. People paste into spreadsheets constantly, and a hard block on a paste often ends with the validation being deleted altogether.
The limitation nobody mentions
Data validation does not survive a paste. If someone copies a cell from elsewhere and pastes it over your drop down, the rule goes with it and the cell accepts anything from then on. There is no setting to prevent this in normal use. Protect the sheet if it matters, or accept it and check the column occasionally with Conditional Formatting to highlight values that are not in your list.
That is honest rather than encouraging, and it is the sort of thing worth knowing before you build a workbook the whole team relies on.
Where this fits
Drop downs are the first step toward sheets that other people can fill in without breaking. The next steps are protection, lookups against the chosen value, and summaries that read the result. The Excel certificate walks through all of it, the introduction course covers the ground before it, and both live in the Business & Productivity school on one subscription.
If you only needed the five clicks, you already have them. Come back when you want the sheet to run itself.
Questions people ask
How do I create a drop down list in Excel?
Select the cells, go to Data and click Data Validation, set Allow to List, then type your options separated by commas or point Source at a range of cells. Click OK and the arrow appears in each cell.
How do I edit a drop down list?
Select a cell that has the list, open Data Validation again, and change the Source. Tick Apply these changes to all other cells with the same settings so the whole column updates at once.
How do I make a drop down list update automatically?
Put your options in a column, convert it to a table with Ctrl+T, then reference the table column in the Source box. New options added to the bottom of the table appear in the list without editing anything.
How do I remove a drop down list?
Select the cells, open Data Validation, and click Clear All. That removes the rule but leaves whatever values were already typed in the cells.
Can I make one drop down depend on another?
Yes. Name each sub-list to match the parent option, then set the second list's Source to =INDIRECT(A2), where A2 holds the first choice. Choosing Fruit in A2 then shows only the fruit list in B2.