How to Create a Drop-Down List in Excel (Every Type + Fixes)
The two-minute basic list is easy. The real questions are the advanced ones: how do I make a list that grows on its own, one that changes based on another cell, one you can search, or one that accepts more than one answer? This guide covers all seven, with exact clicks, the right formula for your Excel version, and honest tradeoffs.
Quick Answer
To create a drop-down list in Excel, select your cell, go to Data > Data Validation, set Allow to List, and type your items separated by commas (or point Source at a range). Click OK. That is the basic list.
For the advanced versions: use an Excel Table so the list auto-grows, FILTER (365) or INDIRECT (older) for dependent lists, nothing at all for search in Microsoft 365 (it is built in), and a short VBA macro for multi-select. Jump to the decision tree below to pick your path.
Which Method Do I Need?
Most people land here because the basic list is not enough. Use this decision tree to jump straight to the right section.
| What you want | Best method | Excel version |
|---|---|---|
| A short fixed list (Yes/No/Maybe) | Basic Data Validation, typed items | Any |
| A list that grows when I add rows | Excel Table + INDIRECT source | Any |
| Second list depends on first (Country then City) | FILTER + spill reference | 365 / 2021 |
| Dependent list on older Excel | Named ranges + INDIRECT | 2016 / 2019 |
| Type to filter a long list | Nothing, it is built in | 365 / web |
| Search on older Excel | ComboBox + helper formulas + VBA | 2016 / 2019 / 2021 |
| Pick several items in one cell | List + Worksheet_Change VBA | Any (desktop) |
| Color each choice differently | List + Conditional Formatting | Any |
1. Basic Drop-Down List (Data Validation)
This is the foundation for every other method on the page. Learn it once and the rest are variations.
Step-by-Step
- Select the cell or range where you want the drop-down.
- Go to the Data tab on the ribbon.
- Click Data Validation in the Data Tools group.
- On the Settings tab, set Allow to List.
- In the Source box, either:
- Type items separated by commas:
Low, Medium, High, or - Select a range on the sheet:
=$A$2:$A$10
- Type items separated by commas:
- Make sure In-cell dropdown is checked.
- Click OK. A small arrow now appears in the cell.
Pro Tip: Referencing a range beats typing items directly. When you type items into the Source box, changing them later means re-opening the dialog. When you reference a range, you just edit the cells. For a source on another sheet, give the range a name first (Formulas > Define Name), then use =YourRangeName.
2. Auto-Expanding List From a Table
The most common frustration with a basic list: you add a new item to your source, but the drop-down does not include it. The fix is to base the list on an Excel Table, which grows automatically as you add rows.
Step-by-Step
- Click any cell in your list of items.
- Press Ctrl + T to convert it to a Table. Confirm the range and check My table has headers if it does. Click OK.
- With the Table selected, look at the Table Design tab and rename the Table to something clear, like
Products. - Select the cell where you want the drop-down.
- Open Data > Data Validation, set Allow to List.
- In the Source box, enter:
=INDIRECT("Products[Item]") - Click OK.
Now when you add a row to the Table, the new item shows up in the drop-down with zero extra work.
Why INDIRECT here? Data Validation will not accept a structured Table reference (=Products[Item]) directly. Wrapping it in INDIRECT("...") as text is the standard workaround. On Excel 365 you can instead point at a spill range from a formula, which we use in the next section.
3. Dependent (Cascading) Drop-Downs
A dependent list is a second drop-down whose options change based on the first. Pick France in column A and column B shows only French cities. There are two ways to build this, and the right one depends entirely on your Excel version.
Modern method: FILTER (Excel 365 and 2021)
This is the cleaner approach. You do not need a named range for each category. Say your data has a Category column and an Item column, and your first drop-down (the category) is in cell D2.
- Pick an empty helper cell, for example
G2. - Enter:
=FILTER(Item_Range, Category_Range = D2)
The matching items spill down automatically from G2. - Select the cell for your second (dependent) drop-down, say
E2. - Open Data > Data Validation, set Allow to List.
- In Source, reference the spill range with the # operator:
=$G$2# - Click OK.
Change the category in D2 and the second drop-down instantly rebuilds itself. Add a new item to your source data and it appears with no maintenance.
Legacy method: INDIRECT + named ranges (Excel 2016 / 2019)
If you do not have FILTER, use the classic INDIRECT trick. It relies on named ranges whose names exactly match your first-level choices.
- Lay out each category as its own column of items, with the category name in the header row.
- Select all of it (headers plus items), then go to Formulas > Create from Selection, check Top row, and click OK. This makes one named range per category.
- Set up your first drop-down (the category picker) as a basic list in
D2. - Select the dependent cell
E2, open Data Validation, set Allow to List. - In Source, enter:
=INDIRECT(D2) - Click OK. If Excel warns the source evaluates to an error, that is normal when D2 is empty. Click Yes.
The INDIRECT gotcha: named ranges cannot contain spaces, so a category like New York breaks. Either remove the space in both the data and the name, or wrap the reference with SUBSTITUTE(D2," ","_") and name the ranges with underscores. INDIRECT is also volatile, so it can slow very large workbooks. If you have 365, prefer FILTER.
| Factor | FILTER (modern) | INDIRECT (legacy) |
|---|---|---|
| Excel version | 365 / 2021 | 2016 and up |
| Named ranges needed | No | One per category |
| Handles spaces in names | Yes | No (needs a workaround) |
| Auto-updates with new data | Yes | Only if named ranges are dynamic |
| Performance on big files | Fast | Slower (volatile) |
4. Searchable Drop-Down List
Good news for Microsoft 365 users: search is already built in. In Microsoft 365 and Excel for the web, every Data Validation drop-down is searchable by default. Just click the cell and start typing. Excel filters the list to matching items in real time, matching text anywhere in the item, not only at the start. There is nothing to set up.
A few things to know about the native 365 search: it does not sort the list (items keep their original order), it does not remove duplicates, and it automatically hides trailing blank cells from your source. If you are on 365, you are done. If you are on Excel 2019 or 2021, read on.
Legacy method: ComboBox + helper formulas + VBA
Older Excel has no native search, so you build a search box from a form control. This is more involved. Here is the shape of it.
- Enable the Developer tab (right-click the ribbon > Customize the Ribbon > check Developer).
- Go to Developer > Insert > ActiveX Controls > Combo Box and draw it over the cell.
- Right-click the ComboBox > Properties and set ListFillRange to a named range (we will define it next), LinkedCell to your target cell, and MatchEntry to
2 - fmMatchEntryNone. - Build three helper columns next to your source list:
- Match flag:
=--ISNUMBER(SEARCH($B$3,E3)) - Running count:
=IF(F3=1,COUNTIF($F$3:F3,1),"") - Stacked results:
=IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),"")
- Match flag:
- Create a dynamic named range (Formulas > Name Manager) called
DropDownListthat points at only the filled result cells. - Right-click the sheet tab > View Code and add a short macro so the list refreshes and reopens as you type:
ComboBox refresh macro
Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown
End SubSave the file as .xlsm so the macro survives. This works, but it is fiddly and does not scale to many cells. Honestly, if search matters to your team, upgrading to Microsoft 365 is the better answer than maintaining this per cell.
5. Multi-Select Drop-Down
By default, choosing a new item replaces the old one. There is no native multi-select drop-down in Excel, not even in 365. To let a cell hold several picks (like Red, Blue, Green), you add a small VBA macro on top of a normal list.
Step-by-Step
- First create a normal Data Validation List on the target cells (Section 1).
- Right-click the sheet tab and choose View Code.
- Paste the macro below. It appends each choice with a comma instead of overwriting.
- Close the editor and save as .xlsm.
Worksheet_Change macro for multi-select (adjust the column in the Address check)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oldVal As String, newVal As String
If Target.Count > 1 Then Exit Sub
On Error Resume Next
If Target.Column = 2 Then ' column B; change as needed
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal <> "" And newVal <> "" Then
If InStr(oldVal, newVal) = 0 Then
Target.Value = oldVal & ", " & newVal
Else
Target.Value = oldVal
End If
End If
Application.EnableEvents = True
End If
On Error GoTo 0
End SubHeads-up: VBA macros do not run in Excel for the web or in the Mac App Store build with restricted scripting, and picking a value that already exists in the cell will be skipped by the code above (so you cannot add the same item twice). Test on a copy before rolling it out to a shared file.
6. Color-Coded Drop-Down
A status list reads much faster when Done is green and Blocked is red. Excel does not color the drop-down itself, but you can color the cell based on the choice with Conditional Formatting.
Step-by-Step
- Create your normal drop-down list first (Section 1).
- Select the cells that contain the drop-downs.
- Go to Home > Conditional Formatting > New Rule.
- Choose Format only cells that contain.
- Set the rule to Specific Text > containing > type the value, for example
Done. - Click Format, pick a fill color on the Fill tab, and click OK.
- Repeat one rule per choice (one for Done, one for In Progress, one for Blocked).
Now the cell fill updates the moment someone picks a value from the list.
7. How to Edit, Add To, or Remove a Drop-Down
Add an item to an existing list
- If the source is a range or Table: just type the new item into the source cells. A Table updates the drop-down automatically; a plain range updates as long as the item falls inside the referenced range.
- If items were typed into the Source box: select the cell, open Data > Data Validation, and add the item in the Source box (for example change
Low, Medium, HightoLow, Medium, High, Urgent).
Apply the change to every cell at once
In the Data Validation dialog, check Apply these changes to all other cells with the same settings before clicking OK. This pushes your edit to every matching drop-down on the sheet.
Remove a drop-down
- Select the cell or cells with the drop-down.
- Open Data > Data Validation.
- Click Clear All, then OK. The arrow and the rule are gone; any values already in the cells stay.
Common Errors & Fixes
No arrow shows up
- Re-open Data Validation and confirm In-cell dropdown is checked.
- The arrow only appears when the cell is selected, so click the cell first.
"The Source currently evaluates to an error"
- Usually a dependent list where the first cell is empty (INDIRECT has nothing to point at). Click Yes to proceed; it works once a category is chosen.
- Check for spaces in named ranges (see the INDIRECT gotcha above).
List from another sheet is rejected
- Older Excel dislikes direct cross-sheet references in Source. Name the range (Formulas > Define Name) and use
=RangeNameinstead.
New items do not appear
- Your source is a fixed range, not a Table. Convert it with Ctrl + T and use the INDIRECT Table reference from Section 2.
Users can still type invalid values
- Data Validation only warns by default. Open the Error Alert tab, keep Show error alert checked, and set Style to Stop to block bad entries.
Macro-based list stopped working
- The file was saved as .xlsx, which drops VBA. Re-save as .xlsm and enable macros in the Trust Center.
Frequently Asked Questions
How do I create a simple drop-down list in Excel?
Select the cell, go to Data > Data Validation, set Allow to List, and either type comma-separated items in the Source box or point it to a range like =$A$2:$A$10. Click OK and a drop-down arrow appears in the cell.
How do I make a dependent (cascading) drop-down list?
In Excel 365 or 2021, put =FILTER(items, category_column = first_cell) in a helper cell so the matches spill down, then set the second list Source to that spill range with the # operator, like =$G$2#. In older Excel, create one named range per category and use =INDIRECT(first_cell) as the second list source.
Is the Excel drop-down list searchable?
In Microsoft 365 and Excel for the web, yes, and by default. Click the cell and start typing to filter the list. In Excel 2019 or 2021 there is no native search, so you need a ComboBox with helper formulas and a small VBA macro.
How do I select multiple items from one drop-down?
Excel has no built-in multi-select. Create a normal Data Validation list, then add a Worksheet_Change VBA macro that appends each pick to the cell instead of replacing it. Save the file as .xlsm.
How do I make the drop-down expand automatically when I add items?
Convert your source list to an Excel Table with Ctrl + T, then set the Data Validation Source to =INDIRECT("TableName[ColumnName]"). New rows in the Table appear in the drop-down automatically.
How do I edit or remove a drop-down list?
To edit, select the cell, open Data > Data Validation, and change the Source. To remove, open the same dialog and click Clear All. Any values already typed in the cells remain.
Why is my drop-down list not working?
Common causes: the In-cell dropdown box is unchecked, the source range is on another sheet without a named range, or the sheet is protected. Re-open Data Validation, confirm In-cell dropdown is checked, and use a named range for cross-sheet sources.
Summary
Every Excel drop-down starts from the same place: Data > Data Validation > List. From there, pick the variant your job needs:
- Auto-expanding? Base it on a Table with an INDIRECT reference.
- Dependent? FILTER and a spill reference on 365, or named ranges plus INDIRECT on older Excel.
- Searchable? Free on Microsoft 365. A ComboBox plus VBA on older versions.
- Multi-select? A short Worksheet_Change macro, saved as .xlsm.
- Color-coded? Layer Conditional Formatting rules on top of the list.
Start simple and add only the complexity your use case actually requires.
Turn Excel How-Tos Into Guided Demos
Written steps are fine, but people learn faster when they can click through the exact flow. Deckoholic turns any workflow into an interactive demo or a narrated video, so your team follows along instead of guessing. See how it compares to the best interactive demo software.
Try Deckoholic FreeBuilding a product demo instead of a spreadsheet?
If you document software for a living, see our guide on how to create a product demo and our roundup of Navattic alternatives. Or head back to the Deckoholic homepage to try it.
