Excel Data Validation: Create a Filtered Dropdown List

Yes. In Excel for Microsoft 365, create a helper formula that returns the matching items with FILTER, then point Data Validation to that spilled range through a defined name. This creates a dropdown that changes when the selection used as the filter changes; it is different from simply showing the visible rows of an AutoFilter.

What “based on a filtered list” can mean

People use “filtered list” for two related but distinct tasks. The first is a dependent dropdown: choosing a category, department, or other criterion changes the options in another cell. For example, choosing “Fruit” in a category cell might make the next dropdown offer only Apples and Pears. This is the most reliable interpretation for a data-entry form, and it is the method explained below.

The second task is to make a dropdown contain only the rows currently visible after applying an ordinary Excel table or range filter. Data Validation does not automatically read the visible rows of an AutoFilter. Filtering a source table hides rows, but that alone does not guarantee that hidden entries disappear from a validation dropdown. If the list must respond to a category choice, build the filtered results from that choice. If it must track manually hidden rows, additional helper logic or a different design is needed.

In either case, Data Validation checks the value entered in a cell; it does not dynamically edit the source data. A dropdown helps users choose an allowed value, but pasting data into a cell can bypass the dropdown interaction, so validation should not be treated as a complete data-security control.

Set up the source data and filter criterion

Start with a clean source list. Put each item on its own row, and keep the category and item fields in separate columns. Convert the range to an Excel table with Insert > Table, and give the table a useful name from Table Design > Table Name. For the example, the table is named tblItems and has columns named Category and Item.

Suppose the source table contains “Fruit — Apples,” “Fruit — Pears,” and “Vegetables — Carrots.” On a form sheet, use B2 for the category selection and B3 for the dependent item dropdown. Put a helper formula in H2 on a sheet named Lists. The helper can be placed elsewhere, but leave enough empty cells below it for the results to spill.

Use consistent category spelling and avoid blank rows inside the source data.
Keep the helper spill area clear of existing values, merged cells, and other obstructions.
Use an Excel table so newly added source rows are included in the structured references.

Create the changing options with FILTER

With tblItems containing the Category and Item columns, and the chosen category in the form cell B2, enter this in Lists!H2:

Cell layout: the source table is named tblItems, its columns are Category and Item, and the selected category is in the form sheet’s B2. In Lists!H2, use:

=FILTER(tblItems[Item],(tblItems[Category]=Form!$B$2)*(tblItems[Item]<>""),"")

Replace Form with the actual name of the sheet containing B2. If B2 contains Fruit and the table has the two fruit entries above, the formula returns Apples and Pears in H2 and the cells below it. The multiplication combines the two tests: the category must match and the item must not be blank. The final empty-text argument makes FILTER return a blank result rather than a #CALC! error when nothing matches.

FILTER is available in Microsoft 365 and Excel 2021 or later. In Excel for Microsoft 365, its results spill into neighboring cells automatically. If the result shows a #SPILL! error, clear the cells in the intended output area and check for merged cells or other obstructions. If the formula returns no options, check that the category text in B2 exactly matches the source values, including spaces.

Connect the spill to Data Validation

Excel’s Data Validation list source is designed to accept a range reference, a comma-separated list, or a defined name. A direct FILTER formula is not a dependable choice in the Source box. Instead, define a name that refers to the helper formula’s spill range, then use that name as the list source.

In Excel for Microsoft 365 on Windows, choose Formulas > Name Manager > New.
Enter Options as the name. Set Scope to Workbook if the validation cell is on a different sheet from the helper.
In Refers to, enter =Lists!$H$2#, adjusting the sheet and cell to match the helper formula’s location. The # refers to the full spilled range, including changes in its length.
Select the cell that should contain the dropdown, such as Form!B3. Choose Data > Data Validation.
On the Settings tab, choose List under Allow. Enter =Options in Source, keep In-cell dropdown selected, and select OK.

When the category in B2 changes, FILTER recalculates, the spill range changes, and the dropdown uses the updated options. If the defined name is scoped to a particular worksheet instead of the workbook, it may not be available from a validation cell on another sheet; workbook scope avoids that cross-sheet naming issue.

Make the dropdown easier to maintain

Using a table and a spill reference makes the setup less brittle than typing the list entries directly into the Data Validation Source box. A typed list is quick for a few fixed values, but it has a practical length limit and must be edited whenever choices change. A helper formula also makes the filtering rule visible and easier to review.

Consider whether duplicate items should appear more than once. The basic FILTER formula returns every matching row, including duplicate item names. If repeated options are undesirable, a formula can combine FILTER with UNIQUE and SORT. These functions are available in Microsoft 365 and Excel 2021 or later. However, if the same item name legitimately belongs to multiple records, removing duplicates may hide a distinction that matters to the user.

