Expert guide
Dependent Drop-Down List in Excel
Pick a category, see only its items: two methods that work, and how to keep entries consistent
Call us at +1 (385) 386-3860
Short answer: in Microsoft 365, store parent and child values in a two-column table, use =SORT(FILTER(tblList[Child], tblList[Parent]=A2)) in a helper cell, and set the dependent cell's Data Validation list source to that helper's spill range (=$H$2#). In older Excel, create a named range per parent and use =INDIRECT(A2) as the validation source. Add a small macro to clear the child when the parent changes. For full entry forms built this way, see custom Excel spreadsheets.
Order sheets, job logs, expense forms and inspection checklists all have the same problem: users type values that do not match, or pick a product from the wrong category. Dependent drop-downs guide them to valid combinations: choose the department, then only that department's cost codes appear.
Set Up the Source Table
| Parent (Category) | Child (Product) |
|---|---|
| Plumbing | PVC pipe 40mm |
| Plumbing | Copper elbow 15mm |
| Electrical | Cable 2.5mm |
| Electrical | Double socket |
| Timber | Pine 90x45 |
Format it as an Excel Table named tblList with columns Parent and Child. New products are added as new rows, and both methods below pick them up.
Method 1: FILTER (Microsoft 365, Excel 2021+)
Parent list: in a helper cell (say J2) enter =SORT(UNIQUE(tblList[Parent])). Set A2's validation to List with source =$J$2#.
Child list: in a helper cell (say H2) enter =SORT(FILTER(tblList[Child], tblList[Parent]=$A$2, "")).
Set B2's validation to List with source =$H$2#.
Hide the helper columns or move them to a Lists sheet.
J2 (parents): =SORT(UNIQUE(tblList[Parent])) H2 (children): =SORT(FILTER(tblList[Child], tblList[Parent] = Entry!$A$2, "")) A2 validation: =Lists!$J$2# B2 validation: =Lists!$H$2#
This is the cleanest method for a single entry form: no named ranges to maintain, lists sort themselves, and new items appear immediately.
Method 2: INDIRECT (Any Version, Works Per Row)
Lay out each category's items in its own column on a Lists sheet, headed by the category name.
Select each column of items and create a named range with the category name (Formulas → Create from Selection → Top row).
Set column A's validation to the list of categories.
Set column B's validation to =INDIRECT(A2) with a relative reference, then copy down the rows.
Names cannot contain spaces. For "Garden Supplies" name the range Garden_Supplies and use: =INDIRECT(SUBSTITUTE(A2, " ", "_"))
Because the reference is relative, each row's B cell looks at its own A cell, so the method works for long lists of entries.
Choosing a Method
| Need | FILTER method | INDIRECT method |
|---|---|---|
| Excel 2019 or older | No | Yes |
| Single entry form | Best | Fine |
| Many rows of entries | Needs helpers per row or VBA | Best |
| Items maintained in one table | Yes | Columns per category |
| Auto-sorted, deduplicated | Yes | Manual |
Clear the Child When the Parent Changes
' In the worksheet's code module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Intersect(Target, Me.Range("A2:A500")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In Intersect(Target, Me.Range("A2:A500"))
c.Offset(0, 1).ClearContents ' clear the product next to it
Next c
Application.EnableEvents = True
End SubWithout this, a user can pick Plumbing → PVC pipe, then change the category to Electrical, leaving an invalid pair. If macros are not allowed, add a check column: =IF(COUNTIFS(tblList[Parent],A2,tblList[Child],B2)=0,"Check","") and highlight it.
Three Levels (Category → Subcategory → Item)
tblList columns: Category | Subcategory | Item Subcategory list: =SORT(UNIQUE(FILTER(tblList[Subcategory], tblList[Category] = A2))) Item list: =SORT(FILTER(tblList[Item], (tblList[Category] = A2) * (tblList[Subcategory] = B2)))
When Drop-Downs Are Not Enough
Long lists (hundreds of products) are hard to scroll; a searchable form, or a lookup by code with XLOOKUP, works better. If several people enter data into the same workbook at once, or the lists come from another system, a proper entry form or a small database is often the right step; see Excel reports and forms and converting Excel to Access. For the functions behind the FILTER method, see Excel dynamic arrays.
Making Many Rows Work With the FILTER Method
The FILTER helper approach produces one list for one parent cell. For a table of entries where every row needs its own dependent list, use one of these patterns:
Helper block per row (small tables)
Put a TRANSPOSE(FILTER(...)) in a hidden area on the same row and point that row's validation at it. Simple, but it adds many helper cells.
Selection-change macro (any size)
A Worksheet_SelectionChange macro writes the filtered list for the active row into one helper range just before the user opens the drop-down. One helper serves every row.
INDIRECT with named ranges
Still the simplest per-row method if maintaining one named range per category is acceptable.
For entry tables used all day by several people, a proper entry form with combo boxes, in Excel or in an Access front end, is often less fragile than heavily engineered validation lists.
FAQ
Dependent drop-down FAQ
FILTER, INDIRECT and keeping entries valid