To use a named range for Excel data validation, select the cells that need a drop-down, choose Data > Data Validation, set Allow to List, and enter the range name in Source with an equals sign, such as =ProductList. The named range supplies the choices; data validation turns those choices into a selectable list.
What a named range does
A named range gives a cell or range of cells a readable name. Instead of referring to a list as Lists!$A$2:$A$8, you can call it ProductList. Using that name in a validation rule makes the rule easier to understand and maintain, especially when the source list sits on another worksheet.
The name does not create a drop-down by itself. It identifies the cells that Excel should use as the allowed entries. You still need to apply a List validation rule to the destination cells. Also, the range should contain the actual choices, not a heading such as “Products,” unless you deliberately want that heading to appear in the menu.
Create the source list and name it
Put the allowed values in one continuous column or row. For example, a worksheet called Lists might have product names in A2:A5, with a heading in A1. To define the name in Excel for Microsoft 365 on Windows:
- Select the source cells, such as Lists!A2:A5.
- Choose Formulas > Define Name.
- Enter a name such as ProductList. Names cannot contain spaces; use an underscore if needed, as in Product_List.
- Check that Scope is Workbook if you want to use the name from different worksheets, and confirm that Refers to points to the intended cells.
- Choose OK.
You can also open Formulas > Name Manager to create, inspect, edit, or delete names. In Name Manager, check the Scope and Refers to columns when a name is not behaving as expected. A name with worksheet-level scope is available only on that worksheet and can conflict with a workbook-level name of the same spelling. For a validation list used across the workbook, workbook scope is usually the straightforward choice.
Apply the named range to a drop-down
After defining ProductList, select the destination cells—for example, B2:B100 on an Orders sheet. Then choose Data > Data Validation. On the Settings tab, choose List under Allow, type =ProductList in Source, and leave In-cell dropdown selected if you want an arrow beside each selected cell. Choose OK to apply the rule.
To make Excel show an error when someone enters a value that is not on the list, open the Error Alert tab before choosing OK. Keep Show error alert after invalid data is entered selected, choose a Stop style if invalid entries should be blocked, and write a clear message. The Input Message tab can display a brief instruction when a validated cell is selected, such as “Choose a product from the list.”
You can select a range of destination cells before opening the dialog, so one rule applies to all of them. To inspect an existing rule, select a cell that uses it and open Data > Data Validation. If the selected cells have different rules, Excel may show a warning or make the settings difficult to edit as a group; inspect a representative cell or apply one consistent rule to the intended range.
Keep the list current as choices change
A name that refers to a fixed range, such as A2:A5, includes only those cells. If you add a fifth product in A6, it will not automatically become a choice unless the name is expanded. This is a common reason a drop-down appears out of date.
For a list that grows, one option in Microsoft 365 is to base the named range on a spilled dynamic-array result. For example, if a formula in Lists!A2 produces the choices below it, define a workbook name called ProductList whose Refers to entry is =Lists!$A$2#. Then use =ProductList in the validation Source box. The # spill-reference operator represents the full spilled result, so the named range can follow its changing size. The source formula needs to return a usable list, and the spill area must not be blocked by other cell contents.
Another option is a dynamic name based on a bounded source range. With a heading in Lists!A1 and a contiguous list beginning in A2, create a workbook name whose Refers to entry is =Lists!$A$2:INDEX(Lists!$A$2:$A$100,COUNTA(Lists!$A$2:$A$100)), then use =ProductList as the validation source. This example assumes at least one value in A2:A100 and no blanks within the list. It counts populated cells and uses INDEX to identify the end of the range. If entries have gaps, the count may not identify the last occupied cell correctly; clean up the list or use a different design. The A100 limit is intentional, so increase it if the source may grow beyond that row.
Excel Tables are useful for maintaining expanding data, but a structured reference such as a table column reference is not always accepted directly in the Data Validation Source box. A workbook-level name can act as a bridge, or a spill-based list can provide the choices. Choose an approach that is simple for the people who will update the workbook, and test it after adding or removing source entries.
Use named ranges for dependent lists
A dependent drop-down changes its choices based on an earlier selection—for example, a user chooses a department in A2 and then chooses a role in B2. One traditional method is to create a separate named range for each department’s roles and make the department names match those range names. Names cannot contain spaces, so category labels may need underscores or other cleanup.
With the department selection in A2 and a named range whose name exactly matches that selection, set the validation Source for B2 to =INDIRECT($A2). The dollar sign fixes the reference to column A while allowing the row to adjust when the rule is applied down the sheet. INDIRECT turns the text in A2 into a reference, which is why the text must match a defined name. This approach can be convenient for small, stable lists, but it needs careful naming and can be awkward when labels contain spaces or change often.
Common problems and fixes
- The source is rejected: Check that the name exists, is spelled correctly, and is preceded by an equals sign in the Source box. Confirm that its Refers to entry points to cells or a supported dynamic reference.
- The list is missing new choices: The name may refer to a fixed range. Expand it, use an appropriate dynamic name, or use a spill-based source in a version that supports it.
- The drop-down is empty or includes a heading: Inspect the referenced cells. Make sure the range starts at the first choice and contains values; remove a heading from the range if it should not be selectable.
- The name works on one sheet but not another: Check whether it has worksheet-level scope. Create or use a workbook-scoped name when the same list is needed in multiple locations.
- People can still enter unwanted values: Verify the Error Alert setting and its style. A Stop alert is intended to block an invalid typed entry, while a warning-style alert allows a user to continue.
Validation is a data-entry aid, not a security boundary. Copying and pasting can introduce values that do not match the list, and users may replace or remove validation. For important records, check the data separately—for example, review entries against the allowed list before using them in reports. Keep the source list in a clearly labeled place and protect the worksheet if accidental edits are a concern, while remembering that worksheet protection does not make the workbook secure.
Excel for Mac and the web
In Excel for Mac, the same basic method applies: define the name, select the destination cells, and create a List rule in the Data Validation dialog. Ribbon wording and dialog layout can vary slightly by release, so look for Data Validation on the Data tab and for Define Name or Name Manager on the Formulas tab. Excel for the web also supports common drop-down validation tasks, but some name-management or advanced workbook features may be easier to configure in the desktop app. If a workbook relies on a dynamic name, verify that the list updates as intended in the version used by its editors.
Frequently Asked Questions
What do I enter in the Data Validation Source box?
Enter an equals sign followed by the defined name, such as =ProductList. The name must refer to the choices you want Excel to display.
Can the named range be on another worksheet?
Yes. A workbook-scoped name can refer to a range on another sheet and be used in the validation source. This is a common way to keep source choices separate from the data-entry sheet.
Why does my drop-down not include a new item?
The name may refer to a fixed range that stops before the new item. Expand the reference or change it to a dynamic design, then check that the added value is inside the named range.
Can I use a named range for a dependent drop-down?
Yes. Create a separate named range for each set of dependent choices and use a formula such as =INDIRECT($A2) when A2 contains the matching range name. The labels and names must be compatible and match exactly.
Does data validation prevent every invalid entry?
No. It can guide normal entry and show an alert, but pasting data or changing the rule can bypass that control. Treat validation as a convenience and add separate checks when data quality matters.
Choosing the right setup
For a short list that rarely changes, a fixed named range is easy to create and audit. For a list that grows, use a dynamic reference or a spill-based list where supported, and document where the source lives. For dependent choices, names can work well when categories are stable and naming is controlled; a more elaborate setup may be preferable when categories change frequently. In every case, test the drop-down after editing the source, confirm the error behavior you want, and make sure other workbook users know how to maintain the list.
Related
- Duplicate Conditional Formatting in Excel: Find Values
- Dropdown Slicer in Excel: What It Is and How to Use One
- Excel Dropdown: Remove Duplicate Options Easily
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Access Query Conditional Formatting ExplainedYes—Microsoft Access can apply conditional formatting to fields in a query’s Datasheet View, as well as to form…
- INDEX MATCH in Google Sheets: How It Works and ExamplesYes. Google Sheets supports both INDEX and MATCH, and combining them lets you find a value in one…
- Google Sheets Meal Prep Planner: Set One Up EasilyYes. Google Sheets can work as a flexible meal prep planner for weekly menus, recipes, serving counts, shopping…
- AG Grid Insert Row: APIs, Examples, and Row Model TipsTo insert a row in AG Grid’s Client-Side Row Model, call gridApi.applyTransaction({ add: [newRow], addIndex: 0 }); omit…
- How to Change Zoom in Outlook Email View on Desktop and WebTo change the zoom in an Outlook email, first identify which Outlook you use. In classic Outlook for…
- How to Auto Archive Emails in New OutlookTo auto archive email in New Outlook, create a rule that matches the messages you want to file…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.