Excel Dropdown List Based on Cell Value: How to Set It Up

Yes. In Excel for Microsoft 365, make a dependent dropdown by using the value in one cell to filter a list, then use that filtered list as the second cell’s Data Validation source. For example, select a category in B2 and use a dropdown in C2 that shows only items in that category; a helper formula with FILTER is a flexible way to build the choices.

What a dropdown based on a cell value means

This setup is usually called a dependent dropdown or cascading dropdown. One cell supplies a choice—such as a department, product category, or country—and another cell’s list changes to match it. A category selection of “Fruit,” for instance, can make the next dropdown show “Apple,” “Pear,” and “Orange,” rather than every item in the workbook.

Excel’s Data Validation feature creates the dropdown, but it does not by itself filter a master list according to another cell. You provide a source range or a formula that resolves to valid choices. The key design decision is how to create that changing source: use dynamic-array formulas in current Excel, or use named ranges for a more traditional setup.

Set up a dependent dropdown with FILTER

First, put the source data in two columns with headings Category and Item. Select the data and choose Insert > Table, then give the table a clear name such as Items on the Table Design tab. For this example, the first dropdown will be in B2 and the dependent dropdown in C2. Keeping the source list in a table makes it easier to add records later without manually expanding a fixed range.

In B2, create a normal dropdown containing the available categories. You can use a short range of category names or a separate list of unique categories.
Choose an empty helper cell, such as H2, on the same worksheet. It will display the items allowed by the category currently selected in B2.
In the Data table, ensure each item is paired with its category. For example, Fruit / Apple and Fruit / Pear are separate rows.

With the selected category in B2 and a table named Items containing the columns Category and Item, enter this formula in H2:

=SORT(UNIQUE(FILTER(Items[Item],Items[Category]=$B$2,"")))

FILTER returns items whose Category matches B2, UNIQUE removes repeats, and SORT orders the results. The formula spills its results into cells below H2, so keep that area clear. These functions are available in Microsoft 365 and Excel 2021 or later. If the category has no matching records, the formula returns an empty string as its fallback; check that the source data and category spelling match if the result is blank.

Now select C2 and choose Data > Data Validation > Data Validation. On the Settings tab, set Allow to List. For the Source, enter =H2#, then select OK. The # spill-range operator refers to the full set of results produced by the formula in H2. If Excel does not accept that reference in the validation box, create a workbook-level name in Formulas > Name Manager > New, set its Refers to value to =Sheet1!$H$2# (replace Sheet1 with your actual sheet name), and use =CatChoices as the Source after naming it CatChoices. The name lets validation refer to the spill range without putting a cross-sheet reference directly in the Source box.

Build the first dropdown

The dependent list can only respond to a usable first choice. For a small, manually maintained category list, place the category names in a range such as J2:J5, then select B2 and open Data > Data Validation > Data Validation. Choose List under Allow and set Source to the category range, for example =$J$2:$J$5. This assumes the category choices are in J2:J5 on the same sheet. Select In-cell dropdown if it is not already selected.

If categories are also maintained in the Items table, avoid duplicate category names in the dropdown. In Microsoft 365 or Excel 2021, a helper cell can create a sorted, distinct category list. With the table named Items and its category values in Items[Category], enter this in J2:

=SORT(UNIQUE(Items[Category]))

Use the resulting spill range as the first dropdown’s source, or define a name for that range if the validation dialog does not accept the spill reference. Keep helper formulas somewhere users will not overwrite them, or place them on a dedicated worksheet. You can hide that worksheet, but hiding it is for presentation—not a way to protect the underlying data.

Use named ranges for older or fixed lists

Before dynamic arrays were available, a common approach was to create a separate named range for every category. For example, define a range named Fruit for fruit items and another named Drinks for drink items. Select C2, open Data Validation, choose List, and use a formula that converts the category in B2 into the corresponding range name.

With the category in B2, named ranges called Fruit and Drinks, and spaces in category labels replaced by underscores in the range names, use this as the validation Source:

=INDIRECT(SUBSTITUTE($B$2," ","_"))

