How to create a drop-down list in Excel
Last updated
A drop-down list lets people pick a value instead of typing it. Select the cells, go to Data > Data Validation, set Allow to List and type the items in Source, such as Yes,No, or point to a range such as =$E$2:$E$4. Excel then refuses anything that isn’t in the list.
Where to find it in Excel
Data > Data Validation > Allow: List > Source
- Allow
- The kind of rule. Choose List for a drop-down. The other types limit whole numbers, decimals, dates, times or text length.
- Source
- The items: typed and separated by your list separator (
Yes,No, orYes;Noin Excel versions that use semicolons), or a reference with an equals sign,=$E$2:$E$4. A reference must be a single row or column. - In-cell dropdown
- Shows the arrow next to the selected cell. Untick it to keep the restriction without the arrow.
- Ignore blank
- Lets the cell stay empty. Leave it ticked unless the cell has to be filled.
- Error Alert
- A second tab of the dialog. The default Stop style refuses values that aren’t in the list; Warning and Information only warn. You can write your own title and message.
Practise drop-down lists here
Select cells and click Data Validation… above the formula bar, as you would in Excel. Then pick values from the arrow that appears in the cell.
A Yes/No list typed by hand
Select B2:B7, open Data Validation, set Allow to List and type the Source Yes,No. Then try the arrow that appears in the cells.
| Guest | Attending? |
| Olivia Reed | |
| James Carter | |
| Sofia Patel | |
| Liam Brooks | |
| Emma Hughes | |
| Noah Bennett |
How to create a drop-down list, step by step
- Select the cells that should get the list, for example
C2:C9. - On the Data tab, in the Data Tools group, click Data Validation.
- On the Settings tab, set Allow to List.
- In Source, type the items separated by commas, or type or select a range such as
=$E$2:$E$4. Keep In-cell dropdown ticked and click OK.
Drop-down list examples
Yes or No
Yes,No
The simplest typed list. If your Excel separates function arguments with semicolons, write Yes;No.
Items from a range
=$E$2:$E$4
The list shows whatever is in E2:E4. Change one of those cells and every drop-down that uses them updates. The $ keeps the range fixed for every cell of the selection.
Items on another sheet
=Lists!$A$2:$A$10
Keep the items on a separate sheet. Type the reference, or click the sheet tab and select the cells while the Source box is active.
A list that grows
=Statuses
Statuses is a name defined for a table column (Formulas > Name Manager). When you add a row to the table, the new item appears in the drop-down.
Common problems and how to fix them
| Problem | Cause | Fix |
|---|---|---|
| “This value doesn’t match the data validation restrictions…” | The value typed isn’t in the list, often because of a typo or an extra space. | Pick the value from the arrow, or add it to the source list. |
| Wrong items lower down the column | The source range is relative, so it shifts one row for each cell: C3 offers E3:E5 instead of E2:E4. | Lock the range with $: =$E$2:$E$4. |
| No arrow in the cell | In-cell dropdown is unticked, or the cell isn’t selected: the arrow only shows on the active cell. | Select the cell. If there’s still no arrow, tick In-cell dropdown in Data Validation. |
| One item with commas in it | The items were separated with commas, but your Excel uses semicolons as its list separator (or the other way round). | Use the separator your Excel uses between function arguments. |
| “The source currently evaluates to an error.” | The Source reference points to a deleted range (#REF!) or to a name that doesn’t exist. | Fix the reference or define the name, then open Data Validation again. |
Typed list or range?
A typed list (Yes,No) is quick for a few fixed items, but you have to edit every rule to change it, and the Source box holds at most 255 characters. A range source (=$E$2:$E$4) keeps the items in cells, so one change updates every drop-down, and it can hold long lists. For lists that keep growing, point to a named table column.
Frequently asked questions
How do I remove a drop-down list?
Select the cells, open Data > Data Validation, click Clear All, then OK. The values already in the cells stay.
How do I add or change the items?
For a typed list, open Data Validation again and edit Source. For a range source, edit the cells of the range. To add items, extend the range or use a table.
Can people still type in the cell?
Yes, but with the default Stop alert Excel only accepts values from the list, in any case. Pasting over the cell can remove the rule, though.
How do I make a list depend on another cell?
Give each sub-list a name that matches an item of the first list, then use =INDIRECT(B2) as the Source of the second list. This is called a dependent drop-down list.
Keep practising
All 5 drop-down list exercises, with explanations and progress tracking.
Open the drop-down list exercises