Excel Data Validation List From a Named Range

To make an Excel drop-down list from a named range, first define a name for the cells containing the choices, then enter that name in the Data Validation Source box with an equals sign, such as =DeptList. In Excel for Microsoft 365 on Windows, select the cells for the drop-down, choose Data > Data Validation > Data Validation, set Allow to List, and use the named range as the source.

What a named-range drop-down does

Data validation can limit what a person enters in a cell. With Allow set to List, Excel displays a drop-down arrow in the selected cell and lets the user choose from a set of permitted entries. A named range gives that set of entries a readable name. Instead of putting a long cell address in the validation rule, you can refer to a name such as =DeptList.

For example, suppose a worksheet named Lists has department names in A2:A5: Sales, Support, Finance, and Operations. If you define the name DeptList to refer to those cells, you can use =DeptList as the validation source for cells on another worksheet. The name acts as a reference to the cells; it does not copy or store the department names separately.

This is especially useful when the choices live on a separate worksheet, when several cells or sheets need the same list, or when you want to make the validation rule easier to understand and maintain. A name can also refer to a changing range, although the way you set that up matters.

Create the named range

For a straightforward list, put the choices in one column or row, preferably with a heading above them. Keep the actual entries together without blank cells between them. Then create a name that refers only to the entries, not the heading.

Select the cells containing the choices, such as Lists!A2:A5.
Choose Formulas > Define Name. You can also open Formulas > Name Manager and select New.
Enter a name such as DeptList. Names cannot contain spaces; use an underscore or capitalize words if that makes the name easier to read.
Check that Scope is Workbook if you need to use the name from multiple worksheets. In Refers to, confirm the correct sheet and cell range.
Select OK, then close Name Manager if it is open.

Excel may display a sheet reference with the worksheet name and an exclamation mark in the Refers to box. That is normal. The source entered later in Data Validation is the name, not the underlying address. Names are not case-sensitive, but spelling must match. Avoid naming a range with text that could be confused with a cell reference, such as Q1.

Apply the name in Data Validation

Select the cell or range that should have the drop-down. To apply one rule to several cells, select the whole destination range before opening the dialog. Then use these settings:

Choose Data > Data Validation > Data Validation.
On the Settings tab, set Allow to List.
In Source, type the equals sign followed by the name, for example =DeptList.
Leave In-cell dropdown selected if you want the arrow to appear in the cell.
Select OK to save the rule.

Click a validated cell to see the arrow and select an item. The rule can also include an Input Message, which appears when the cell is selected, and an Error Alert, which can warn or prevent someone from entering a value that is not on the list. For a controlled entry field, keep the error alert enabled and use Stop as the alert style. A drop-down is a convenient way to choose; the validation rule is what checks entries.

If the source list is on another worksheet, a workbook-scoped defined name is a convenient way to reference it. Enter the name in Source rather than trying to type a cross-sheet cell reference directly. Excel’s validation dialog can reject some direct references to another sheet, while a defined name provides a supported reference to the list.

Choose the right name scope

Excel names can have workbook scope or worksheet scope. A workbook-scoped name is available throughout the workbook and is usually the best choice for a shared drop-down list. A worksheet-scoped name is available only on the sheet named in its scope. If a name exists with both scopes, the local sheet name can take precedence on that sheet, which can make a validation rule appear to use the wrong list.

When a name does not work, open Formulas > Name Manager and check its Scope and Refers to columns. Confirm that the name refers to the intended cells and that it has not been deleted or replaced. If users will copy the worksheet to another workbook, check the name again after copying; a local name or a reference to the original workbook may not behave as expected.

Keep the choices current

A name that refers to a fixed address, such as Lists!$A$2:$A$5, continues to cover those same cells. Typing a sixth choice in A6 will not automatically make it part of that range. You can edit the name in Name Manager whenever the list changes, but that creates an extra maintenance task and can leave the drop-down out of date.

