Excel Data Validation Lists Based on Criteria

To make an Excel drop-down list change according to a criterion, put a FILTER formula in a helper cell, define a name for its spilled results, and use that name as the list’s Data Validation source. In Microsoft 365 for Windows, this is a practical way to create a dependent list—for example, showing only products that match the category selected in another cell.

What a criteria-based validation list does

Data Validation can restrict a cell to a list of permitted entries. A criteria-based list adds a relationship: the options depend on a value elsewhere in the workbook. If a user selects “Fruit” in a category cell, the next drop-down might offer only “Apple,” “Pear,” and “Orange,” rather than every item in the source data.

This is often called a dependent drop-down. It is useful in order forms, intake sheets, inventory records, and reports where one answer narrows the choices for a later answer. It can reduce typing differences and invalid combinations, but it does not automatically update a previous selection if the criterion changes. A cell that already contains an item may still show that item after its category changes; the user must replace it if it is no longer appropriate.

Excel’s built-in list validation does not itself filter a source table by criteria. The usual pattern is to calculate the matching options in a helper range, then point validation at those results.

Prepare the source data

Start with a tidy source list: one header row, one record per row, and columns for the criterion and the option to show. For example, use headers Category and Item, with rows such as Fruit / Apple and Fruit / Pear. Avoid merged cells and blank rows inside the data.

Convert the source data to an Excel table by selecting it and choosing Insert > Table, or pressing Ctrl+T on Windows. Confirm that the table has headers. On the Table Design tab, set its name to ItemTable. Structured references make the formula easier to read, and the table expands when new source rows are added.

For the example below, the entry sheet has a selected category in B2. The source table is named ItemTable and contains columns named Category and Item. A separate worksheet named Lists will hold the helper formulas. Change those names or cell references to match your workbook.

Create the dependent options with FILTER

On the Lists sheet, enter the following formula in G2. With the table named ItemTable and its columns named Category and Item, it returns the distinct, alphabetized items whose category matches Entry!B2, excluding empty source items:

=SORT(UNIQUE(FILTER(ItemTable[Item],(ItemTable[Category]=Entry!$B$2)*(ItemTable[Item]<>""),"")))

The formula uses FILTER to keep matching rows, UNIQUE to remove repeated item names, and SORT to make the choices easier to scan. The multiplication combines two conditions: the category must match, and the item must not be blank. The final argument makes FILTER return an empty string if there are no matches instead of displaying a #CALC! error. If your entry sheet has a different name, update the reference; a sheet name containing spaces needs single quotes, as in 'Entry Form'!$B$2.

These functions are available in Microsoft 365 and Excel 2021 or later. The results spill into cells below G2 automatically, so keep that area clear. If another value blocks the spill range, Excel displays a spill error and the list cannot be used as intended.

Connect the spill range to Data Validation

Data Validation’s Source box expects a list source. A workbook-level defined name is a convenient bridge between the helper formula and the drop-down, particularly when the helper is on another sheet.

In Excel for Windows, choose Formulas > Name Manager > New.
Enter ItemOptions as the name and leave its scope as Workbook.
In Refers to, enter =Lists!$G$2#, then confirm. The # symbol means the entire spill range beginning at G2.
Select the cell where the dependent drop-down should appear. Choose Data > Data Validation.
On the Settings tab, set Allow to List. In Source, enter =ItemOptions, then select OK.

Click the validated cell to check that its arrow displays the filtered options. If the sheet name contains spaces, use a quoted reference in the name’s Refers to field, such as ='Helper Lists'!$G$2#. The data-validation source can still be =ItemOptions.

For the first drop-down, you can also build a dynamic list of categories. With the same table and its Category column, put this formula in a clear helper cell:

=SORT(UNIQUE(FILTER(ItemTable[Category],ItemTable[Category]<>"")))

Define a workbook name such as CategoryOptions that refers to that formula’s starting cell followed by #, then set the category cell’s Data Validation Source to =CategoryOptions. Select a category there, and the dependent list will recalculate from the value in Entry!B2.

Adapt the criteria to your workbook

The condition does not have to be a category. It can compare a source-table column with a department, region, project code, or status. For instance, if the selected department is in B2, the filter condition can compare the source table’s Department column with that cell. Keep the criterion cell reference aligned with the actual layout.

