Link Two Data Validation Lists in Excel: Step-by-Step

To link two data validation lists in Excel, make the first dropdown a category list, create a named range for each category’s options, then set the second dropdown’s source to =INDIRECT($A2) (assuming the first dropdown is in A2). The second list will then show options for the category selected in the first. You’ll need a worksheet containing the lists and a desktop version of Excel for the clearest setup.

What You’ll Need

Excel for Microsoft 365 on Windows. The steps are similar in current desktop versions of Excel for Mac.
A worksheet for the choices, plus a worksheet where people will make selections.
Category names that can be used as named ranges. Excel names cannot contain spaces, so use simple names such as Fruit or Drinks, or use underscores such as Hot_Drinks.

This is commonly called a dependent dropdown: the value chosen in the first list determines what appears in the second. The example below uses categories named Fruit, Drinks, and Bakery. You can replace those with your own choices while keeping the names consistent.

Step-by-Step

Set up the source lists. Add a worksheet called Lists. In A2:A4, enter the category names Fruit, Drinks, and Bakery. In B1:D1, enter the same names as column headings. Under each heading, enter that category’s options, one per cell. For example, put Apple, Pear, and Orange in B2:B4; Tea, Coffee, and Water in C2:C4; and Bread, Cake, and Muffin in D2:D4. Keep each category’s options together in one column and do not include the heading in the option range.
Create the name for the first dropdown’s source. Select Lists!A2:A4. Click the Name Box at the left of the formula bar, type Categories, and press Enter. The Name Box usually displays the selected cell address before you type. This creates a workbook-level named range for the category choices. If you prefer the ribbon, select the cells and choose Formulas > Define Name, enter Categories as the name, and confirm that the reference points to Lists!$A$2:$A$4.
Name each category’s option range. Select Lists!B2:B4, excluding the Fruit heading. Type Fruit in the Name Box and press Enter. Repeat for Lists!C2:C4, naming it Drinks, and Lists!D2:D4, naming it Bakery. Each name must match its category text exactly. You can also create these names with Formulas > Define Name; check that each reference covers only the relevant options. Names are normally workbook-scoped when created this way.
Check the names before adding validation. Open Formulas > Name Manager and confirm that Categories, Fruit, Drinks, and Bakery appear in the list. Select each name to check its Refers to address. If a name includes a heading or misses the last option, edit it here. A correctly defined name is essential: the dependent list uses the selected category text to find the matching named range.
Choose where users will make selections. On a separate worksheet, such as Entry, use A2 for the category and B2 for its related option. You can label A1 Category and B1 Choice. If you want the dropdowns on more rows, decide on a range such as A2:A100 and B2:B100 now. The example source below assumes the first dropdown is in column A, beginning on row 2.
Add the first dropdown. Select Entry!A2, or select A2:A100 if you want the category dropdown ready on multiple rows. Choose Data > Data Validation. In the Data Validation dialog, set Allow to List. With Categories as the named range for the choices, enter this in Source: =Categories. Leave In-cell dropdown checked, then choose OK. Select a cell in the range and confirm that Fruit, Drinks, and Bakery appear.
Add the dependent dropdown. Select Entry!B2, or select B2:B100 to configure several rows at once. Choose Data > Data Validation, set Allow to List, and enter the source formula below. The dollar sign fixes the category column while leaving the row relative, so B3 looks at A3, B4 looks at A4, and so on. Keep In-cell dropdown checked and choose OK.
With category selections in A2:A100 and dependent cells in B2:B100, use =INDIRECT($A2) as the Source. For just one pair of cells, the same formula works when A2 contains the category and B2 is the dependent dropdown.
Test each category and row. Select Fruit in A2, then open B2’s dropdown; it should show Apple, Pear, and Orange. Change A2 to Drinks and confirm that B2 offers Tea, Coffee, and Water instead. If you set up several rows, choose different categories in A2 and A3 and confirm that B2 uses A2 while B3 uses A3. Save the workbook after the tests work.

If your category labels contain spaces, the name used by INDIRECT must still be a valid Excel name. For example, label a category Hot Drinks, name its options range Hot_Drinks, and use a formula that replaces the space with an underscore. With the category in A2 and its named range called Hot_Drinks, use =INDIRECT(SUBSTITUTE($A2," ","_")) as the dependent list’s Source. This simple conversion handles spaces; it does not automatically remove punctuation or convert every possible label into a valid name. For easier maintenance, categories without spaces or punctuation are usually best.

Common Mistakes

The category and range names do not match. If the first dropdown says Beverages but the named range is Drinks, INDIRECT cannot find a range called Beverages. Either change the category text or rename the option range so the names match. Check spelling, spaces, and underscores as well as capitalization; using the same capitalization throughout makes errors easier to spot.

The named range includes the column heading. If you selected C1:C4 for Drinks, the second dropdown may include the word Drinks as an option. Define the range from the first actual choice, such as C2, through the final choice. When the number of options changes, update the range so new items are included.

