Excel Data Validation Lists Based on Formulas

Formulas & functions · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, you can base a Data Validation list on formula-generated choices. The reliable method is to calculate the choices in worksheet cells, then point the list to that range—often through a defined name—rather than trying to put an arbitrary array formula directly in the Source box.

What a formula-based validation list means

Data Validation controls what a user can enter in a cell. For a dropdown, choose the List option and specify where its entries come from. That source can be a typed list, a cell range, or a defined name that refers to a range. A formula can make the range’s contents change as the workbook data changes.

It helps to distinguish two jobs: a worksheet formula calculates the choices, and Data Validation displays those choices. For example, a formula can remove blanks and duplicates from a source column. The dropdown then refers to the cells where that formula returns its results. This arrangement is easier to inspect and troubleshoot than a complicated formula hidden in the validation settings.

A formula in the Source box is not the same as a formula that calculates a list. The Source box is designed to receive a valid list or reference. Some simple references and named formulas work there, but many array calculations—such as filtering a table—are better placed in worksheet cells first.

Choose the right source method

The best approach depends on whether the choices are fixed, change over time, or depend on another selection. Common options include:

  • Typed entries: Suitable for a short, fixed list, such as Yes, No, and Maybe. It is quick to set up, but editing the choices means changing the validation rule.
  • A cell range: Suitable when the choices already exist in a worksheet column. The list is simple to maintain, though a fixed range may include unused blank cells or fail to include later additions.
  • A defined name: Useful when the source is on another sheet or is a dynamic range. A name gives the validation rule a convenient reference.
  • A formula-generated helper range: Useful for unique, sorted, filtered, or otherwise calculated choices. A defined name can refer to the results, including a modern spill range.

For a list that will grow or change, a helper range combined with a defined name is usually the clearest option. For a very short list that rarely changes, typing the entries may be simpler than building a formula-based setup.

Create a dynamic list with a formula

This example takes product names from a source column, removes blank cells and duplicates, sorts the remaining names, and uses the results for a dropdown. It assumes the source data is on a worksheet named Products, in B2:B100, and that the helper formula will go in Lists!F2. Keep the helper cells below F2 clear so Excel has room to display the spilled results.

With product names in Products!B2:B100 and the helper formula starting in Lists!F2, enter this formula in F2:

=SORT(UNIQUE(FILTER(Products!$B$2:$B$100,Products!$B$2:$B$100<>"","")))

In Microsoft 365, FILTER keeps nonblank entries, UNIQUE removes repeated names, and SORT puts the results in order. These functions are also available in Excel 2021. The formula spills its results into the cells below F2. If the source contains Apple, Pear, Apple, and a blank, for example, the visible results are Apple and Pear.

Next, define a name for the spilled range. On Windows, open Formulas > Name Manager, choose New, enter ProductChoices as the name, set the scope to Workbook, and enter the reference below in Refers to. This assumes the helper formula begins at Lists!F2 and the worksheet is named Lists:

=Lists!$F$2#

The # spill operator means the range produced by the formula starting at F2. Now select the cell or cells that need the dropdown and choose Data > Data Validation. Under Allow, choose List; make sure In-cell dropdown is selected; then enter =ProductChoices in Source and choose OK. The name lets the validation rule refer to a list on another worksheet without putting a direct cross-sheet range reference in the Source box.

If you edit the source products, the helper results update and the dropdown uses the updated list. If the spill formula returns an error or cannot expand because cells underneath it are occupied, the dropdown may not behave as expected. Check the helper range before troubleshooting the validation rule.

Build a dependent dropdown

A dependent dropdown changes its choices based on another cell. For example, a user might choose a department in D2 and then choose an employee from that department in a second dropdown. If the source table has departments in A2:A100 and employee names in B2:B100, a helper formula can filter the names for the selected department.

With departments in A2:A100, employees in B2:B100, the selected department in D2, and the helper formula starting in F2, enter this formula in F2:

=SORT(UNIQUE(FILTER($B$2:$B$100,$A$2:$A$100=$D$2,"")))

The formula returns the unique, alphabetized employees whose department matches D2. Define a workbook-level name such as EmployeeChoices that refers to =Lists!$F$2#, assuming the helper is on a sheet named Lists. For the employee dropdown, set Data Validation to List and use =EmployeeChoices as Source. The department dropdown in D2 needs its own list source.

