Excel Data Validation List Formula: Examples and Fixes

For an Excel drop-down list, select the cells, choose Data > Data Validation, set Allow to List, and enter a source such as =$D$2:$D$5 or a named range such as =DepartmentList. For a changing list in Microsoft 365 or Excel 2021, create the list with a formula in a helper cell and use its spill reference, for example =$G$2#, as the validation source.

What the list formula does

Data validation controls what users can enter in selected cells. With Allow set to List, Excel displays a drop-down arrow in each selected cell and offers the values supplied by the Source box. The source can be a short list of typed choices, a worksheet range, or a defined name that refers to a range. In other words, the formula identifies where Excel should get the choices; it is not usually a formula typed into the cell that will contain the selection.

That distinction matters. A cell with a validation list stores the selected text, such as “Operations,” rather than the formula that built the menu. The source formula also does not automatically update old entries if the underlying choices change. Validation limits future entries; it is not a guarantee that every existing value in a workbook is valid.

Set up a basic drop-down list

In Microsoft Excel for Microsoft 365 on Windows, the standard setup is:

Put the allowed choices in a single column or row, or decide on a short typed list.
Select the cell or cells that should have the drop-down.
Choose Data > Data Validation.
On the Settings tab, choose List in the Allow box.
Enter the list or reference in Source, keep In-cell dropdown selected, and choose OK.

For example, with department names in D2:D5, enter =$D$2:$D$5 in Source. The dollar signs keep the range fixed if the validation rule is copied to other cells. On Excel for Mac, use Data > Data Validation; the dialog layout can differ slightly. Excel for the web also supports list validation, though some workbook and dialog controls can differ from the desktop application.

In the dialog, Ignore blank allows an empty cell, while the Error Alert tab controls whether Excel warns or blocks an entry that is not in the list. A Stop-style alert is the strictest option. Even with an alert, users may be able to paste values into validated cells, so validation is useful for guided entry but should not be treated as a complete data-security measure.

Choose between a typed list and a range

A typed list is convenient for a tiny set of stable choices. For example, put Open,In progress,Complete in Source. Excel uses the list separator recognized by the computer’s regional settings, so a comma-separated entry may need a different separator in some locales. A typed source is also limited in length, making it unsuitable for a long or frequently edited menu.

A worksheet range is easier to maintain when choices are numerous or likely to change. With status values in D2:D4, the source is =$D$2:$D$4. Add or edit choices in the source cells rather than reopening the validation dialog. A direct range reference generally needs to be on the same worksheet as the validated cells; for a source on another sheet, a defined name is the usual reliable approach.

Use a typed list when there are only a few permanent options. Use a range or name when another person may need to maintain the choices, when the list is longer, or when the same menu is used in several places. Keeping the source list on a dedicated worksheet can make a workbook easier to audit and update.

Use a named range for a reusable source

A defined name gives a cell range a readable label. To make one, select the source cells, choose Formulas > Define Name, enter a name such as DepartmentList, and confirm the range it refers to. Names cannot contain spaces, so use a form such as Department_List if needed. Then set the validation Source to =DepartmentList.

This is especially useful when the source list lives on a different worksheet or is reused in multiple validation rules. If the department choices are in Lists!A2:A6, define DepartmentList to refer to that range and use the name in the validation rule. If the list grows, update the name’s reference or define it to cover a suitably sized range. A name that refers only to A2:A6 will not include a new value entered in A7 unless its reference is expanded.

Keep names clear and avoid names that could be confused with cell references. You can review or change workbook names through Formulas > Name Manager. Names are a practical way to separate the user-facing entry sheet from the worksheet that stores source values.

Create a dynamic list with a spill range

For a list that should automatically exclude blanks and update when source values change, use a helper formula in a clear area of the workbook. With choices in A2:A20, enter this in G2:

With the source choices in A2:A20 and an empty area below G2 for the results, enter this formula in G2:

=SORT(UNIQUE(FILTER($A$2:$A$20,$A$2:$A$20<>"")))

Then, in Data Validation’s Source box, use =$G$2#. The # refers to the full spill range produced by the formula in G2. If A2:A20 contains Red, Blue, Red, and a blank, the helper list returns Blue and Red once each, in alphabetical order. When a new nonblank choice is added within A2:A20, the result can expand and the drop-down uses the expanded spill.

