Excel Dependent Drop-Down Lists: Link One to Another

Yes. In Excel for Microsoft 365 on Windows, make the first drop-down with Data Validation, create a named range for each group of second-list choices, then set the second list’s Source to a formula that uses the first selection to find the matching range. This is called a dependent or cascading drop-down list; a dynamic-array method is another option when your source data changes often.

What a dependent drop-down list does

A regular drop-down offers the same choices every time. A dependent drop-down changes its choices according to a selection in another cell. For example, choosing “Beverages” in A2 could make B2 offer “Coffee,” “Tea,” and “Water,” while choosing “Snacks” could make B2 offer “Chips,” “Nuts,” and “Fruit.”

The relationship is between the selected category and the list of valid items for that category. Excel does not automatically infer this relationship from two adjacent cells; you provide it using named ranges and a formula, or by using a helper formula that filters a structured source table. The first approach works in a broad range of Excel versions. The dynamic-array approach is useful in Microsoft 365 and Excel 2021 or later.

These lists are useful in order forms, surveys, inventory sheets, project trackers, and data-entry templates. They guide people toward valid combinations and reduce spelling variations, such as “Tea,” “tea,” and “Tee,” that can make later sorting and analysis harder.

Prepare the source lists

Start by arranging your options on a separate worksheet, such as a sheet named Lists. For a named-range setup, put each group in its own column, with a heading above it. For example, place Beverages in A1 and Coffee, Tea, and Water below it; place Snacks in B1 and Chips, Nuts, and Fruit below it. Use one item per cell and avoid blank cells within a list.

Excel range names cannot contain spaces, so make the headings and names agree. One simple convention is to replace spaces in category names with underscores: “Hot Drinks” becomes the range name Hot_Drinks. Range names must begin with a letter, an underscore, or a backslash, and cannot look like a cell reference. Keep names clear and consistent, because the dependent-list formula relies on the category text matching the corresponding range name.

For the example, assume the first selection will be in A2 and the dependent list in B2. You can put the category choices in a separate range, such as Lists!D2:D3, or define a range called Categories for those choices. Keeping source data on its own sheet makes the entry form easier to use, though the source sheet does not have to be hidden.

Create the first drop-down

Select the cell or cells where users will choose a category, such as A2.
On the ribbon, choose Data > Data Validation.
On the Settings tab, choose List in the Allow box.
In Source, select the category cells or enter a reference to the named range Categories.
Make sure In-cell dropdown is selected, then choose OK.

If you enter a direct worksheet reference in Source, Excel’s Data Validation list may not accept a reference to a different sheet in that form. A named range is a dependable way to refer to category cells on another sheet: define Categories in Formulas > Name Manager, then use =Categories as the Source. You can also temporarily put the category list on the same sheet as the entry cells.

Name each dependent list

Create a named range for every group of second-level choices. Select only the items under the heading, not the heading itself, and use Formulas > Define Name. Enter a name that corresponds to the category, such as Beverages for the beverage items and Snacks for the snack items. Check that the name refers to the intended cells and has Workbook scope if you want to use it across worksheets.

If a category contains spaces, use a consistent replacement in the range name. For instance, name the list for “Hot Drinks” Hot_Drinks. The dependent formula can convert the space in the selected category to an underscore. Other punctuation can also cause name mismatches, so use simple category labels where possible. If your labels contain several special characters, either standardize them or choose the table-and-helper method described below.

Names need not be created one at a time in Name Manager. If the source data is arranged with headings above columns, you can select the heading-and-list block and use Formulas > Create from Selection, then choose Top row. Review the names afterward, especially if headings contain spaces: Excel may adjust them to valid names, and the names must still match the formula’s transformation.

Connect the second drop-down to the first

With the category in A2 and the corresponding named ranges already created, select B2 and choose Data > Data Validation. Set Allow to List and enter this in Source:

For this example, A2 contains the category, and the named ranges are Beverages and Snacks. The formula replaces spaces with underscores before looking up the range name: =INDIRECT(SUBSTITUTE(A2," ","_"))

Choose OK, then select a category in A2 and open the drop-down in B2. The available items should match that category. INDIRECT turns text into a reference, while SUBSTITUTE makes “Hot Drinks” match a range named Hot_Drinks. The cell reference A2 is relative, which is useful when applying validation down a column: each row can refer to its own category cell.

To apply the rule to more rows, copy B2 and paste it into the other dependent-list cells, or select the target range before creating the validation rule. Confirm that the formula refers to the category cell in the same row. A formula that keeps pointing to A2 for every row will show the same dependent choices throughout the range.

Use a dynamic-array alternative for changing data

Named ranges are straightforward when the categories and their lists are fairly stable. If items are added frequently, a single Excel Table is often easier to maintain. Make a table with columns named Category and Item, with one category-item pairing per row. For example, the rows Beverages/Coffee, Beverages/Tea, and Snacks/Chips belong in separate records. Select the data and choose Insert > Table; give the table a clear name such as SubcategoryTable using Table Design > Table Name.