For a list that grows over time, an Excel Table is often a practical way to manage the source entries: select the list and choose Insert > Table, then add new choices in the next table row. Because Data Validation’s Source box does not accept a table’s structured-reference syntax in the same way as an ordinary cell formula, use a defined name as an intermediary. For example, you can define a name called DeptList that refers to a range sized to include the table’s entries. Review the name’s Refers to value after changing the table setup so it covers the intended data and not the heading.

Another option for Microsoft 365 is a spilling list. If a formula in a cell returns a changing set of choices, it can spill into the cells below. Define a workbook name that refers to the spill range, then use that name in Data Validation. With the formula results starting in Lists!A2, the defined name’s Refers to entry can be =Lists!$A$2#; use =DeptList in the validation Source box. The # reference means the cells in the formula’s spill range. This method requires a version that supports dynamic arrays, and the spill area must be clear so results can expand.

Choose the approach based on how the list changes. Fixed ranges are simplest for stable choices. A carefully maintained table-based name suits lists that people add to manually. A spill-based name suits a list produced by a formula in Microsoft 365. In each case, test the drop-down after adding an item so you know whether the new choice is included.

Common problems and fixes

If Excel reports a problem with the source or the drop-down is empty, check the common causes before rebuilding the rule:

Missing equals sign: In Source, enter =DeptList, not just DeptList.
Name misspelled or unavailable: Check the name in Formulas > Name Manager, including its scope and worksheet reference.
Wrong cells included: Make sure the name points to the entries, not a heading or an unrelated part of the sheet.
New entries are outside a fixed range: Expand the name or change it to a suitable dynamic reference.
Blank cells appear as empty choices: Remove blanks from the source range or revise the source so it covers only populated entries.
In-cell dropdown is off: Reopen Data Validation and select In-cell dropdown on the Settings tab.

Duplicate values in a source range generally appear as duplicate choices; Excel does not automatically make a validation list unique. If duplicates are unwanted, remove them from the source or create a separate cleaned list. Also remember that pasting data can bring values into validated cells without the usual drop-down selection process. Use the error alert and review imported or pasted data if entries must comply with the list.

One frequent misconception is that the drop-down itself prevents every invalid value from entering a cell. It does not. The list provides choices, and the validation rule can display an error when someone types another value, but paste operations and some other editing methods can bypass normal validation prompts. Validation is a useful control for everyday data entry, not a substitute for checking important data.

Named ranges compared with other list sources

For a very short, rarely changed list used in one place, you can type comma-separated choices directly into Source, for example Open,In progress,Complete. This is quick, but it is harder to maintain when the choices change or are shared by several rules. The typed source is also limited in length, so it is not a good fit for a long list.

A named range makes the source easier to reuse and lets the list live on a separate worksheet. A direct cell reference is straightforward when the source is on the same sheet, but it is less readable and less convenient to reuse. A table or formula-generated list can make updates easier, but needs a correctly configured name when used as the validation source. The best choice depends on whether the list is short and static, shared, or expected to grow.

Frequently Asked Questions

Can an Excel data validation list use a named range on another sheet?
Yes. Define a workbook-scoped name that refers to the cells on the other sheet, then enter that name with an equals sign in the validation Source box, such as =DeptList.

Why does Excel say the named range is invalid?
Check for a missing equals sign, a misspelled name, an incorrect scope, or a name that refers to a deleted or invalid range. Open Formulas > Name Manager to inspect the name and its Refers to entry.

Will the drop-down update when I add a new item?
Only if the name refers to a range that includes the new item. A fixed address does not expand by itself. Use a suitable dynamic range or spill-based name, or update the name when the list changes.

Can I use a named range for a dependent drop-down?
Yes. A dependent list uses a second validation rule whose source depends on an earlier selection. This usually requires a separate named range for each set of choices or another carefully designed formula-based setup. Test the names and rules together, especially if names must correspond to selected categories.

Does the method work on Mac or Excel for the web?
The named-range concept and validation source are similar in current Excel for Mac, though ribbon placement and dialog details can differ. Excel for the web supports working with many validation lists, but name-management options can differ from the Windows desktop app. If you need to create or troubleshoot the defined name, use Formulas > Name Manager in Excel for Microsoft 365 on Windows.

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.