Yes. In Excel for Microsoft 365, you can make one dropdown depend on another by using Data Validation lists and named ranges: choose a value in the first cell, and the second cell then offers only the related choices. A reliable setup uses a named range for each group and an INDIRECT formula for the dependent list.
What a dependent dropdown does
A dependent dropdown, also called a cascading dropdown, filters the choices in one cell based on a selection in another. For example, choosing a product department such as Fruit can make the next dropdown offer Apple, Pear, and Orange, while choosing Drinks offers Water, Tea, and Coffee. This is useful in order-entry forms, surveys, inventory sheets, and other workbooks where a long combined list would be hard to use.
Excel creates dropdowns with Data Validation. Data Validation can restrict a cell to a list, but it does not automatically understand that two lists are related. The relationship comes from how you organize the source choices and connect the second validation rule to the first selection.
Prepare the source lists
For a straightforward example, put source lists on a separate worksheet named Lists. Arrange the parent choices in one column and each group of child choices in its own column:
- Lists!A2:A4: Fruit, Drinks, and Bakery.
- Lists!B2:B4: Apple, Pear, and Orange.
- Lists!C2:C4: Water, Tea, and Coffee.
- Lists!D2:D4: Bread, Bagel, and Muffin.
Make sure each child list contains only choices for its parent. Keep the parent labels consistent, since Excel will use the selected label to find the corresponding named range. A separate Lists sheet keeps the form tidy, but the source values can be on another sheet or elsewhere in the workbook.
Excel defined names cannot contain spaces, so name each child range to match its parent after replacing spaces with underscores if needed. In this example the simple names are Fruit, Drinks, and Bakery. If a parent label were Hot Drinks, name its range Hot_Drinks. Avoid punctuation or inconsistent spelling that would make the label and name difficult to match.
Create the named ranges
Create a workbook-level name for the parent list and one for each child list. Select Formulas > Name Manager > New, enter a name, and set Refers to to the relevant source range. For example, name Lists!$A$2:$A$4 Categories, Lists!$B$2:$B$4 Fruit, Lists!$C$2:$C$4 Drinks, and Lists!$D$2:$D$4 Bakery. Click OK for each name. You can also select a source range and use the Name Box to the left of the formula bar to assign a name, then press Enter.
To avoid an invalid reference before the user chooses a parent, create one more name called EmptyList that refers to a blank cell. It is only a placeholder; it keeps the dependent dropdown empty until a parent is selected. Use names that are unique in the workbook and do not include spaces.
Add the first dropdown
Suppose the form will use column A for the parent and column B for the dependent choice, beginning on row 2. Select A2, then choose Data > Data Validation. In the Settings tab, set Allow to List and enter the parent-list name as the source. If the dialog offers an In-cell dropdown option, leave it selected. Choose OK. Copy A2 down to the other form rows if users need to enter multiple records.
With the form layout A2 for the parent selection and Lists!A2:A4 named Categories, the Data Validation list Source is =Categories. This source points to the three parent choices, so users can select Fruit, Drinks, or Bakery.
Make the second dropdown depend on the first
Select B2 and open Data > Data Validation again. Set Allow to List. With parent selections in A2 and dependent choices in B2, enter this formula in the Source box:
=INDIRECT(IF($A2="","EmptyList",SUBSTITUTE($A2," ","_")))
The formula checks A2. If it is blank, it points to EmptyList; otherwise, SUBSTITUTE changes spaces in the selected label to underscores, and INDIRECT treats the resulting text as the name of the relevant range. Thus, Fruit points to the range named Fruit, and Hot Drinks would point to Hot_Drinks. The dollar sign fixes the parent column, while the row number remains relative so the rule can be copied to other rows.
Click OK, then test the result by selecting each parent in A2 and opening the dropdown in B2. To apply the rule to additional rows, copy B2 and paste it into the cells below, or use Paste Special > Validation. Confirm the formula adjusts by row: in B3 it should refer to $A3, not $A2. Excel for Mac uses the Data Validation command from the Data menu or ribbon depending on the current layout; the underlying named-range method is the same. Excel for the web can use many validation lists, but if a command or editing option is missing, open the workbook in the desktop app to set up or troubleshoot the rule.
Understand the formula and its limits
INDIRECT is a reference function: it turns text into a cell or range reference. SUBSTITUTE makes the displayed parent label compatible with the child range name by changing spaces to underscores. The IF handles an empty parent cell so Excel uses the blank placeholder instead of trying to find a name for an empty string. The formula does not create or filter the source lists; those ranges and names must already exist.
This method is convenient when there are a manageable number of categories and the choices are maintained by the workbook owner. It also has a naming constraint: the formula only replaces spaces. If labels include hyphens or other characters that do not match valid range names, standardize the labels or use a separate mapping scheme rather than assuming the formula will convert every character automatically.
Alternatives for larger or changing lists
Named ranges work well for small, clearly separated groups, but they require a corresponding name for every parent. If a workbook has many groups or the choices change frequently, maintaining those names can become tedious. One option is to use a helper area that creates the relevant list for each row, then point Data Validation to that row’s helper range. In Microsoft 365, dynamic array functions such as FILTER can generate matching choices in a worksheet; however, confirm that the resulting range is connected to Data Validation in the version and workbook setup you use. A spilled result alone does not automatically make a cell a dropdown.
Excel Tables are useful for keeping source data organized and expanding it as rows are added, but simply converting a two-column category-and-item list to a Table does not create dependent dropdowns. You still need a method that selects the matching items for the current parent and makes those items available to the validation rule. For a shared form with only a few fixed categories, named ranges are often easier to inspect and hand off than a more elaborate helper formula.
Common problems and fixes
- The child dropdown is empty. Check that the parent cell has a selection, that its spelling matches a defined name after spaces are replaced with underscores, and that the name refers to the intended non-empty cells.
- Excel reports a source error. Verify that the names exist at workbook scope and that the formula begins with an equals sign. Also check that the EmptyList name refers to a real blank cell.
- Spaces or punctuation break the match. The example handles spaces only. Use consistent parent labels and range names, or revise the naming approach to handle the characters your labels contain.
- The old child choice remains after changing the parent. Data Validation controls what can be selected next; it does not automatically erase a value that is already in the dependent cell. Clear B2 after changing A2, or add a separate, carefully reviewed automation if automatic clearing is essential.
- The dropdown works in one row but not another. Copy the validation rule down and confirm each row refers to its own parent cell. A fixed reference such as $A$2 would make every row depend on A2.
Data Validation’s error alert can help prevent entries that are not on the list, but it is not a substitute for checking existing values. If a parent changes, the former child value may no longer be valid even though it remains visible. Consider adding a review step or a conditional formatting rule if users need a clear warning about stale selections.
Tips for maintaining the workbook
Keep the source lists together and document the relationship between each category and its named range. When you add a new item, expand the relevant range so it includes that item. When you add a new parent, create its child list and named range, then make sure the parent list includes the new label. If a named range is too short, the new choice will not appear even though it is visible on the Lists sheet.
For a workbook used by other people, protect the source sheet from accidental edits while leaving the entry cells unlocked if sheet protection suits the workflow. Test each parent category and several copied rows before distributing the file. Also try changing a parent after entering a child choice; this reveals the important limitation that Excel does not clear a now-mismatched child selection by itself.
Frequently Asked Questions
Can I make a dependent dropdown without VBA?
Yes. The named-range and Data Validation method above uses worksheet features and formulas, not VBA. VBA is only an optional route for automating tasks such as clearing a child cell when its parent changes.
Why does my dependent dropdown show the wrong options?
Usually the parent label points to the wrong named range, or the validation formula refers to the wrong row. Check the name in Formulas > Name Manager and make sure a row such as B3 uses $A3 as its parent reference.
Does changing the first dropdown clear the second one?
No. Standard Data Validation does not delete a value already in the dependent cell. The user must clear or replace that value, or you must add automation that handles the change.
Can I use spaces in category names?
Yes, in the visible parent choices. With the formula shown here, name the corresponding child range with underscores instead of spaces. For example, Hot Drinks maps to Hot_Drinks.
Can I use this in Excel for Mac or Excel for the web?
The same basic named-range approach applies in Excel for Mac. Ribbon and menu placement can vary, and some setup or editing controls may differ in Excel for the web, so use the desktop app if you cannot find a needed Data Validation or Name Manager option.
Related
- Daily Employee Schedule Template Excel: Setup & Tips
- Daily Schedule Excel Template: Plan Your Day in Excel
- Daily Cleaning Checklist Template Excel: Setup Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add Page Numbers in Word Starting on Page 2To add page numbers in Word from the second page, insert a page number, then turn on Different…
- Excel Conditional Formatting Icon Sets: A Practical GuideTo apply an icon set in Excel for Microsoft 365 on Windows, select the cells, then choose Home…
- Excel Print Area Shortcut: Set, Add, and Clear ItIn Excel for Microsoft 365 on Windows, select the cells you want to print, then press Alt, P,…
- Excel Sum by Cell Color: Methods That Actually WorkExcel does not have a built-in SUMIF function that adds cells based on fill color. To total one…
- Import CSV to Excel: Steps, Settings, and Common FixesTo import a CSV file into Microsoft Excel for Microsoft 365 on Windows, select Data > From Text/CSV,…
- Change the Folder Pane View in OutlookTo change the folder pane in Outlook for Windows, select View > Folder Pane, then choose Normal, Minimized,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.