How to Create a Drop-Down List in Excel
To create a drop-down list in Excel, select the cells, go to Data > Data Validation, choose Allow: List, and type your options or point to a range. Here is how to build, edit, and troubleshoot one.
Drop-down lists keep data clean by letting people pick from a set of options instead of typing (and mistyping) values. They are built with a feature called Data Validation, and you can set one up in under a minute.
How to make a drop-down list
- Select the cell or cells where you want the drop-down to appear.
- Go to the Data tab and click Data Validation.
- In the Allow box, choose List.
- In the Source box, type your options separated by commas — for example: Yes,No,Maybe
- Click OK. A small arrow now appears in the cell, and clicking it shows your list.
Use a list from a range (easier to update)
Typing options works for short lists, but linking to a range is better when the list changes. Put your options in a column — say cells H1:H10 — then in the Source box enter:
=$H$1:$H$10Now you can edit the list just by changing those cells. If your source column might contain repeats, clean it first — see how to remove duplicates in Excel — so the drop-down only shows each option once.
Edit or remove a drop-down list
- To change the options: select the cell, open Data > Data Validation, and edit the Source, then click OK.
- To remove the drop-down: select the cell, open Data Validation, and click Clear All.
- To copy a drop-down to other cells: copy the cell, then use Paste Special > Validation.
Common drop-down problems
If the arrow does not appear, the cell may have been resized very small, or "In-cell dropdown" was unticked — reopen Data Validation and check it. If people can still type invalid values, the sheet may be protected or the validation was cleared. You can also pair a drop-down with an IF function so another cell reacts to whatever option is chosen.
Frequently asked questions
How do I add color to a drop-down list in Excel?
The drop-down itself has no color, but you can color the results with Conditional Formatting. Select the cells, go to Home > Conditional Formatting, and add a rule for each option — for example, turn cells green when they equal "Done".
Can I make a dependent (conditional) drop-down list?
Yes. A dependent list changes its options based on another cell — for example, choosing a country then showing only its cities. It uses named ranges and the INDIRECT function in the Source box, and is a step up from a basic list.
Why does my drop-down list show a warning when I type?
Data Validation blocks values that are not in the list by design. To allow other entries, open Data Validation, go to the Error Alert tab, and either change the style to Information or untick "Show error alert".