One important detail: changing D2 updates the available employee choices, but it does not automatically erase an employee already selected in the dependent cell. That existing value may no longer be valid for the new department. The user must select a new employee, or you need a separate process to clear the old entry. Data Validation primarily controls new entries; it does not automatically repair every existing cell value when its source changes.

Use a fixed range or older-Excel alternative

In Excel versions without dynamic-array functions, a helper list can still be used, but the formula that prepares unique or filtered results may need a different design. A straightforward alternative is to maintain the allowed choices in a dedicated column and point validation to that range. This is often the better choice when compatibility with older Excel versions matters more than automatic list generation.

A fixed range can include spare cells for future entries, but those empty cells may appear as blank dropdown choices. A defined name can make the reference easier to manage, and an Excel Table can expand as rows are added; however, Data Validation does not always accept a structured table reference typed directly into Source. A common solution is to create a defined name that refers to the intended table column, then use the name in the validation rule. Test the workbook in the Excel versions used by its recipients.

For a formula-generated list in Microsoft 365 or Excel 2021, the helper-range method avoids requiring the dropdown’s Source field to calculate a complex array formula. If you need a workbook to work in older editions, avoid relying on newer functions such as FILTER, UNIQUE, and SORT unless you have confirmed that all users have compatible Excel versions.

Common problems and fixes

  • The Source entry is rejected: Check that it begins with an equals sign when it refers to a defined name, and verify the name in Formulas > Name Manager. A direct formula that returns an array may not be accepted in the Source box; use a helper range and a name instead.
  • The dropdown is empty: Confirm that the helper formula returns values, that its spill area is not blocked, and that the defined name points to the correct starting cell and spill range. Also check for filters or criteria that return no matches.
  • The list contains blanks: Remove empty source entries or adjust the helper formula to exclude them. If the source range includes many empty rows, a formula that filters out blanks can prevent them from becoming choices.
  • New source rows do not appear: The formula may refer to a fixed range that stops before the new data. Extend the source range or use a structured table column as the formula’s input, then confirm the formula still spills correctly.
  • A dependent cell keeps an old choice: Validation does not clear a value simply because the list has changed. Check the selection against the new choices and replace it if necessary.
  • Users can type values outside the list: In Data Validation, review the Error Alert tab. A Stop-style alert can prevent invalid entries through normal editing, while a warning or information alert may let a user proceed. Pasting data can also introduce values that do not match the rule, so validation should not be treated as a complete data-security control.

Excel’s formula separator can vary with regional settings. The examples here use commas, as in the standard English-language syntax; an installation configured for another list separator may display or require semicolons instead. If a copied formula is rejected, check the separator expected by that installation.

Excel for Mac and Excel for the web

The formula and defined-name approach is intended for Microsoft 365 on Windows. Excel for Mac also provides Data Validation and Name Manager, though some ribbon layouts and dialogs can differ. Look for Data > Data Validation and Formulas > Name Manager; the exact placement can vary with the Mac release and window layout.

Excel for the web supports many existing validation lists and common editing tasks, but some workbook features and dialog options differ from desktop Excel. If you cannot create or adjust a particular defined name or formula-backed rule in the browser, open the workbook in the desktop app to set it up. Afterward, check that the list works in the environment where people will use the workbook.

Frequently Asked Questions

Can I put FILTER directly in the Data Validation Source box?
Usually, the more dependable setup is to put FILTER in a worksheet cell, let its results spill, and refer to that range through a defined name. This keeps the list calculation separate from the validation rule and is easier to inspect.

Can a dropdown list use values from another sheet?
Yes. Put the choices on the other sheet and use a defined name that refers to the range or spill range. Use that name, preceded by an equals sign, as the validation Source.

Does a validation dropdown update when its source changes?
It can, provided the source reference includes the new results. A spill-based name follows the spill range as it grows or shrinks. A fixed range cannot include new entries outside its boundaries without being adjusted.

Why does Excel say my formula has an error?
Check the formula syntax, worksheet names, cell references, and list separator. If the error occurs in the Source box, try moving the calculation to a helper cell and using a defined name for the result.

Can I make the second dropdown clear itself when the first selection changes?
Not with Data Validation alone. The rule changes which choices are available, but it does not automatically delete a now-invalid value already in the dependent cell. A user can replace it manually, or a workbook can use a separate automation method.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.