Data Validation List Based on Another List in Excel

Yes. In Excel for Microsoft 365, you can make a dependent data validation list by filtering the options for a helper list, then using that list as the second dropdown’s source. For a classic setup, named ranges and INDIRECT can also make the second list change when the first selection changes.

What a dependent dropdown does

A data validation list limits what a person can enter in a cell to a set of choices. When that set of choices depends on another cell, it is often called a dependent dropdown or cascading dropdown. For example, a user chooses a department in one cell, then sees only the job roles belonging to that department in the next cell.

This is useful for order forms, surveys, inventory sheets, and data-entry templates. It can make entries more consistent and reduce typing errors, but it does not create a permanent relationship between cells: changing the first choice does not automatically erase a second choice that is no longer valid. That behavior is important when designing the sheet.

Build a dependent list with FILTER

This method uses a helper formula to return only the options that match the selected category. The example assumes a worksheet named Lists has categories in A2:A100 and corresponding items in B2:B100. On a worksheet named Input, A2 is the category dropdown, B2 is the dependent dropdown, and D2 is a helper cell. Keep the helper results in an unused area with enough empty cells below for the options to spill.

On Lists, arrange the source as two columns: one category and one option per row. For example, Office Supplies can appear beside Paper, Pens, and Folders; Food can appear beside Fruit and Bread.
Create the first dropdown in Input!A2. Select A2, choose Data > Data Validation, set Allow to List, and set Source to the category choices. For a short fixed list, type the choices separated by commas. If the choices are maintained in cells, use a named range for those cells.
In Input!D2, enter this formula. With categories in Lists!A2:A100, options in Lists!B2:B100, and the selected category in Input!A2, it returns all matching options; if there is no match, it returns a blank result: =FILTER(Lists!$B$2:$B$100,Lists!$A$2:$A$100=$A2,"")
Create a workbook-level name for the spill range. Go to Formulas > Name Manager > New. Name it SubcategoryOptions and, in Refers to, enter =Input!$D$2#. The # refers to the entire range produced by the formula in D2. If the worksheet name contains spaces, enclose it in single quotation marks, as in ='Input Sheet'!$D$2#.
Select Input!B2, open Data > Data Validation, choose List, and enter =SubcategoryOptions in Source. The dropdown now lists the options returned by FILTER for the category selected in A2.

In the sample data, if A2 contains Office Supplies, the helper list returns Paper, Pens, and Folders. If A2 changes to Food, it returns Fruit and Bread. FILTER is available in Microsoft 365 and Excel 2021 or later. The helper formula must have room to spill; any value in its output area can cause a #SPILL! error. Keep the helper cells clear, or put them on a separate worksheet.

Use named ranges for a classic setup

Named ranges with INDIRECT are a widely used alternative, especially in older workbooks that do not have dynamic array functions. Make a separate range for each category’s options, then name each range after its category. For example, name a range containing Paper, Pens, and Folders Office_Supplies, and name the Food options Food. Excel range names cannot contain spaces, so replace spaces in category labels with underscores.

Assume the parent dropdown is in A2 and the named range for each category uses the category text with spaces replaced by underscores. In the child cell’s Data Validation Source box, use this expression: =INDIRECT(SUBSTITUTE($A2," ","_")). It converts a selection such as Office Supplies into Office_Supplies, then INDIRECT treats that text as the name of the list range. Set up each named range through Formulas > Define Name or Formulas > Name Manager.

This approach is straightforward for a small, stable set of categories. It requires a matching named range for every category, however, and punctuation or labels beginning with numbers can make naming awkward. The text conversion in the example replaces spaces only; it does not automatically clean hyphens, slashes, or other characters. If category labels change, update the names or the mapping logic as well.

Choose the method that fits the workbook

FILTER is generally easier to maintain when options are stored in a central two-column table and categories may change. Add a new category-option row to the source data and the formula can include it, provided the referenced range covers that row. Converting the source into an Excel Table can help keep data organized, though the formula and data validation setup still need to point to suitable ranges.

Named ranges with INDIRECT are useful when a workbook already uses them or when compatibility with older Excel versions matters. They are less convenient when there are many categories, since each one needs a correctly named range. A fixed list typed directly into the Source box is simplest for a handful of permanent choices, but it is not a true dependent list and becomes cumbersome to revise.

Choose FILTER when the source is tabular, changes over time, and users have a compatible Excel version.
Choose named ranges and INDIRECT when supporting older workbooks or following an existing named-range design.
Choose a typed list only for a small, unchanging set of choices that does not need to depend on another cell.

Make the setup work across rows