In Microsoft 365 or Excel 2021 and later, a FILTER formula can return the items matching the category. For this layout, the table has columns Category and Item, A2 contains the category selection, and H2 is an unused helper cell on the same worksheet as the validation cell: =FILTER(SubcategoryTable[Item],SubcategoryTable[Category]=A2,"")

The results spill from H2 into cells below it. In the dependent cell’s Data Validation Source, enter =H2# to refer to the spilled list. If Excel does not accept that spill reference directly in your setup, define a workbook name in Formulas > Name Manager that refers to the spill range, then use that name as the validation Source. Keep the spill area clear of other data, and place the helper formula somewhere users are unlikely to overwrite it. FILTER is available in Microsoft 365 and Excel 2021 or later; it is not available in every older Excel release.

This table method makes adding records simpler: type a new category-item pair in the table, and the structured reference can include the added row. It does not eliminate the need for a valid category choice or a clean source table. Also, the helper result is tied to the category in its formula. For a form with many entry rows, each row needs an appropriate helper result or another carefully designed setup.

Make the list easier to maintain

Choose a method based on how the workbook will be used, not just on how quickly the first drop-down can be built.

Use named ranges for a small, stable set of categories and when compatibility with older Excel files matters.
Use a table and FILTER when items are added regularly and the workbook is intended for Microsoft 365 or Excel 2021 and later.
Keep labels consistent. A category’s displayed text must map reliably to its named range or table records.
Leave room for growth. Tables expand as records are added, while a fixed named range needs to be updated if new choices fall outside its original cells.
Protect source and helper cells if other users should not change the lists or formulas accidentally.

You can also set a helpful input message or error alert in Data Validation. An input message can tell users to choose a category first. The Stop-style error alert prevents users from entering a value that is not in the list, while less restrictive alert styles can allow an override. These settings improve guidance but do not replace checking the workbook’s data, especially when values are pasted into cells.

Common problems and how to fix them

The second list is empty or shows an error. Check whether the category text matches a named range after spaces are replaced. Confirm that the range name points to the item cells, not the heading, and that the formula uses the correct category cell. With FILTER, check the table name and column headings, and ensure the helper spill area is not blocked.

The category works, but categories with spaces do not. A range called Hot_Drinks will not match the unmodified text Hot Drinks. The SUBSTITUTE step handles ordinary spaces, but not every punctuation difference. Standardize the names or use the table-based method, which matches category text directly.

The second cell keeps an old choice after the category changes. Excel changes the choices offered by the drop-down, but it does not automatically erase a value already selected in the dependent cell. If someone chose Coffee and later changes the category to Snacks, B2 can still display Coffee even though Coffee is no longer a valid choice. Clear the dependent cell and choose again, or use a carefully designed automation process if automatic clearing is essential.

The list does not update after adding an item. A named range may cover only the original cells. Edit its Refers to range in Formulas > Name Manager, or set up a range with room for expected growth. A table-based source generally expands with new table rows, but verify that the new record was added to the table and that the helper formula includes the right table columns.

Users can still paste an invalid value. Data Validation is a useful entry control, not an absolute data-security boundary. Pasting or other workbook actions can sometimes introduce values that do not follow the intended list. For important records, add a review step or use formulas and reporting checks to flag values that do not match the allowed combinations.

Excel for Mac and Excel for the web

The main Data Validation workflow and named-range approach are also available in Excel for Mac, although ribbon layout and some dialog details can vary by release. On Mac, use the Data tab and its Data Validation command; keyboard shortcuts differ from Windows, so the ribbon path is the clearest route. Excel for the web supports common data-validation lists, but some workbook editing and formula behaviors can differ from desktop Excel. If a spill reference or a particular validation setting is unavailable in the web interface, create or adjust the rule in the desktop app and then check it in the browser.

When sharing a workbook, consider whether recipients will open it in a current desktop version, the web app, or an older Excel release. Dynamic-array functions such as FILTER require a compatible version. Named ranges and INDIRECT are generally the more conservative choice for a workbook that must reach a broader mix of Excel versions.

Frequently Asked Questions

Can one Excel drop-down control another?
Yes. Create the first list, then make the second list’s Data Validation Source depend on the first selection. Named ranges with INDIRECT are a common method; a helper formula using FILTER is another option in compatible Excel versions.

Can I make a dependent drop-down without INDIRECT?
Yes. In Microsoft 365 or Excel 2021 and later, use a helper formula with FILTER to return matching items, then point the second list to the resulting spill range or a defined name for that range. A table of category-item pairs is a convenient source for this design.

Why does changing the first drop-down not clear the second cell?
Data Validation controls the choices available when a user opens the second list; it does not automatically remove an existing entry when the first choice changes. Clear the second cell manually, or use automation if the workbook specifically needs that behavior.

Can the source lists be on another worksheet?
Yes. Named ranges can refer to cells on another sheet, and the dependent validation rule can use those names. A direct cross-sheet reference in the Data Validation Source may be rejected, so using a defined name is a practical workaround.

How do I add more choices later?
For named ranges, extend the relevant range in Formulas > Name Manager so it includes the new cells. For a table-based setup, add a new category-item row to the table and confirm that the FILTER helper formula and validation source include its result.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.