How to Create Dependent Drop-Down Lists in Excel

Introduction

You have a form where someone picks a region, then picks a city. Right now both dropdowns show every option regardless of context, so someone can select "Northeast" and then pick "Los Angeles." A dependent drop-down list makes the second dropdown show only the cities that belong to the selected region.

The Setup

Here's a reference table on a sheet called "Lists" with three regions and their cities:

	A	B	C
3	Northeast	Southeast	West
4	Boston	Atlanta	Los Angeles
5	New York	Miami	San Francisco
6	Philadelphia	Charlotte	Seattle

Each column holds the valid cities for one region. The dependent dropdown will pull from the correct column based on which region the user picks.

Creating Named Ranges

Each city list needs a named range whose name matches the column header exactly.

1. Select A4:A6. Go to the Name Box (the field to the left of the formula bar), type Northeast, and press Enter.
2. Select B4:B6. Type Southeast in the Name Box and press Enter.
3. Select C4:C6. Type West in the Name Box and press Enter.

You now have three named ranges. When a formula references "Northeast," Excel returns the list Boston, New York, Philadelphia. When it references "Southeast," it returns Atlanta, Miami, Charlotte.

Key takeaway: The named range names must match the primary dropdown values exactly. If the dropdown says "Northeast," the named range must also be called "Northeast" for INDIRECT to resolve it.

The Primary Drop-Down

Set up the first dropdown so users can pick a region. Suppose the form lives on a separate sheet with the region selector in cell B3 and the city selector in B4.

1. Select cell B3.
2. Go to Data > Data Validation.
3. Under Allow, choose List.
4. In the Source field, type Northeast,Southeast,West.
5. Click OK.

Cell B3 now shows a dropdown with the three region names. Selecting one sets the value that the dependent dropdown will read.

The Dependent Drop-Down with INDIRECT

Now connect the city dropdown to the region selection using INDIRECT. The idea is simple: whatever text sits in B3, use it as a named range reference.

1. Select cell B4.
2. Go to Data > Data Validation.
3. Under Allow, choose List.
4. In the Source field, enter =INDIRECT(B3).
5. Click OK.

When B3 contains "Northeast," INDIRECT reads that text and resolves it to the named range called Northeast. The dropdown in B4 shows Boston, New York, and Philadelphia. Switch B3 to "West" and B4 updates to Los Angeles, San Francisco, and Seattle.

The connection works because the text in B3 matches a named range name. INDIRECT converts that text into a live reference, and data validation uses that reference as its list source.

Handling Category Names with Spaces

Named ranges cannot contain spaces. If a region were called "East Coast" instead of a single word, you couldn't create a named range with that exact name. Excel would reject it.

The fix uses underscores in the named range and SUBSTITUTE in the formula:

1. Name the range East_Coast (underscore instead of space).
2. In the primary dropdown source, keep the display value as East Coast (with space) so it reads naturally.
3. Change the dependent dropdown formula to =INDIRECT(SUBSTITUTE(B3," ","_")).

SUBSTITUTE replaces every space in B3's value with an underscore before INDIRECT resolves it. "East Coast" becomes "East_Coast," which matches the named range. Regions without spaces pass through unchanged because there's nothing to replace.

Use this SUBSTITUTE version from the start, even if none of your current categories have spaces. It costs nothing and prevents breakage if someone adds a multi-word category later.

Clearing the Dependent Cell

One thing to watch: when someone changes the primary dropdown, the dependent cell keeps its old value. If a user picks "Northeast" and then "Boston," then switches the region to "West," the city cell still displays "Boston" even though it's no longer valid. The dropdown won't show an error until the user opens it again.

Two ways to handle this:

• Manual approach. Train users to clear the city cell after changing the region. Simple, but relies on discipline.
• VBA approach. A short Worksheet_Change macro can detect when the region cell changes and clear the city cell automatically. Right-click the sheet tab, select View Code, and add:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$3" Then
        Range("B4").ClearContents
    End If
End Sub

The macro fires whenever B3 changes and blanks out B4. This keeps the form consistent without relying on the user to remember.

Adding More Items

When a region gains new cities, extend the named range:

1. Open the Name Manager (Ctrl + F3).
2. Select the range you want to extend (for example, "Northeast").
3. Update the "Refers to" field from =Lists!$A$4:$A$6 to =Lists!$A$4:$A$8.
4. Click the checkmark to save, then close.

The dependent dropdown picks up the new cities immediately because it reads from the named range, not from a fixed cell address.

If your source lists grow frequently, consider using a dynamic named range built with OFFSET and COUNTA so the range expands automatically as you add rows. See our guide on the OFFSET function for the setup.

Conclusion

Dependent drop-down lists filter the second dropdown based on the first selection, preventing invalid combinations and making data entry cleaner. The technique combines named ranges with INDIRECT to route the dropdown source dynamically.

For the basics of creating a single dropdown, see our guide on drop-down lists in Excel. For a deeper look at how INDIRECT converts text into references, check out our guide on the INDIRECT function.