Dependent Dropdown Lists in Excel (Two Methods That Actually Work)

A dependent (cascading) dropdown shows only the options that make sense: choose Category = Displays and the second list offers only monitors. It prevents typing errors and makes forms feel professional.

The data

Method 1: FILTER spill range (Microsoft 365 / Excel 2021+)

1. Put the list above in a table on a sheet called Lists (columns A:B).
2. First dropdown in Form!B2: Data Validation → List → Source =UNIQUE(Lists!$A$2:$A$100) won’t work directly, so put =SORT(UNIQUE(Lists!A2:A100)) in Lists!D2 and use Source =Lists!$D$2#.
3. In Lists!E2 enter =FILTER(Lists!B2:B100, Lists!A2:A100=Form!B2, "—"). It spills the matching products.
4. Second dropdown in Form!C2: Data Validation → List → Source =Lists!$E$2#.

The # after a cell refers to its whole spill range, so the list grows and shrinks automatically when you add products.

Method 2: INDIRECT with named ranges (every version)

1. Put each category’s products in its own column with the category as header: Accessories, Displays, Video.
2. Select the whole block → Formulas → Create from Selection → Top row. Excel creates a name for each column.
3. First dropdown (B2): List → Source = the header row.
4. Second dropdown (C2): List → Source =INDIRECT(B2). Copy it down the column.

Clear the second choice when the first changes

Changing Category leaves an old, invalid Product behind. A short sheet macro fixes it (right-click the sheet tab → View Code):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("B2:B500")) Is Nothing Then
        Application.EnableEvents = False
        Target.Offset(0, 1).ClearContents
        Application.EnableEvents = True
    End If
End Sub

Which method?