For a simple one-condition list, the core pattern is “return values where this column equals that cell.” More elaborate conditions can combine tests. In Excel formulas, multiplying TRUE/FALSE tests acts like AND, while adding them can act like OR. Check the intended logic carefully: adding conditions may include rows meeting either test, while multiplying them keeps rows meeting both.

If you want a separate dependent list for every row of an entry table, a single helper formula tied to B2 is not enough: it follows only that one criterion cell. Each row needs a corresponding helper result and validation source, or a different design such as fixed named ranges for a small, stable hierarchy. Plan the layout before copying validation down, since relative references and helper ranges must correspond to the correct row.

Choose between dynamic formulas and fixed lists

For a growing source table and criteria that can change, FILTER with a spill range is usually easier to maintain than manually editing each validation list. New table rows are considered by the structured reference, and the spill result adjusts when the criterion changes. The defined name keeps the validation source readable.

For a short, rarely changed set of options, typing comma-separated values directly into Data Validation’s Source may be simpler. That approach is not a good fit for a long list or a list that depends on another cell: the entries must be maintained manually, and the source does not filter itself.

Another familiar approach uses named ranges for each group and INDIRECT to choose a range based on the first selection. It can suit a small, fixed hierarchy, but names must be maintained as groups change, and the text used in the first list must map cleanly to valid range names. A FILTER-based helper is generally more adaptable for ordinary table data. It does require a version with dynamic-array functions.

Common problems and useful checks

The drop-down is empty: Check that the criterion exactly matches a value in the source table. Extra spaces, different spelling, or a criterion with no matching rows can leave no choices.
Excel shows a spill error: Clear cells in the expected spill area below the formula. Spilled arrays cannot overwrite existing content.
The name is not accepted: Check that the name exists in Name Manager, its scope is Workbook, and Refers to includes the # spill operator. In Data Validation, use the name with a leading equals sign.
The list includes unexpected blanks: Confirm that the source formula excludes empty items and that the table does not contain formulas returning empty strings that your conditions fail to exclude.
An old value remains after changing the criterion: This is normal. Data Validation controls allowed entries when users enter or select values; it does not clear existing cell contents. Review the dependent cell when its criterion changes.
Users can type a different value: In Data Validation, review the Error Alert tab and keep the alert enabled with a Stop style if entries outside the list should be blocked. Validation is not a security boundary; pasted or programmatically written data may need separate checking.

If the list is a critical part of a shared workbook, test the actual workflow: change the criterion, inspect the available choices, try an invalid typed value, and confirm how blanks and no-match cases behave. Protecting a sheet can affect editing, so verify that users can still make permitted selections.

Excel version and platform notes

The instructions use Excel for Microsoft 365 on Windows. FILTER, SORT, and UNIQUE are available in Microsoft 365 and Excel 2021 or later; older perpetual versions may not calculate these functions. In an older version, use fixed lists or a formula-and-name design supported by that version, and expect more maintenance.

Excel for Mac has Data Validation and defined names, although ribbon placement can differ by release. The formulas and spill-reference idea are the same in supported versions. Excel for the web can work with many validation lists, but complex workbook setup and feature availability may vary; if the spill-based source does not behave as expected in the browser, create or troubleshoot it in the desktop app.

Frequently Asked Questions

Can I use FILTER directly in the Data Validation Source box?
Usually, put FILTER in a worksheet cell first and use a defined name referring to its spill range as the source. This separates the calculation from validation and is easier to inspect and maintain.

Why does my dependent drop-down not change?
Confirm that the helper formula refers to the correct criterion cell and that the validation source points to the name referring to the spill range. Also check that the criterion has matching source rows. A previously selected value is not cleared just because the options changed.

Can a list be based on more than one condition?
Yes. FILTER can use a combined include test, such as multiplying conditions that must both be true. Make sure each condition returns a compatible array and test cases where one or both conditions have no matches.

Can I use this method in older Excel?
FILTER, SORT, and UNIQUE require Microsoft 365 or Excel 2021 or later. Earlier versions need another approach, such as fixed lists or carefully maintained named ranges; they do not support this exact dynamic-array formula pattern.

Does changing the first choice erase the second choice?
No. Data Validation does not automatically delete a value that is already in the dependent cell. Users should reselect an appropriate option, or a workbook may need a separate process to identify stale combinations.

Can users enter values that are not in the list?
A Stop-style validation error alert can block ordinary invalid entries, but validation should not be treated as data security. Pasted or imported values can require additional checks, especially in shared or business-critical workbooks.

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.