To create a drop-down list in Excel, select the cells, go to Data > Data Validation, set Allow to List, type the items in Source separated by commas (North,South,East,West) or select the range that holds them, and press OK. Each cell now shows an arrow with those choices, and other entries are refused.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Region | Sales | |
| 2 | Ana | North | 120 | North | 360 | |
| 3 | Ben | South | 85 | |||
| 4 | Cara | North | 240 | |||
| 5 | Dan | East | 60 | |||
| 6 | Eve | South | 150 |
F2 shows 360, the North total. Pick North in B3 and F2 grows by Ben's 85. E2 has a drop-down too: pick South there and F2 shows the South total instead. A list for the input and a formula that reads it is the most common use of a drop-down.
How to create a drop-down list, step by step
- Select the cells that should get the list, for example B2:B6.
- Go to Data > Data Validation (Data Tools group). On Windows the key sequence is Alt, A, V, V.
- On the Settings tab, set Allow to List.
- In Source, either type the items separated by commas,
North,South,East,West, or click in the box and select the range with the items on the sheet, which writes=$F$2:$F$5. - Keep In-cell dropdown ticked (without it there is no arrow, only the check).
- Press OK.
To open the list from the keyboard, select the cell and press Alt+Down Arrow (Windows) or Option+Down Arrow (Mac). In Excel for Microsoft 365, typing the first letters in the cell narrows the list to the matching items.
Two optional tabs in the same dialog: Input Message shows a tip when the cell is selected, and Error Alert sets what happens when someone types a value that is not in the list. With the style Stop (the default) the entry is refused; with Warning or Information it is allowed after a prompt. Untick Show error alert after invalid data is entered to let people type anything and still offer the list.
Typed items are separated by the list separator of the computer's regional settings. In most locales that use a decimal comma (France, Germany, Spain, Italy), that is a semicolon: Nord;Sud;Est;Ouest.
Drop-down list from a range of cells
A list typed into the dialog is hidden from view and has to be edited there. A list in cells is easier to maintain: change a cell and every drop-down that uses it changes. Here the regions are in E2:E5 and the drop-down on B2:B6 uses that range as its source.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Regions | |
| 2 | Ana | North | 120 | North | |
| 3 | Ben | South | 85 | South | |
| 4 | Cara | North | 240 | East | |
| 5 | Dan | East | 60 | West | |
| 6 | Eve | South | 150 |
Change E5 from West to Central, then open any arrow in column B: the list offers Central instead of West. The values already chosen in column B do not change.
To use a range on another sheet, which is the usual way to keep lists out of sight, type the sheet name in Source: =Lists!$A$2:$A$5. To have the list grow when you add an item at the bottom, turn the items into a table first (select them, Insert > Table) and then select the table's column as the source: the reference expands with the table.
A dynamic drop-down list with UNIQUE
When the items should come from the data itself (every region that appears in a column, once each), build the list with a formula and point the drop-down at the result. =SORT(UNIQUE(B2:B8)) in G2 spills the distinct regions in alphabetical order.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Pick | Sales | Regions | |
| 2 | Ana | North | 120 | South | 235 | East | |
| 3 | Ben | South | 85 | North | |||
| 4 | Cara | North | 240 | South | |||
| 5 | Dan | East | 60 | West | |||
| 6 | Eve | South | 150 | ||||
| 7 | Fay | West | 95 | ||||
| 8 | Gus | East | 110 |
G2 spills East, North, South, West, and the drop-down in E2 offers those four. Change B7 to Central and Central appears in both the spill and the list.
In Excel, set the Source of the drop-down to =$G$2#. The # after a cell means "the whole spill of this formula", so the list is always exactly as long as the result, with no empty rows at the end. The spill reference needs Excel 365 or 2021; the source cell can be on another sheet (=Lists!$A$2#). If the data column has empty cells, UNIQUE returns a 0 for them; leave them out with =SORT(UNIQUE(FILTER(B2:B100,B2:B100<>""))). The UNIQUE page covers the function in detail.
Dependent drop-down lists
A dependent list changes with the choice in another cell: pick Fruit in A2, and B2 offers only fruit. In Excel 365 and 2021, a FILTER formula builds the second list: =FILTER(E2:E8,D2:D8=A2) returns the items whose category matches A2, and the drop-down in B2 uses that spill as its source.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Category | Item | Category | Item | Items | ||
| 2 | Fruit | Pear | Fruit | Apple | Apple | ||
| 3 | Fruit | Pear | Pear | ||||
| 4 | Vegetable | Carrot | Kiwi | ||||
| 5 | Vegetable | Leek | |||||
| 6 | Bakery | Bread | |||||
| 7 | Fruit | Kiwi | |||||
| 8 | Bakery | Bagel |
With Fruit in A2, G2 spills Apple, Pear and Kiwi, and those are the choices in B2. Pick Bakery in A2: G2 changes to Bread and Bagel. B2 still says Pear until you pick again, since a drop-down never changes a value already in the cell. In Excel the source of B2 is =$G$2#.
In older Excel versions, the classic way uses INDIRECT and named ranges:
- Put each category's items in its own column, with the category name as the header: Fruit in one column, Vegetable in the next.
- Select each column of items and name it after its category in the Name Box (left of the formula bar):
Fruit,Vegetable,Bakery. - Give A2 a drop-down with the source
Fruit,Vegetable,Bakery. - Give B2 a drop-down with the source
=INDIRECT(A2). INDIRECT turns the text in A2 into a reference to the range with that name.
The names must match the category text exactly and cannot contain spaces (use Dairy_Products, or =INDIRECT(SUBSTITUTE(A2," ","_")) in the source). More on INDIRECT on the INDIRECT page.
Look up the value of the chosen item
A drop-down is often the input of an order form or a quote: the user picks a product and a lookup fills in its price.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Product | Price | ||
| 2 | Order | Pear | Apple | $1.20 | ||
| 3 | Pear | $1.50 | ||||
| 4 | Carrot | $0.80 | ||||
| 5 | Bread | $2.40 | ||||
| 6 | Milk | $1.10 |
Your turn: Return the price of the product chosen in B2 from the table in E:F, in C2.
With Pear chosen, the answer is $1.50. Pick another product in B2 and the price follows. =XLOOKUP(B2,E2:E6,F2:F6) works too; see VLOOKUP for the arguments.
Colour a cell by the chosen item
To colour the cell by what was picked (green for Done, red for Late), add a conditional formatting rule to the same cells: select B2:B6, go to Home > Conditional Formatting > Highlight Cells Rules > Equal To, type Late and pick a format. To colour the whole row, select A2:B6 and use New Rule > Use a formula to determine which cells to format with =$B2="Late".
| A | B | |
|---|---|---|
| 1 | Task | Status |
| 2 | Quote | Done |
| 3 | Invoice | Late |
| 4 | Order | Open |
| 5 | Report | Late |
| 6 | Survey | Done |
B3 and B5 are highlighted. Pick Late in B4 and it is highlighted too; pick Done in B3 and the highlight goes. The conditional formatting page covers rules in detail.
Why a drop-down list is not working
- In-cell dropdown is unticked in Data > Data Validation. The list still restricts entries, but there is no arrow.
- The arrow only shows on the selected cell. Nothing in the grid marks the other cells that have a list; to find them, use Home > Find & Select > Data Validation.
- The Source range has blank cells, so the list shows empty lines. Select only the filled cells, or use a spilled source (
=$G$2#), which has no blanks. - Items typed with the wrong separator:
North;Southin an Excel that uses commas becomes one item calledNorth;South. - A drop-down holds one value. Picking a second item replaces the first; selecting several items in one cell needs a VBA macro.
To copy a drop-down to other cells without copying the value, copy the cell, then use Home > Paste > Paste Special > Validation. To remove one, select the cells and choose Data > Data Validation > Clear All.
Frequently Asked Questions
How do I create a drop-down list in Excel?
Select the cells, go to Data > Data Validation, set Allow to List, type the items in Source separated by commas (North,South,East) or select the range that holds them (=$F$2:$F$5), and press OK.
How do I edit a drop-down list in Excel?
Select a cell with the list, open Data > Data Validation and change the Source box. Tick Apply these changes to all other cells with the same settings to update every copy. If the source is a range, editing the cells in that range changes the list without opening the dialog.
How do I remove a drop-down list in Excel?
Select the cells, go to Data > Data Validation and click Clear All, then OK. The values already chosen stay in the cells; only the arrow and the restriction go away.
How do I make a drop-down list from another sheet?
Type the reference with the sheet name in Source: =Lists!$A$2:$A$6, or click the other sheet and select the range while the Source box is active. A named range (Formulas > Define Name) also works: =Regions.
How do I make a drop-down list that updates automatically?
Point it at a spilled formula: put =SORT(UNIQUE(FILTER(B2:B100,B2:B100<>""))) in a helper cell such as H2 and use =$H$2# as the Source. New values in column B then appear in the list at once, and FILTER keeps empty rows out of it. This needs Excel 365 or 2021.
