Yes. In Excel for Microsoft 365 on Windows, create a drop-down for the first choice, then make the second list’s Data Validation source depend on that choice. A dependable setup uses named ranges for each group and the INDIRECT function; for example, choosing Fruit can make the next drop-down show Apple and Pear.
What a dependent drop-down does
A dependent drop-down, also called a cascading drop-down, changes its available choices according to a value selected elsewhere. It is useful when a person should first select a broad category and then choose an item within that category. For instance, a service request form might ask for a department first, then offer only the request types belonging to that department.
Excel’s Data Validation feature creates the lists, but it does not automatically understand how one list relates to another. You provide that relationship through a formula or a named range. The first list is usually called the parent list; the list controlled by it is the dependent or child list.
Build the source lists
Start with a small, clearly organized list on a worksheet. In this example, put category names in A1:A3 on a sheet named Lists, with one item per row in each category column:
- Lists!A1:A3: Fruit, Vegetable, Beverage
- Lists!B1:B2: Apple, Pear
- Lists!C1:C2: Carrot, Spinach
- Lists!D1:D2: Tea, Coffee
The category names are the parent choices. Each adjacent column contains the choices for one category. Keep the entries contiguous, without blank cells between choices, and use simple headings that can also be valid Excel range names. Avoid spaces and punctuation in these headings; Fruit, Vegetable, and Beverage are suitable.
Make a named range for each list. Select B1:B2, choose Formulas > Define Name, enter Fruit as the name, confirm that the scope is Workbook, and select OK. Repeat for C1:C2 using the name Vegetable and D1:D2 using Beverage. To make a named range for the parent list, select A1:A3 and define the name Categories. You can review or edit names through Formulas > Name Manager.
Named ranges are useful here because Data Validation’s list source is awkward when it directly points to a range on another worksheet. A workbook-level name gives the validation rule a concise reference, such as =Categories, rather than a cross-sheet address.
Create the parent drop-down
On the entry sheet, use C2 for the category and D2 for the dependent item. Select C2, then choose Data > Data Validation > Data Validation. In the Settings tab, set Allow to List and enter the named range as the Source:
With the parent choices stored in Lists!A1:A3 and defined as the workbook-level name Categories, enter =Categories in the Source box for C2.
Confirm that In-cell dropdown is selected, then choose OK. C2 should now display a drop-down with Fruit, Vegetable, and Beverage. If it does not, check that the name is spelled correctly in Name Manager and that its scope is Workbook.
Make the second list depend on the first
Select D2 and open Data > Data Validation > Data Validation. Set Allow to List. With C2 holding the parent choice and the three category lists named Fruit, Vegetable, and Beverage, use this Source formula:
=INDIRECT($C2)
Choose OK. If C2 contains Fruit, Excel reads the text Fruit, treats it as the name of a range, and shows the values in the named range Fruit. If C2 contains Beverage, it uses the Beverage range instead. The dollar sign locks the parent column C while leaving the row number relative, which helps when copying the rule down.
Test all parent choices, not just the first one. Select Fruit in C2 and confirm D2 offers Apple and Pear; change C2 to Vegetable and confirm D2 offers Carrot and Spinach. This quick check catches misspelled names and mismatches between category labels and range names.
Copy the rules to additional rows
For a form with several records, apply the parent validation to C2:C100 and the dependent validation to D2:D100. One way is to set up both rules in row 2, copy C2:D2, select C3:D100, and use Paste Special > Validation. The relative row reference in the dependent formula adjusts so D3 refers to C3, D4 to C4, and so on. Check a few rows after copying.
Each row’s second drop-down responds to the parent cell on that same row. The named lists remain shared: every row choosing Fruit uses the same Fruit range. If users need different categories or item lists for different records, update the source lists and named ranges accordingly.
Changing the parent value does not necessarily erase a previously selected child value. For example, a cell may still contain Pear after its parent is changed from Fruit to Beverage, even though Pear is no longer offered in the dependent menu. Data Validation restricts new selections; it does not automatically clear or revalidate existing entries. Users should choose a new child value after changing the parent, or you can use a macro or other workflow if automatic clearing is essential.
Category names with spaces or punctuation
Excel range names cannot contain spaces, so a visible category such as Home Supplies cannot be used unchanged as a named range. One option is to use underscores consistently: name the range Home_Supplies and adjust the dependent formula to convert spaces to underscores.
With the parent label in C2 and a corresponding named range called Home_Supplies, this formula handles spaces in the label:
=INDIRECT(SUBSTITUTE($C2," ","_"))
This conversion only solves spaces. It does not reliably map every possible label containing punctuation, symbols, or other characters that are not allowed in range names. For arbitrary display labels, keep a separate key such as Home_Supplies alongside Home Supplies, or use a lookup-based design that maps labels to valid range names. Do not assume every category label can be turned into a valid name by replacing spaces.
Dynamic-array alternative for changing lists
If your source items change often, Microsoft 365 has dynamic-array functions that can produce a filtered list without creating a separate named range for every category. This approach needs a helper formula and a named range pointing to the formula’s spill range. It is most straightforward when building one dependent list at a time; forms with many independent rows need a separate helper area or a more advanced design for each row.
For this example, assume the source data is in an Excel table named Items with columns Category and Item, and the selected category is in C2. Put the following formula in F2 on a helper sheet:
=FILTER(Items[Item],Items[Category]=Entry!C2,"")
The formula returns the items whose Category matches Entry!C2 and spills the results below F2. Replace Entry with the actual worksheet name if needed; if a sheet name contains spaces, Excel requires apostrophes around that sheet name in the reference. FILTER is available in Microsoft 365 and Excel 2021 or later. The empty text argument means the formula returns a blank if no rows match.
In Formulas > Name Manager, create a workbook-level name such as FilteredItems that refers to the helper spill range =Helper!$F$2#, using the actual helper sheet name. Then set the dependent cell’s Data Validation Source to =FilteredItems. The spill operator, #, refers to the full range produced by the formula. If Excel will accept a direct spill reference in the Source box, =Helper!$F$2# may also work, but using a defined name is a practical compatibility workaround.
Because this helper formula refers to Entry!C2, it is set up for that selected category. To use the same method for many entry rows, provide a corresponding helper formula and spill range for each row, or choose the simpler named-range method when the categories are fixed. Excel tables can expand as source rows are added, which is an advantage for maintaining the underlying item data, but the helper and validation setup still needs to be designed for the number of dependent cells in the form.
Common problems and how to fix them
- The second list is empty or shows an error: Check that the value in the parent cell matches a defined range name exactly, or that your label-to-name conversion produces the correct name. Confirm each named range covers the intended cells.
- The Data Validation dialog rejects the source: Check that Allow is set to List and that the name is workbook-scoped. A named range is generally more reliable than typing a cross-sheet cell reference directly into the Source box.
- The list includes a blank choice: Remove blank cells inside the named range or adjust the range so it includes only actual choices. Blanks at the end can also create an empty option.
- New source items do not appear: A fixed named range such as B1:B2 does not automatically include a new entry in B3. Expand the range or use a source design that grows with the data, such as an Excel table and a dynamic-array helper.
- The wrong list appears after copying: Verify the formula uses a relative row, as in
=INDIRECT($C2), and that it was applied to the intended rows. A fully locked reference such as $C$2 would make every row depend on C2. - A pasted value ignores the drop-down: Validation is not a security boundary. Pasting or other editing methods can introduce values that do not appear in the list. Use appropriate checks if the workbook must enforce data quality.
Which method should you choose?
Named ranges with INDIRECT are a good fit when categories are stable, there are a manageable number of them, and the labels can match valid range names. They are easy to inspect and work well in standard Excel workbooks, but each category needs a named range and changes to the source range may require maintenance.
A dynamic-array helper is more convenient when item data changes frequently or is stored as a table. FILTER can generate the relevant choices from the category column, avoiding a manually maintained range for every group. It depends on newer Excel functions and needs careful setup for multiple independent rows. Excel 2021 and Microsoft 365 include FILTER; users of older versions should use named ranges or another compatible approach.
There is no need to use VBA for an ordinary dependent drop-down. Macros can automate tasks such as clearing an old child choice when a parent changes, but they add security and maintenance considerations. If you do use a macro, save a copy of the workbook first: macro actions cannot be undone with Ctrl+Z. Excel for Mac has comparable data-validation features, though ribbon placement and dialogs can differ; these steps describe Excel for Microsoft 365 on Windows. Excel for the web may not expose every name-management feature in the same way as desktop Excel.
Frequently Asked Questions
Can I make a dependent drop-down without INDIRECT?
Yes. In Microsoft 365 or Excel 2021 and later, a FILTER formula can create a helper list based on the parent choice, and a defined name can refer to the spilled results. For fixed groups, named ranges with INDIRECT are often simpler.
Does changing the first drop-down clear the second one?
No. Excel’s Data Validation menu limits the choices offered, but it does not automatically remove a value already in the dependent cell. Re-select a valid child value after changing the parent, or design an automated process if clearing is required.
Can the category names contain spaces?
Visible labels can contain spaces, but named ranges cannot. You can translate spaces to underscores with SUBSTITUTE, use a separate key for each label, or use a lookup-based design. Replacing spaces alone will not handle every punctuation mark.
Why does my dependent list show all the options or no options?
Check the dependent cell’s Source formula, the parent cell’s exact text, and the spelling and scope of the named ranges. Also check that the source range contains only the choices intended for that category.
Will the drop-down update when I add a new item?
Not if its named range ends at the old last cell. Expand the named range or use a table-based source with a dynamic-array helper. After changing the setup, test the new item in the actual dependent drop-down.
Related
- Company Invoice Template in Excel: Setup and Tips
- Compare Lists in Excel: Find Matches and Differences
- Combine Two Cells in Excel and Keep Both Text
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Apple Numbers vs Excel: Which Spreadsheet Should You Use?Apple Numbers is a strong choice for attractive, straightforward spreadsheets on Apple devices; Microsoft Excel is usually the…
- Excel Group Rows: How to Expand and Collapse Rows EasilyIn Excel for Microsoft 365 on Windows, select the rows you want to fold away, choose Data >…
- Excel Row and Column AutoFit: Steps, Limits, and FixesTo auto-adjust Excel row or column size, select the relevant rows or columns, then choose Home > Cells…
- Word Online Watermark: How to Add One in a BrowserWord for the web can display a watermark, but it does not currently let you insert or change…
- Freeze Panes in Excel: Keep Rows and Columns VisibleTo lock rows or columns in place while you scroll in Excel for Microsoft 365 on Windows, select…
- Checking Duplicates in Google Sheets: A Practical GuideTo check duplicates in Google Sheets, select the cells you want to check and use Format > Conditional…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.