Data Validation List Formula: Excel Examples and Setup

In Excel, a data validation list formula goes in Data > Data Validation > Settings > Allow: List > Source; it tells Excel where to get the dropdown choices. For values stored in cells A2 through A5 on the same sheet, enter =$A$2:$A$5. The right source depends on whether your choices are fixed, stored on another sheet, or meant to update as your data changes.

What a data validation list formula does

Data validation controls what a user can enter in a cell. When you choose List as the validation type and turn on In-cell dropdown, Excel displays the allowed choices in a dropdown. The Source box can point to a cell range, a defined name, or a short list of values typed directly into the box. A formula can also return or identify the source list.

The dropdown helps people choose consistent entries rather than typing variations such as “In progress,” “In-Progress,” and “in progress.” It does not, by itself, guarantee that every existing cell value is valid: a value already in a cell may remain after you change the rule. Validation is an input aid, not a replacement for reviewing or cleaning data.

The word “formula” can be confusing here. A simple range reference is often all that is needed; you do not need a complex calculation to make a dropdown. More advanced formulas are useful when the list should depend on another selection or update from a growing set of source data.

Create a list from cells on the same sheet

First put the choices in a continuous range, with one choice per cell. For example, enter Open, In progress, and Closed in A2:A4. Select the cell or cells that should have the dropdown, then use these steps in Excel for Microsoft 365 on Windows:

Open Data > Data Validation.
On the Settings tab, choose List in the Allow box.
Turn on In-cell dropdown if you want the arrow to appear in the cell.
Enter the source shown below, then select OK.

With the choices in A2:A4 on the same worksheet as the validated cell, enter =$A$2:$A$4 in Source. The dropdown then offers Open, In progress, and Closed. The dollar signs make the range absolute, so it stays fixed if you copy the validation rule to another cell.

You can select the source range with the mouse while the Data Validation dialog is open, rather than typing it. A cell range is usually easier to maintain than a comma-separated list: to change a choice, edit the source cell. Keep the source entries together and avoid blank cells if you do not want an empty choice in the dropdown.

Use a list stored on another worksheet

Excel’s Data Validation Source box generally will not accept a direct reference to a different worksheet in the same way as a same-sheet range. A workbook-level defined name is a dependable way to use a list stored on a sheet named Lists.

On Lists, place the choices in A2:A10.
Go to Formulas > Name Manager > New.
Set the name to DepartmentList, choose Workbook for Scope, and enter the reference below in Refers to.
In the validation Source box, use the defined name shown below.

Assuming the choices are in Lists!A2:A10, set the name’s Refers to field to =Lists!$A$2:$A$10. Then, in the Source box for the validated cell, enter =DepartmentList. The dropdown displays the entries in that range. A workbook-level name can be used from other worksheets; a name scoped to only one worksheet may not work where you expect it to.

Names are also helpful because they make the source easier to recognize later. Use a clear name without spaces, such as DepartmentList or PaymentStatus. To inspect or update it, return to Formulas > Name Manager.

Make a dropdown update with its source data

A fixed reference such as A2:A10 will not automatically include a new entry typed in A11. If the list is likely to grow, consider storing its source data in an Excel table or using a dynamic-array helper list. A helper list is especially useful when you want to remove blanks, eliminate duplicates, or sort the choices.

For a dynamic-array example, assume an Excel table named Products has a column called Category, and the formula will go in H2 on the Lists sheet. In Microsoft 365 or Excel 2021 and later, enter =SORT(UNIQUE(FILTER(Products[Category],Products[Category]<>""))) in H2. The formula filters out blank categories, returns distinct values, and sorts them. If the nonblank categories are Garden, Office, and Travel, the results spill down from H2 in that order.

To use the spilled results as a validation source, create a workbook-level name called CategoryOptions. Assuming the helper formula is in Lists!H2, set the name’s Refers to field to =Lists!$H$2#. The # refers to the full spill range. With the validated cells selected, enter =CategoryOptions in Source. When the table gains a new nonblank category, the helper results and dropdown can update automatically, provided the formula has room to spill and the name still points to the correct helper cell.

Dynamic-array functions such as FILTER, SORT, and UNIQUE are available in Microsoft 365 and Excel 2021 or later. In older Excel versions, you can maintain a source range manually, use a table-based approach supported by that version, or build a helper list with older formulas. Do not assume a formula available in current Excel will work in an older workbook environment.

Create a dependent dropdown

