To create a dependent dropdown in Excel, name each list of sub-options with exactly the text of its parent option, then set the second dropdown’s Data Validation source to =INDIRECT(A2). INDIRECT turns the text selected in the first dropdown into a reference to the matching named range. If your category names contain spaces, replace them with underscores in the range names and use =INDIRECT(SUBSTITUTE(A2, " ", "_")).
A dependent dropdown (also called a cascading dropdown) is when the options in a second dropdown change based on what was selected in the first. Pick “Electronics” in column A, and column B shows “Laptop, Tablet, Phone.” Pick “Furniture,” and column B shows “Desk, Chair, Bookcase.” This eliminates invalid data entry and makes forms intuitive for users.
The source lists and named ranges
Step 1: Create Your Source Lists
On a separate sheet (call it “Lists”), set up each category’s options as a column with the category name as the header:
| A | B | C | |
|---|---|---|---|
| 1 | Electronics | Furniture | Clothing |
| 2 | Laptop | Desk | Shirt |
| 3 | Tablet | Chair | Pants |
| 4 | Phone | Bookcase | Jacket |
| 5 | Monitor | Filing Cabinet | Shoes |
| 6 | Keyboard | Hat |
Step 2: Name Each Range
Select each column of items (without the header) and create a Named Range that matches the header exactly:
1Select A2:A6 → Formulas → Define Name → Name: Electronics
2Select B2:B5 → Name: Furniture
3Select C2:C6 → Name: Clothing
Name must match exactly
The Named Range must match the category header character-for-character. “Electronics” and “electronics” are different names. Spaces in names cause problems — use underscores if your categories have spaces (e.g., “Office_Supplies”).
Step 3: Create the First Dropdown (Category)
1Select the cells where users choose a category (e.g., A2:A100)
2Go to Data → Data Validation
3Allow: List → Source: Electronics, Furniture, Clothing (comma-separated)
Step 4: Create the Dependent Dropdown (Subcategory)
1Select the subcategory cells (e.g., B2:B100)
2Data → Data Validation
3Allow: List → Source:
=INDIRECT(A2)
INDIRECT takes the text value in A2 (e.g., “Electronics”) and converts it to a reference to the Named Range called “Electronics”. When A2 changes to “Furniture”, the dropdown list changes to the Furniture range.
How the two dropdowns behave together
| A | B | |
|---|---|---|
| 1 | Category | Product |
| 2 | Electronics ▼ | Laptop ▼ |
| 3 | Furniture ▼ | Chair ▼ |
Clearing the subcategory on change
When someone changes the category, the old subcategory selection stays — even if it’s no longer valid. Excel doesn’t auto-clear it. For a form-like experience where changing Category A clears Category B, you need a small VBA macro triggered by the Worksheet_Change event.
Handling Categories with Spaces
If a category is “Office Supplies” (with a space), Named Ranges can’t have spaces. Use SUBSTITUTE in the INDIRECT formula:
=INDIRECT(SUBSTITUTE(A2, " ", "_"))
Converts “Office Supplies” to “Office_Supplies” to match the Named Range. Name your range “Office_Supplies” (with underscore).
Common questions
Can I add a third level?
Yes. The pattern repeats: name each third-level list after its second-level option, and point the third dropdown at =INDIRECT(B2).
Why is the second dropdown empty?
The named range does not exactly match the text selected in the first dropdown. INDIRECT is looking for a range by that exact name and finding nothing.
Can named ranges contain spaces?
No. Excel does not allow spaces in range names. Use underscores and strip them in the formula: =INDIRECT(SUBSTITUTE(A2, " ", "_")).
When Formulas Aren’t Enough
INDIRECT-based dependent dropdowns work for two levels. But real data entry forms often need more:
- Three or more cascade levels — Category → Subcategory → Product → Size gets unwieldy with Named Ranges
- Dynamic lists — new products added to the source data should appear in dropdowns automatically
- Auto-clear on change — when Category changes, Subcategory should reset (requires VBA)
- Search/filter in dropdowns — when a list has 100+ items, users need to type to filter
- Database-driven lists — dropdown options pulled from an external source or master database
A custom VBA data entry form handles multi-level cascading, auto-clearing, searchable dropdowns, and database-driven options — giving users an application-like experience inside Excel.
“The Best EXCEL service! Fast, Accurate and Reliable!”
Darryl H. · Repeat client · PeoplePerHour
Need a Professional Data Entry Form in Excel?
Tell us about your data entry needs and we’ll build a form your team will actually enjoy using.
Start a Project