SUBSTITUTE changes spaces to underscores, and INDIRECT interprets the resulting text as a range reference. The category labels and range names must correspond exactly after that conversion. Excel names cannot contain spaces and have other naming restrictions, so punctuation or duplicate labels may require extra cleanup. This method is useful for stable lists and older Excel versions, but adding a category means creating and maintaining another named range. It can also be harder to audit than one normalized source table.

Choose the right method

For a workbook used in Microsoft 365 or Excel 2021 and later, a table plus FILTER is often easiest to extend: add another category-item row to the table and the formula can include it. The table keeps related records together, while the helper formula makes the logic visible. A named-range approach may be preferable when the lists rarely change, the workbook must support older Excel releases, or the data is already organized as separate category columns.

Use FILTER and a spill range when you want one source table and changing lists that update as records are added.
Use named ranges with INDIRECT when the set of lists is fixed or compatibility with older Excel is important.
Use a helper sheet when you want to keep formulas and source data out of the way of everyday users.
Use a simple static list if the choices never depend on another selection; a dependent setup adds complexity without benefit in that case.

Excel for the web supports many Data Validation tasks, but the desktop app provides the fullest experience for managing names and workbook features. Excel for Mac has Data Validation and dynamic-array functions in current Microsoft 365 editions, though dialog layouts can differ slightly from Windows. If the workbook will be opened in older desktop Excel, verify the functions and validation source there before distributing it.

Handle changes, blanks, and invalid selections

One important limitation is that changing B2 does not automatically erase an old value in C2. If a user selects Fruit, chooses Apple, and then changes B2 to Drinks, C2 may still display Apple even though Apple is no longer an allowed choice. The dropdown’s available options change, but existing cell contents are not automatically revalidated or cleared. Tell users to reselect the dependent choice after changing the parent, or design the form so they notice the stale value.

Data Validation is a data-entry aid, not a security boundary. Users can sometimes introduce values by pasting, importing, or editing data in ways that do not behave like selecting from the dropdown. For important records, add checks that flag mismatches, and review the data after imports. In the validation dialog, the Error Alert tab can show a message and use the Stop style to reject ordinary typed entries that are not on the list.

Check these common causes when the dependent dropdown is empty or wrong:

The text in B2 does not exactly match the category values in the source table; leading or trailing spaces can cause a mismatch.
The helper formula’s spill area is blocked by existing values or merged cells.
The validation Source points to the helper formula cell but omits the spill reference, or the workbook name points to the wrong worksheet.
The source table or its column headings were renamed, breaking the structured references in the formula.
The workbook is opened in an Excel version that does not support FILTER, UNIQUE, or SORT.

Make the workbook easier to maintain

Give the source table descriptive headings and keep one item-category relationship per row. Avoid arranging the main data with one category per column if you expect categories to grow; a two-column table is easier to filter, sort, and extend. When adding new source records, check that the table expands and that the helper formula’s results include them.

For a polished form, use a short input message in Data Validation to explain what the user should select. You may hide the helper column or move formulas to a support sheet, but document where the source data lives so another editor can maintain it. Test several cases: a category with many choices, one with a single choice, and one with no matching items. Also test changing the parent after a child has already been selected.

Frequently Asked Questions

Can I make a dropdown list change when another cell changes?
Yes. Use Data Validation for the dropdown and create its source from the value in the other cell. In current Excel, a FILTER formula in a helper cell can return the matching options; a named-range method is another option.

Why does my dependent dropdown show a blank option?
Check for blank item cells in the matching source rows and for categories with no matching items. A FILTER formula using an empty fallback can also produce a blank result when there are no matches. Remove empty source records or handle the no-match case deliberately.

Can I use a dropdown list from another worksheet?
Yes. A workbook-level defined name is a reliable way to point Data Validation to a list on another sheet. In Microsoft 365, you can define a name referring to a helper formula’s spill range and use that name as the validation source.

Does changing the first dropdown clear the second one?
No, not automatically. The available choices can update while the old dependent-cell value remains. Ask users to choose the second value again after changing the first, or use an additional process if automatic clearing is essential.

Can I create a dependent dropdown without INDIRECT?
Yes. In Microsoft 365 or Excel 2021 and later, FILTER can return the matching records to a helper cell, and the dropdown can use that spill range. This avoids building a text-based range reference with INDIRECT.

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.