The example creates a dependent list for one row: A2 controls B2. For an entry form with many rows, each child cell must refer to the parent selection on its own row, and each helper result needs its own non-overlapping space. Simply copying the one-row setup may leave several cells pointing to the same helper spill, so test the copied validation rules before distributing the workbook.

For a small form, create a helper formula for each row and arrange the helpers so their spill ranges cannot collide. For a larger form, named ranges per category can be easier to copy down because the source expression can use a relative row reference such as $A2. Check the references after copying: a child cell on row 3 should depend on A3, not remain tied to A2. Excel Tables are convenient for the entry area, but dynamic-array spill formulas should be placed outside a table rather than inside its calculated columns.

If you want the first dropdown’s choices to update automatically as categories are added, use a maintained category list or a dynamic-array formula to create that list, then name or reference its output appropriately. Avoid duplicate or blank category labels in the source data unless they are intentional; duplicates can make dropdowns confusing even if the filtered result is technically correct.

Handle changes, blanks, and invalid entries

Data validation controls new entries made through the dropdown, but it does not automatically clear an existing child value when its parent changes. If a user selected Paper and then changes the category from Office Supplies to Food, Paper may remain in the child cell even though it is no longer an allowed choice. Tell users to reselect the child value after changing the parent, or clear the child cell as part of the workflow.

In the Data Validation dialog, the Error Alert tab lets you configure a message when someone enters a value that is not allowed. A Stop-style alert is useful when the sheet should reject invalid typed entries. It is not a security feature: users may paste data into cells, and validation can be removed or bypassed. For important records, use additional checks to flag values that do not match the current parent selection.

The FILTER formula’s third argument in the example returns an empty string when no category matches. That avoids a #CALC! error from an empty result, but a blank helper result can still leave the child dropdown with no meaningful choices. Check that the parent cell is filled and that its spelling matches the category values in the source list. Leading or trailing spaces and inconsistent labels are frequent causes of apparent mismatches.

Common problems and fixes

The child dropdown is empty: Confirm the parent selection matches the source category exactly, the named range refers to the intended cells, and the FILTER helper is not returning an error.
The helper shows #SPILL!: Clear cells in the spill area. The formula needs open cells to display every matching option.
Excel rejects a cross-sheet source: For a list located on another worksheet, use a workbook-level defined name as the validation source rather than typing a direct reference to the other sheet in the Source box.
The dropdown shows old or wrong options: Check whether the name refers to the correct helper spill and whether the validation rule points to that name. Also check for duplicate category labels.
A copied row still depends on the wrong parent: Edit or inspect the validation source and helper formula references. A relative row reference should change with the row; an absolute reference such as $A$2 will always point to A2.

When a validation dropdown seems missing, select the cell and reopen Data > Data Validation to inspect its settings. Confirm that Allow is List and that In-cell dropdown is enabled. In Excel for Mac, the relevant commands are also on the Data and Formulas tabs, though dialog layouts and keyboard shortcuts can differ. Excel for the web supports common validation tasks, but managing named ranges and spill-based setups may be easier in the desktop app.

Frequently Asked Questions

Can a data validation list refer to another worksheet?
Yes, but Excel commonly requires the other-sheet range to be exposed through a defined name. Create the name in Formulas > Name Manager, then use that name in the validation Source box. This also makes the source easier to read and maintain.

Can I use FILTER directly in the Source box?
It is more reliable to put FILTER in a worksheet helper cell, name the spill range with the # operator, and use the name as the Source. This separates the formula that creates the choices from the validation rule and makes errors easier to inspect.

Does changing the first dropdown clear the second one?
No. Data validation restricts permitted entries but does not automatically erase a value already present in the dependent cell. Clear or reselect that value when the parent choice changes, or add a separate process to identify stale entries.

Will this work in older versions of Excel?
The FILTER method requires Excel 2021 or Microsoft 365. Older versions can use named ranges with INDIRECT, provided the category labels can be mapped reliably to valid range names. Test the workbook in the oldest version its users need.

Can I use this with more than two dropdown levels?
Yes. Each additional level can depend on the previous selection, but each level needs a source list or helper logic that narrows the choices further. Plan the references and blank behavior for every level, and test what happens when an earlier selection changes.

Keep the source and rules maintainable

Whichever method you choose, keep source choices in a clearly labeled area, avoid mixing unrelated lists, and document how category labels map to named ranges if you use INDIRECT. Test representative selections, including categories with one option, several options, and no matching rows. Then test changing a parent after a child has already been selected. These checks catch the most common surprises before other people rely on the workbook.

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.