FILTER, SORT, and UNIQUE are available in Microsoft 365 and Excel 2021. Keep the cells where the results will spill empty; a value or merged cell in the way can cause a #SPILL! error. The helper formula must return a usable list, and the validation source must point to the spill anchor with the #. If a workbook will be opened in older Excel versions, use a fixed source range or a conventional named range instead, because those versions do not support these dynamic-array functions.

Build a dependent drop-down

A dependent list changes according to a choice made in another cell, such as showing different product options for each selected department. One straightforward approach is to create a separate named range for each parent choice. If E2 contains a department and there is a named range for each matching department’s products, set the validation Source for the dependent cell to =INDIRECT($E2). Applied down a column, the row reference adjusts so each row uses its own E-cell selection.

This approach depends on the selected text matching a valid defined name. Since names cannot contain spaces, labels such as “Field Support” need a compatible naming convention, such as Field_Support, plus a way to translate the displayed label to the name. INDIRECT interprets text as a reference and can be less convenient to maintain than a simple range list. For a complex dependent menu, test changes to parent choices and names carefully, and consider a helper-sheet design rather than building a long formula into the validation box.

Common problems and practical fixes

The drop-down arrow is missing: Reopen Data > Data Validation and confirm Allow is List and In-cell dropdown is selected. The arrow commonly appears when the validated cell is selected.
A source on another sheet is rejected: Define a name for the source range and use =DepartmentList rather than entering a direct cross-sheet reference in the Source box.
New choices do not appear: Check whether the validation source includes the new cells. A fixed reference such as D2:D5 will not grow when a value is entered in D6.
Blanks appear in the menu: Remove blank cells from the referenced range or use a dynamic formula that filters out empty values. A range containing gaps can produce blank choices.
The formula produces an error: Check that its spill area is clear, the referenced cells exist, and the functions are supported by the Excel version in use.
Invalid values still appear: Validation can be bypassed by pasting or by other editing methods. Use a suitable Error Alert and review or clean existing data when accuracy matters.

Another common misconception is that a list formula changes the content of cells already filled in. It does not. If a choice is renamed or removed from the source, existing entries remain as they were until they are edited. To check entries against a current list, use Excel’s validation error alerts during input and inspect existing data separately.

Which source method should you use?

For a one-off menu of a few fixed choices, a typed list is fastest. For a maintained list, use a worksheet range. For a source used across sheets or multiple rules, define a name. For a list that should sort, remove duplicates, or omit blanks automatically, use a dynamic-array helper formula and point validation at its spill range. Dependent lists are appropriate when a second menu genuinely needs to respond to a first choice, but they require more setup and maintenance.

Consider who will maintain the workbook, whether choices change often, and which Excel versions recipients use. A dynamic formula can be elegant in current Excel but may be less portable to older installations. A simple, documented source range can be the better choice for a workbook shared widely. Whatever approach you pick, keep the source values in a predictable place and name the worksheet or range so another user can find it.

Frequently Asked Questions

Can I put a formula directly in the Data Validation Source box?
Yes, if it is a supported reference or name, such as =$D$2:$D$5 or =DepartmentList. For a formula that calculates a changing set of values, put the calculation in worksheet cells and refer to the resulting range, such as =$G$2# in supported Excel versions.

How do I use a list from another worksheet?
Define a name for the source cells on the other sheet using Formulas > Define Name, then enter the name with an equals sign in Source, for example =DepartmentList. This avoids the limitation that a direct list source is generally expected to refer to the same worksheet.

Can a validation list update automatically when I add an item?
It can if its source expands with the new item. A fixed range will not expand on its own. Use a dynamic-array formula with a spill reference in Microsoft 365 or Excel 2021, or adjust the named range or source range when the list grows.

Why does Excel accept a value that is not in my list?
Check the Error Alert tab and confirm that an alert is enabled with a blocking style if you want Excel to reject direct entries. Pasting can still bypass validation in some situations, and data already in the cell is not automatically removed when the source list changes.

Can I use a drop-down list with multiple selections?
Standard Data Validation list cells allow one selected value at a time. A multi-selection workflow requires a different design, such as separate cells or custom automation; it is not enabled by adding a formula to the ordinary validation source.

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.