Excel Data Validation List Based on Selection: How-To

How-to basics · Updated November 19, 2026

To make an Excel drop-down list depend on another selection, create the first list, then set the second list’s Data Validation source to a formula that points to the matching choices. With the first selection in A2 and named ranges whose names match its choices (using underscores instead of spaces), use =INDIRECT(SUBSTITUTE(A2," ","_")) as the second list’s source. This classic method works well for fixed categories; for lists that change with your data, a Microsoft 365 dynamic-array approach is another option.

What a dependent drop-down list does

A dependent list, sometimes called a cascading drop-down, changes its available choices according to a value selected elsewhere. For example, a user might choose a department in one cell and then see only that department’s job titles in the next cell. The first selection determines which set of values the second validation list displays.

This is different from simply putting every possible choice into one long list. A dependent list can make a form easier to use and reduce accidental mismatches, such as choosing a product that does not belong to the selected category. It does not automatically update other cells or guarantee that all data in a worksheet is valid, however; it controls the choices offered when someone edits the validated cell.

Choose a method for your workbook

For a small, stable set of categories, named ranges combined with INDIRECT are straightforward and work in many Excel versions. Each category gets a named range, and the selection in the first cell determines which range supplies the second list. This is especially convenient when you can give each category a simple name.

For a source list that grows or changes, Microsoft 365’s FILTER, UNIQUE, and SORT functions can generate matching choices from a regular data table. This avoids maintaining a separate named range for every category, but it requires a helper area for the results and a compatible Excel version. These functions are available in Microsoft 365 and Excel 2021 or later; the dynamic-array method below is written for Microsoft 365 on Windows.

  • Use named ranges when the categories and their choices are mostly fixed.
  • Use a filtered helper list when source records are frequently added or revised.
  • If other people use the workbook in older Excel versions, test the design in those versions before relying on newer functions.

Build dependent lists with named ranges

Here is a simple layout. Put category names in a small list, such as Drinks and Food. Put the choices for each category in separate columns or ranges, with each set kept together. For example, one range might contain Tea and Coffee, and another might contain Soup and Salad. The names assigned to the ranges must correspond to the category selections.

  1. Select the category choices and create a named range for them. On Windows, use Formulas > Define Name, enter a name such as Drinks, confirm that the reference points to the correct cells, and select OK. Repeat for each category. Names cannot contain spaces, so use names such as Hot_Drinks when a category label is “Hot Drinks.”
  2. Select the cell for the first drop-down, such as A2. Choose Data > Data Validation. On the Settings tab, set Allow to List and set Source to the cells containing your category names, or to a named range containing those names. Make sure In-cell dropdown is selected, then select OK.
  3. Select the cell for the dependent list, such as B2. Open Data > Data Validation, choose List, and enter the formula from the opening example in Source. It converts spaces in the selection to underscores before looking up the named range. Select OK.
  4. Test each category in A2 and open the drop-down in B2. Confirm that each selection offers the intended choices, including categories with spaces in their labels.

The formula assumes A2 contains a category label that becomes the exact named-range name after spaces are replaced with underscores. If labels contain other punctuation, or if the range names do not follow that pattern, use a small mapping table or a helper cell to translate labels into valid range names. Avoid naming conflicts with cell references and keep the range names consistent as the workbook changes.

In Excel for Microsoft 365 on Windows, the ribbon path is Data > Data Validation. Excel for Mac has a Data Validation command on the Data tab, though the dialog layout can differ. The underlying named-range idea and formula are the same. In Excel for the web, available controls can differ from desktop Excel; if you cannot create or edit the required validation setup there, make the configuration in the desktop app.

Use FILTER for a list that follows source data

A dynamic approach is useful when your records are stored as two columns: category in column A and choice in column B. Suppose the source records occupy A2:B100, the user’s category selection is in A2 on a separate entry area, and you want matching choices to appear beginning in D2. Because the source data and selection share a column in this simplified example, put the source records on a separate sheet or adjust the references to match your workbook.

With category labels in Data!A2:A100, corresponding choices in Data!B2:B100, the selected category in Entry!A2, and a helper cell in Entry!D2, enter this formula in D2:

=SORT(UNIQUE(FILTER(Data!$B$2:$B$100,Data!$A$2:$A$100=Entry!$A$2,"")))

The formula filters the choice column to rows matching the selected category, removes duplicate choices, and sorts the results. With the filtered results spilling from Entry!D2, select Entry!B2, open Data > Data Validation, choose List, and use =D2# as the Source. The spill operator # refers to the full result range generated by D2, so the drop-down can reflect a different number of matches as the selection changes.

