How to create a drop-down list in Excel
To create a drop-down list in Excel, select the cell, open Data > Data Validation, set Allow to List and give the items in Source. People then pick a value instead of typing it, which keeps the entries consistent. This guide covers the steps, a list that grows by itself, and what to do with the choice afterwards.
1. Build the list in four steps
Select the cell or cells that should get the list. Open Data > Data Validation (on Windows the keys are Alt, A, V, V). Under Allow, choose List. In Source, either type the items separated by the list separator, or select the range that holds them. Keep "In-cell dropdown" ticked and press OK. A small arrow appears next to the cell, and Alt with the down arrow opens the list from the keyboard.
The list separator depends on your regional settings. It is a comma in many setups and a semicolon in others, so if a typed list ends up as one long item, try the other character.
2. Point to a range, so the list can change
A typed list is fixed. A list that points to a range follows that range: change a cell and the drop-down changes. Put the items in a column on their own sheet or beside the table, and use that range as the Source. If the items are in an Excel table, the range grows when you add a row. In Microsoft 365 you can also use a spilled list as the Source, such as a UNIQUE result referenced with the # sign.
3. Use the choice in a formula
The drop-down only stores the text of the choice. A formula can read that cell and look up everything else, such as a price. Here the cell D2 holds the item that was picked, and VLOOKUP returns its price.
Example: Price of the chosen item
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Price | Chosen | Price | |
| 2 | Tea | 3 | Coffee | 4 | |
| 3 | Coffee | 4 | |||
| 4 | Juice | 5 |
Change D2 to Tea or Juice and the price follows. In your own sheet, D2 is the cell with the drop-down.
4. Control what else can be typed
On the Error Alert tab, the Stop style rejects anything that is not in the list. Warning and Information let the person keep a value that is not on the list. Use Stop when other formulas rely on exact spelling. Use the Input Message tab to show a short hint when the cell is selected.
Common mistakes
Typing a list with the wrong separator. The whole text becomes one item.
Fix: Check which separator your Excel uses, or select a range of cells instead of typing.
A range that does not grow. New items you add below the range are missing from the list.
Fix: Turn the items into an Excel table, or point the Source at a spilled list.
Pasting over the cell. Pasting a value into the cell can remove the drop-down and its rule.
Fix: Copy the cell and use Paste Special > Validation to apply the same list elsewhere.
Frequently asked questions
How do I remove a drop-down list in Excel?
Select the cell, open Data > Data Validation and choose Clear All. The value that was in the cell stays.
Can I pick more than one item from a drop-down list?
Not with plain Data Validation. A cell holds one choice. Several choices need a macro, or one list per cell.
Can the second list depend on the first?
Yes. A dependent drop-down changes its items based on the first choice, usually with named ranges and the INDIRECT function. The data validation lesson walks through the basics.
Related functions
- VLOOKUPVLOOKUP finds a value in the first column of a table and returns a value from the same row in another column.
- UNIQUEUNIQUE returns each value of a list once, removing duplicates with a formula that updates by itself.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.
- IFIF checks a condition and returns one value if it's true and another if it's false.
More on drop-down lists
The guide builds a basic list and uses the choice in a lookup. This piece adds lists that depend on each other, a rule that blocks repeated entries, and how to fix a drop-down that misbehaves.
A second list that depends on the first
Pick a country in one cell and see only its cities in the next. The usual method uses named ranges. Create one named range for each country, named exactly like the country and holding its cities. Then the second list reads the name that the first cell contains.
=INDIRECT($B$2)A named range cannot contain spaces, so a choice such as New Zealand needs a name with an underscore, and a helper step to swap the space for it. Keep the names simple if you want the setup to stay easy to maintain.
Use a custom rule to block repeated entries
Data Validation can also check a cell with a formula. Choose Custom under Allow and enter a formula that must be TRUE for the entry to be accepted. This rule accepts a value only if it does not already appear in the column.
=COUNTIF($A$2:$A$100,A2)=1The formula is written for the first selected cell, and Excel adjusts it for the others. The lock signs on the range keep it fixed. It uses COUNTIF.
When the drop-down misbehaves
- No arrow appears: the In-cell dropdown option is unticked, or the sheet is protected.
- Blank rows show up in the list: the Source range reaches past the last item. Shorten it, or use an Excel table.
- A choice that looks right is rejected: the item has a hidden trailing space. Clean the source with TRIM.
- The rule disappeared after pasting: pasting a value over a cell removes its validation. Paste with Paste Special > Values only into cells that have no rule.
To practice the full setup, work through the [data validation lesson](/learn/data-validation).