For a larger workbook, use descriptive names for the helper range and the validation list, document which cells contain the criterion and helper, and keep the helper sheet protected from accidental edits if appropriate. Protection can help prevent routine changes, but it is not a substitute for keeping a backup or controlling access to sensitive workbook contents.

Filtered table rows versus a dependent dropdown

A common misconception is that the funnel buttons on a table automatically control a dropdown elsewhere. They do not. An AutoFilter changes which source rows are displayed, while Data Validation uses the reference or list supplied in its Source setting. Those are separate mechanisms. A helper formula driven by the selected category is usually preferable because its criteria are explicit and repeatable.

If the actual requirement is “show only the rows currently visible after I filter this table,” identify that requirement before building the form. A formula based on a criterion cell may not follow a user’s manual AutoFilter choices. Visibility-aware formulas can be more complicated and can depend on how the source is arranged. For routine data entry, it is often simpler to use a dedicated criterion cell and let FILTER produce the options, rather than relying on a user to remember to filter the source table first.

Another useful distinction is between filtering the options and filtering records after entry. A dependent dropdown limits what a user can select at that moment. It does not automatically update existing selections if the category later changes. For example, a user might select Apples under Fruit, then change the category to Vegetables. The existing item cell may still contain Apples. Add a separate check or conditional formatting rule if the workbook must flag such mismatches.

Common problems and fixes

The Source box rejects the reference: Check that the name exists in Formulas > Name Manager, that its Refers to entry points to the helper’s spill range, and that the validation source is entered as =Options.
The dropdown is empty: Confirm that the helper formula returns results, that the selected category matches the source spelling, and that the named range points to the correct sheet and cell.
The list is stale or incomplete: Make sure the source data is inside the named table and that the formula refers to the intended table columns. Check that calculation is not set to Manual under Formulas > Calculation Options.
A blank choice appears: FILTER’s empty-result argument can produce a blank result when there are no matches. Decide whether that is acceptable, or display a message such as “Choose a category with available items” and prevent users from treating a blank as a real option.
The helper shows #SPILL!: Remove anything occupying the cells where the results need to spill. The formula cell itself must have a clear output area.
Changing the category leaves an old item selected: Data Validation does not erase or revalidate a previous entry automatically. Clear the item cell when changing categories, or add a visible validity check.

Version and platform considerations

The FILTER-based method requires a dynamic-array version of Excel: Microsoft 365 or Excel 2021 and later. Excel 2019 and earlier do not include FILTER, so they cannot use this exact setup. Older workbooks may use named ranges, helper columns, or other formulas to create dependent lists, but those approaches require different construction and may be harder to expand.

On Windows, the menu paths above apply to Excel for Microsoft 365. Excel for Mac has Data Validation and defined-name tools as well, though ribbon placement and dialog details can differ by release; use the Formulas and Data tabs to locate Name Manager or Define Name and Data Validation. Excel for the web can work with many existing dropdowns, but authoring and managing names may be more limited or arranged differently than in the desktop application. If you cannot create or troubleshoot the spill-based name in the web interface, set up the name and validation in desktop Excel, then check the workbook in the browser.

Frequently Asked Questions

Can I put FILTER directly in the Data Validation Source box?
Use a helper cell with FILTER and a defined name that refers to its spill range instead. Data Validation’s Source field is intended for a list or reference, and the named spill reference is a practical way to connect it to a changing array.

Does a dropdown automatically show only visible AutoFilter rows?
No. Applying a filter to a source table does not automatically change a separate validation list to include only visible rows. Use a formula driven by a criterion cell for a dependable dependent dropdown, or design a separate visibility-aware solution if manual row visibility is essential.

Why does my dependent dropdown show the wrong choices?
Check the criterion cell, source spelling, formula references, and the defined name’s Refers to location. Also confirm that the helper formula recalculates and that its spill area is not blocked.

Will the dropdown update when I add another source row?
It should if the new row is added to the Excel table referenced by the formula. The table’s structured references expand with the table, and the helper spill can then return the additional matching item.

Does changing the first dropdown clear the second one?
No. Data Validation changes the available choices but does not automatically remove a value already selected in the dependent cell. Clear that cell when changing the category or use a separate check to flag a value that no longer matches.

Choosing the right approach

For a category-dependent dropdown in Microsoft 365, the helper formula, spill reference, and defined name provide a clear, expandable setup. Use a fixed validation list when the choices are short and rarely change. Choose a different method when the workbook must support older Excel versions or must track rows hidden by a user’s AutoFilter. The key is to decide what “filtered” means first: matching a selected criterion and displaying only currently visible table rows are not the same requirement.

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.