If Excel does not accept a spill reference directly in the Source box, define a name in Formulas > Name Manager that refers to the helper spill range, then use that name as the validation source. For example, with the helper formula in Entry!D2, the defined name can refer to =Entry!$D$2#; set the Data Validation Source to =DependentChoices if that is the name you created. Keep the helper cells clear so the formula has room to spill. A nonblank cell in its spill area causes a spill error, and an empty match produces no useful choices.

Designing for multiple entry rows

A common next step is copying the dependent lists down a form. The named-range method can often be applied row by row: if each row’s validation formula refers to its own first selection, B2 should refer to A2 and B3 to A3. Check the cell references after copying, because an absolute reference such as $A$2 would keep pointing to the first row.

Dynamic helper formulas need more planning. Each row’s filtered results need their own spill area, and separate spill ranges must not overlap. One helper column per entry row may be impractical if the lists vary in length. For a larger form, consider a carefully designed helper sheet, a named formula suited to your workbook, or the named-range approach if the category structure is stable. Test several rows at once rather than checking only the first one.

Keep the source values in a clean, consistent structure. Extra spaces, inconsistent spelling, or category labels that differ by even one character can result in an empty or incorrect dependent list. Converting the source data to an Excel Table can make ongoing data entry easier, although the precise validation references still need to be set up to suit the workbook.

Common problems and how to fix them

  • The second list is empty or reports an invalid source. Check that the first cell’s value matches a named range after the formula’s substitution, and verify the name’s spelling and scope in Name Manager.
  • A category has spaces or punctuation. The example formula replaces spaces only. It does not remove hyphens, slashes, ampersands, or other characters. Rename ranges to follow a consistent convention and use a mapping method if labels cannot be converted directly.
  • The dependent list shows choices from the wrong category. Confirm the formula points to the intended selection cell. When applying validation to many rows, use a relative row reference so each row follows its own selection.
  • A dynamic formula displays a spill error. Clear cells in the expected spill area and check that the helper formula is not blocked by another result. Also confirm that the formula’s ranges include the relevant source records.
  • The first selection changes but the old second selection remains. Data Validation offers choices; it does not clear a value that was already entered. After changing the category, the user may need to reselect the dependent value. You can flag mismatches with a separate check or conditional formatting.

Also distinguish typing from pasting. A Data Validation error alert can discourage or reject invalid values entered through the cell, depending on its style, but pasted data may bypass validation. If the workbook will receive copied or imported values, add a separate review or checking step rather than treating the drop-down as a complete data-integrity system.

Limits and alternatives

Dependent lists are useful for guided entry, but they add maintenance. Someone must update the source choices, check range names or helper formulas, and account for old selections when a category changes. They can also be awkward when a category has many choices or when the source structure changes frequently.

Alternatives include a single comprehensive list with category labels included in each item, a searchable table with filters, or a form designed for the workflow. A single list is simpler to maintain but may be longer and less focused. A table with filters is better for browsing or reviewing many records, while a data-entry form may be preferable when users need instructions, required fields, or more elaborate checks. Choose based on how people will enter and maintain the data, not just on how the drop-down looks.

Frequently Asked Questions

Can an Excel drop-down list depend on another cell?
Yes. Use Data Validation for the first list, then make the second list’s source refer to the first selection. Named ranges with INDIRECT are a common method; a dynamic helper list is another option in Microsoft 365.

Can I make the dependent list update automatically?
Yes, provided the source and validation setup are designed for it. A FILTER helper formula can recalculate when the selected category or source records change. A value already chosen in the dependent cell is not automatically cleared when the category changes.

Why does INDIRECT return an error in my drop-down?
The text produced from the selected value must identify an existing named range. Check spelling, spaces, punctuation, and the range’s scope in Name Manager. The sample formula changes spaces to underscores but does not transform other characters.

Can I use this in Excel for the web or on a Mac?
The basic Data Validation feature is available across Excel platforms, but the menus and support for editing more involved setups can vary. The steps here use Microsoft 365 for Windows; use the Data tab on Mac, and use desktop Excel if the web interface does not expose the controls you need.

Does Data Validation stop someone from pasting an invalid value?
Not reliably. Validation helps guide normal entry and can display an error alert, but pasted or imported data may not be blocked. Use additional checks if invalid combinations would cause problems.

Related

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.