The source formula uses the wrong row reference. When applying validation to B2:B100, use $A2, not $A$2. The first version locks the category column but allows the row to change. The second locks both the column and row, so every dependent dropdown would look at A2. If you start your selected range on a different row, make the row number in the formula match its first row.

A category contains characters that cannot be in a name. Named ranges cannot contain spaces and cannot be written like a cell address. A category such as Hot Drinks needs a compatible name such as Hot_Drinks and a formula that translates the space, or you can rename the category to Hot_Drinks. Do not assume that INDIRECT will clean up labels automatically.

Changing the first choice leaves an old second choice in place. Excel does not automatically erase a previously selected value when the category changes. If A2 changes from Fruit to Drinks while B2 still contains Apple, that entry may no longer be valid. Clear B2 and choose a new option. You can also use a Stop-style error alert in Data Validation to prevent users from typing choices that are not on the current list.

Tips

Keep the source lists on a dedicated worksheet and make the category and range names easy to understand. That makes the workbook simpler to review and reduces the chance that someone will accidentally change the options. You can hide the Lists sheet after setup if users do not need to edit it, but do not delete it: the named ranges depend on the cells there.

Use Formulas > Name Manager when you add or rename categories. It shows each name and its cell reference in one place, which is useful when a dependent list stops working. If a list grows, update the range’s Refers to address to include the new entries. Avoid leaving blank cells in the middle of a short source range because they can appear as blank choices in the dropdown.

For a larger workbook, agree on a naming rule before building the lists. For instance, use single words for category labels and range names, or replace spaces consistently with underscores. The category text in the first dropdown is effectively a key: Excel uses that text to locate the corresponding named range. A predictable key is more reliable than trying to account for many different punctuation patterns.

On Excel for Mac, the Data Validation command is also available from the Data tab, though dialog placement and ribbon layout can vary slightly. You can use the Name Box or Formulas > Define Name to create ranges. In Excel for the web, basic dropdowns can be used, but some named-range or validation editing tasks may be easier in the desktop app. If a source formula is rejected in the web version, open the workbook in desktop Excel and configure it there.

Troubleshooting

The second dropdown is empty or shows an error. First select a valid category in the cell to its left. Then check Formulas > Name Manager to confirm that a name with exactly that text exists and points to cells containing options. Check that the Data Validation Source in the dependent cell begins with an equals sign and refers to the correct category cell. If the category is blank, INDIRECT has no range name to look up, so choose a category before testing the dependent dropdown.

The second list shows the wrong category’s choices. Check the source formula’s reference and the top row of the selected range. For a dependent range starting in B2, the formula should refer to A2. If you copied validation from another row or column, open Data > Data Validation for the affected cell and correct the Source. Confirm that the category choices and named ranges have not been swapped.

Excel says the Source evaluates to an error. A name may be misspelled, may not exist, or may refer to an invalid range. Check that the name is workbook-scoped and that the Refers to address still points to cells on the Lists sheet. If the category label has spaces, use the underscore naming approach and the SUBSTITUTE version of the formula, or simplify the label. A workbook with a sheet renamed or source cells removed may also need its names repaired in Name Manager.

New options do not appear. The named range may still end at the old last item. Open Name Manager and extend its Refers to address to cover the new cells, then test the dropdown again. If users will frequently add options, make a habit of checking the range after edits; a fixed range does not automatically include cells added below it.

Users can type values that are not in the list. Open Data > Data Validation, select the Error Alert tab, and make sure Show error alert after invalid data is entered is enabled. Choose Stop as the style if you want Excel to reject unlisted entries rather than merely show a warning. This does not clear old entries when the first dropdown changes, so users still need to reselect the second value.

Frequently Asked Questions

Can I link more than two dropdowns?
Yes. You can add another dependent dropdown by creating named ranges for the next level and making its validation source refer to the appropriate earlier selection. Plan and test each level carefully, because every category value needs a matching range name.

Do the category names have to be identical to the named ranges?
For the basic INDIRECT setup, yes. The selected category text is used to find a named range with that name. If labels contain spaces, translate them consistently with a formula such as the SUBSTITUTE example, or use simpler labels.

Will the second dropdown update automatically when I change the first?
The available choices update when you open the second dropdown, but Excel does not automatically clear a value already selected there. Remove the old value and choose one that belongs to the newly selected category.

Can I use this method in Excel for the web?
You may be able to use existing dropdowns, but creating or editing named ranges and validation sources can differ from desktop Excel. If the web app will not accept the source, use Excel for Microsoft 365 on Windows or Mac to set up and check the workbook.

What if I want the list to include new items automatically?
The basic named ranges in this guide cover fixed cell ranges, so extend a name when you add items. For a list that changes often, consider a carefully designed dynamic named range, but test it in the version of Excel used by everyone who edits the workbook. Keeping a clear, maintained source range is often easier to troubleshoot.

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.