Cascading (Dependent) Data Validation Lists

Pick a country in one cell and the next cell should offer only that country’s cities. Excel has no built-in “dependent dropdown”, but named ranges plus INDIRECT get you there in a few minutes.

Set up the source lists

On a separate sheet, put each parent value at the top of its own column and its children beneath. Then select each column of children and give it a name identical to the parent value — select the cities under “France” and name that range France.

The fastest way: select the whole block including the header row, then Formulas → Create from Selection → Top row. Excel creates one named range per column, named after its header.

Names must match the parent values exactly, and Excel names cannot contain spaces. If your parent values contain spaces, replace them with underscores in the names and use SUBSTITUTE in the formula below.

Build the two dropdowns

1. Select the first cell (say A2), then Data → Data Validation → Allow: List, and point Source at your list of parent values.
2. Select the second cell (B2), open Data Validation again, choose List, and enter as the Source:

=INDIRECT($A2)

Note the mixed reference: the column is locked with $ but the row is not, so the rule fills down correctly. With underscores in your names, use =INDIRECT(SUBSTITUTE($A2," ","_")).

Excel warns that the source currently evaluates to an error if A2 is still empty. Accept it — it resolves as soon as a parent value is chosen.

The trap: stale child values

Choose France, choose Paris, then change the first cell to Germany. Excel does not clear the second cell — it still reads Paris, and it is now invalid data that no validation rule will flag.

Two ways to handle it. Use Data → Data Validation → Circle Invalid Data to make offenders visible before you rely on the sheet. Or clear the child cell automatically with a short worksheet event macro:

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

Adding a macro means saving as .xlsm, which is a real cost if the file is shared widely. For most workbooks, Circle Invalid Data plus a note to the user is enough.

Lists that grow: the OFFSET problem

Named ranges are fixed. Add a fourteenth city under France and the dropdown still offers thirteen, because the name still points at the original block.

The traditional fix is a dynamic name built with OFFSET and COUNTA:

=OFFSET(Lists!$A$2, 0, 0, COUNTA(Lists!$A:$A)-1, 1)

It works, with two drawbacks. OFFSET is volatile — it recalculates on every change anywhere in the workbook, and a few dozen of these make a file noticeably sluggish. And COUNTA breaks the moment a gap appears in the column.

INDEX gives the same result without volatility:

=Lists!$A$2:INDEX(Lists!$A:$A, COUNTA(Lists!$A:$A))

The better answer: Excel Tables

Convert each source column to a Table with Ctrl+T and it resizes itself as rows are added. No OFFSET, no COUNTA, no volatility.

One wrinkle: Data Validation will not accept a structured reference such as =Table1[Cities] typed directly into the Source box. Define a normal named range that points at the table column, then reference that name. The validation rule sees an ordinary name; the name tracks the table.

A third level

Country, then region, then city works the same way, one level at a time: name each region’s city list after the region, and set the third dropdown’s source to =INDIRECT($B2).

The constraint is that every name in the workbook must be unique. Two countries with a region called “North” cannot both have a range named North. Prefix them — FR_North, DE_North — and build the name in the formula:

=INDIRECT($A2 & "_" & $B2)

Beyond three levels this becomes hard to maintain, and the naming scheme is where it usually falls apart. At that point a lookup table plus a multi-criteria lookup, or a small PivotTable-driven selector, is easier to live with than a fourth tier of names.

Data validation, named ranges and INDIRECT all appear in Microsoft’s MOS Excel Expert objectives — see the MO-211 exam guide.