Yes. Create a named range for each parent choice, then use INDIRECT in the child dropdown’s Data Validation source. With the parent selection in A2 and the dependent dropdown in B2, enter =INDIRECT($A2) as the source for B2’s list; the text in A2 must match a named range.
What a dependent dropdown does
A dependent dropdown limits the choices in one cell according to the selection in another. For example, choosing “Fruit” in a Category cell can make the next cell offer “Apple,” “Pear,” and “Orange,” while choosing “Vegetables” shows a different list. This is useful for order forms, survey sheets, inventory records, and other workbooks where users should choose from consistent options rather than type them.
Excel’s Data Validation feature creates the dropdown lists. INDIRECT connects the two lists: it takes text and interprets it as a cell reference or defined name. If A2 contains the text Fruit, =INDIRECT($A2) tells Excel to use the range named Fruit as the list source. INDIRECT does not create or filter the choices by itself; the named ranges and the Data Validation settings do that work.
Prepare the lists and matching names
Start by putting each group of child choices in its own range. For a small example, you could place the parent categories Fruit and Vegetables in one column, and put the items for each category in separate columns. The exact locations do not matter, but keeping source lists together on a sheet called Lists makes a workbook easier to maintain.
- Put the parent choices—Fruit and Vegetables, for example—in a simple list.
- Put the Fruit choices, such as Apple, Pear, and Orange, in one range.
- Put the Vegetables choices, such as Carrot, Pea, and Spinach, in another range.
- Give each child-choice range a defined name that exactly matches its parent label: Fruit and Vegetables.
To create a name in Excel for Microsoft 365 on Windows, select the cells containing a group of child choices, then choose Formulas > Define Name. Enter the name, confirm that the workbook is the intended scope, and select OK. Repeat for every group. You can also select the cells, click the Name Box to the left of the formula bar, type a valid name, and press Enter. Use Formulas > Name Manager to review or edit the names later.
Defined names cannot contain spaces, and they must follow Excel’s naming rules. They also cannot be written like a cell reference, such as A1. That is why the example uses simple labels with no spaces. If you want to use visible labels such as Home Goods, see the section on spaces below.
Create the parent and child dropdowns
Once the named ranges exist, add the dropdowns to the worksheet where people will enter data. For the parent list, select the cell or cells where users will choose a category. Choose Data > Data Validation, open the Settings tab, set Allow to List, and supply the parent choices in Source. For a short list, you can type the items separated by commas; for a longer list, use a range or a defined name. Select the in-cell dropdown option if it is not already selected, then confirm with OK.
Now select the child cell, such as B2, and open Data > Data Validation again. Set Allow to List. Assuming the parent selection is in A2 and the child dropdown is in B2, enter =INDIRECT($A2) in the Source box, then select OK. The dollar sign fixes the parent column at A while leaving the row relative, which is helpful when copying the validation down a column.
Choose Fruit in A2 and open the dropdown in B2: it should show the items in the Fruit named range. Change A2 to Vegetables, and B2 should offer the items in the Vegetables range. If the dropdown arrow is not visible, select the cell; Excel normally displays the arrow when the validated cell is active.
Handle spaces in category labels
Sometimes a worksheet needs readable parent labels with spaces, but defined names cannot contain spaces. A common workaround is to replace spaces with underscores in the name. For example, show “Home Goods” in the parent list, name its child-choice range Home_Goods, and use a formula that converts the label into the corresponding defined name.
For a layout with the visible parent label in A2 and the child dropdown in B2, and with a named range Home_Goods, use =INDIRECT(SUBSTITUTE($A2," ","_")) as the Data Validation Source. SUBSTITUTE changes the space to an underscore; INDIRECT then resolves the resulting text as a name. This works for labels whose only naming mismatch is spaces. If labels also contain punctuation or other characters that cannot be used in defined names, create a consistent naming scheme or use a separate lookup/helper area to translate labels to valid names.
Keep the visible category labels and the range names in sync. A spelling change in the parent list will break the connection unless the corresponding defined name or translation is updated too. For a workbook shared with other people, document the naming convention near the source lists so a future editor does not accidentally rename a range.
Apply the dropdowns to multiple rows
For a data-entry table, the usual pattern is one parent and one child selection per row: A2 controls B2, A3 controls B3, and so on. Set up the child validation in B2 using the relative-row reference shown above, then copy B2 and paste it into the other child cells. Excel adjusts the row reference as it copies. You can also select the full target range before opening Data Validation, though checking the first few rows afterward helps catch an incorrect reference.
The dollar sign in $A2 matters. It keeps the parent reference in column A if the validation is copied sideways, while the row number can change as the rule is copied down. A reference such as $A$2 would keep pointing at A2 in every row, so every child dropdown would depend on the same parent cell. Use that absolute-row version only when all the child cells are intentionally controlled by one fixed parent.
One related detail often surprises users: changing a parent choice does not automatically erase a child value that was selected earlier. For example, B2 may still display Apple after A2 changes from Fruit to Vegetables. The new dropdown choices will be vegetables, but the existing value can remain in the cell until someone replaces or clears it. Data Validation restricts new selections; it is not a rule that automatically cleans up old entries.
Common problems and how to fix them
- The child dropdown is blank or shows an error: Check that the parent cell is not empty and that its text matches a defined name. Confirm spelling, spaces, underscores, and the name’s scope in Name Manager.
- Excel says the Source evaluates to an error: Check that each expected name exists and refers to a valid range. If a parent label contains spaces, use the SUBSTITUTE version or another explicit mapping rather than passing the label directly to INDIRECT.
- Every row shows the same child list: Review the row reference. A locked reference such as
$A$2always points to the same parent. For row-by-row choices, use a fixed column and a relative row, as in$A2. - A dropdown arrow seems missing: Select the cell and check Data > Data Validation to confirm Allow is set to List and the in-cell dropdown is enabled. Also check whether the worksheet is protected or the cell is being viewed in a context that prevents editing.
- A category has a new item that does not appear: Check whether the defined name includes the added cell. A name that refers to a fixed range will not necessarily expand just because an item was typed below it.
Another potential issue is a name that exists only at worksheet scope rather than workbook scope. Excel names can have different scopes. For a straightforward dependent list used on the entry sheet, workbook-scoped names are generally easier to manage, especially when the validation refers to them from another sheet. If a workbook contains duplicate names at different scopes, inspect them in Name Manager rather than assuming Excel is resolving the one you intended.
What INDIRECT is good at—and its limits
INDIRECT is convenient because the validation source can be short and the relationship is easy to see: a parent value identifies a named range. It is supported in current desktop Excel, including Microsoft 365, and is also available in older Excel versions. That makes this technique useful when a workbook needs to work across a mix of Excel releases.
There are trade-offs. INDIRECT is a volatile function, meaning Excel recalculates it when recalculation occurs, even if its referenced text has not changed. A few dependent lists are unlikely to be a concern, but a very large workbook with many volatile formulas may be harder to keep responsive. The text-based reference can also be less transparent than an ordinary cell reference: if a name is renamed or a label changes, the formula may no longer resolve.
The named-range approach is also a little manual. Each category needs a valid name, and fixed-size names need to be expanded when the source list grows. Plan how the source lists will be maintained, especially if users regularly add categories or items. Excel’s Data Validation list is a selection aid, not a database relationship: it does not guarantee that every existing cell stays valid after source data changes.
Alternatives and choosing a method
For a small or medium workbook with stable categories, named ranges plus INDIRECT are often a clear, compatible choice. They require no macro and are relatively easy to explain to another editor. A mapping formula can also accommodate labels that are more reader-friendly than the defined names behind them.
For a workbook with changing lists, consider structuring the source data as an Excel table and using a more maintainable formula-based setup. Microsoft 365 and Excel 2021 include dynamic-array functions such as FILTER and UNIQUE, which can help produce changing lists. However, connecting a dynamic result to Data Validation may require a helper range or a defined name, depending on the workbook setup; do not assume that typing any spill formula directly into the validation Source box will work in every configuration.
Other options include helper columns or a small mapping table that translates each parent label to the range or list it should use. These methods make exceptions easier to manage, but add setup complexity. VBA can automate dependent dropdown behavior, but it requires a macro-enabled workbook and brings security and maintenance considerations. Choose INDIRECT when simplicity and compatibility matter most; choose a more dynamic design when the lists change frequently and the workbook can be maintained with modern Excel features.
Frequently Asked Questions
Does INDIRECT automatically create the dropdown list?
No. First create the child-choice ranges and define names for them. Then use INDIRECT in the Data Validation Source field to connect the parent cell’s text to the matching name.
Can I use INDIRECT when the lists are on another worksheet?
Yes. Put the source lists on a separate sheet and create defined names that refer to those ranges. The child validation can refer to the names without placing the lists on the entry sheet. Defined names are a common way to avoid relying on a direct cross-sheet range in the validation Source box.
Why does my dependent dropdown keep an old selection?
Data Validation changes the choices offered for a new selection; it does not automatically clear a value that is already in the cell. Clear or replace the child cell when you change the parent, or use a separate process if automatic cleanup is essential.
Will this work if my category has spaces?
Not with a same-spelling defined name, because Excel names cannot contain spaces. Use a name such as Home_Goods and convert the displayed label “Home Goods” to that name with SUBSTITUTE, or use a mapping approach for more complicated labels.
Is INDIRECT better than a dynamic-array solution?
It depends on the workbook. INDIRECT is straightforward and works in many Excel versions, but needs matching names and can be awkward to maintain as lists grow. Dynamic-array methods can suit changing lists in Microsoft 365 or Excel 2021 and later, but may need helper ranges or names to work cleanly with Data Validation.
Related
- Close Excel Shortcut Key for Windows and Mac
- Close Group Excel Shortcut: Close Workbooks and Rows
- Close Excel Shortcut: Windows and Mac Keys
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Row Filter: How to Show Only Matching Data RowsTo filter rows in Excel for Microsoft 365 on Windows, select a cell in your data and choose…
- Excel Formula for an Entire Column: How to Use ItTo calculate values from an entire column, use a whole-column reference such as A:A inside a function. To…
- Filter Excel Table by Multiple Criteria: Methods That WorkTo filter an Excel table by multiple criteria, open a column’s filter arrow and set its rule; rules…
- Excel Filter: Exclude Specific Text from ResultsIn Excel for Microsoft 365 on Windows, open the filter arrow on the column you want to check,…
- VLOOKUP Across Sheets in Excel: Formula and TipsYes. If the value to look up is in A2 on an Orders sheet, and a Product List…
- COUNTIF in Excel: Count Cells That Contain TextTo count cells in Excel that contain a particular text anywhere in the cell, use COUNTIF with an…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.