A dependent dropdown changes its choices based on another cell. For instance, a user might choose a product group in B2 and then choose an item from a list belonging to that group in C2. One traditional method is to create a separate defined name for each group and make the group names match the values selected in B2.

Assume B2 contains a group name, the group’s defined name replaces spaces with underscores, and C2 is the dependent dropdown cell. In C2’s Data Validation Source box, enter =INDIRECT(SUBSTITUTE($B2," ","_")). If B2 contains Hot Drinks, Excel looks for a defined name called Hot_Drinks. The dollar sign fixes the column while allowing the row reference to adjust if you copy the rule down.

This method depends on the names being set up correctly. Each referenced name must exist and point to the intended choices; punctuation and other characters in group labels can also make name matching awkward. For a small, stable set of groups, named ranges can be manageable. For a larger or frequently changing set, a helper-formula design may be easier to maintain than creating many names.

Choose between a range, a name, and typed choices

Same-sheet range: Use a direct range reference when the choices are nearby and the list is simple. It is quick to create and easy to inspect.
Defined name: Use a name for a list on another worksheet or when a clear label makes the source easier to maintain. Names can also refer to dynamic results.
Typed choices: Type a short, unchanging set directly into Source when maintaining a worksheet range would be unnecessary. Separate the entries with commas, and remember that the list is then harder to edit centrally.
Formula-driven helper range: Use a helper range when the choices need sorting, deduplication, filtering, or updates from a data table. It takes more setup but makes the logic visible on the sheet.

Typed choices are not the same thing as a formula. For example, entering Yes,No,Not applicable directly in Source gives those three choices; it does not refer to cells. If a choice itself contains a comma, a typed list can be ambiguous, so use cells as the source instead.

Common problems and how to fix them

The dropdown arrow is missing: Reopen Data Validation and check In-cell dropdown. The cell may still have a list rule even when the arrow is hidden.
Excel rejects the source: Check that a range reference begins with an equals sign, that the defined name exists, and that the name’s scope is appropriate. For a source on another sheet, try a defined name rather than a direct cross-sheet reference.
The list contains blanks: Remove blank source cells or use a helper formula that filters them out. A blank in a referenced range may appear as an empty dropdown choice.
New entries do not appear: A fixed range does not expand just because data was added below it. Extend the range or use a table and a dynamic helper list.
A dependent list shows an error or no choices: Check that the selected parent value corresponds exactly to a defined name, including the space-to-underscore convention in the example.
Invalid values remain in cells: Editing a rule does not necessarily remove old entries. Use the validation error alert to guide future entry, and separately check existing data when consistency matters.

In the Data Validation dialog, the Input Message tab can show guidance when a user selects a cell. The Error Alert tab controls what happens when an entry does not meet the rule. A Stop-style alert blocks an invalid entry through ordinary editing; a warning or information alert can let the user proceed. These settings complement the dropdown but do not change how its source range is defined.

Excel for Mac and Excel for the web

The instructions above target Excel for Microsoft 365 on Windows. Excel for Mac also provides data validation from the Data tab, though dialog layout and some interface details can differ. Defined names and formula compatibility should be checked in the version where the workbook will be used, especially if a list relies on newer dynamic-array functions.

Excel for the web has data-validation features, but its interface and available controls can differ from desktop Excel. If you are creating a workbook with named ranges, dependent lists, or dynamic-array sources, set it up and verify the result in the desktop version that your organization uses. In every version, test the dropdown in the actual cells where people will enter data.

Frequently Asked Questions

What formula do I put in Excel Data Validation List Source?
For a source range A2:A10 on the same sheet, enter =$A$2:$A$10. For a named range, enter an equals sign followed by its name, such as =DepartmentList.

Can a data validation list use another sheet?
Yes. Define a name that refers to the source cells on the other sheet, then use that name in the Source box. This avoids relying on a direct cross-sheet reference in the validation dialog.

How do I make a dropdown list expand automatically?
Use a source design that expands with new data, such as a dynamic-array helper formula and a defined name pointing to its spill range. A fixed reference will not include entries added beyond its last cell.

Can I use a formula for a dependent dropdown?
Yes. One method uses INDIRECT to refer to a defined name based on a selection in another cell. The names must match the selected values or a consistent transformation of them.

Why does my validation list show a blank option?
The source range may contain blank cells, or a formula-generated list may include an empty result. Remove the blanks or build a helper list that filters them out.

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.