Yes. In Excel for Microsoft 365 on Windows, create a named range for the cells that contain your choices, then set the Data Validation list source to =DepartmentList (using the name you created). This is a practical way to build a drop-down list from cells on another worksheet and to make the source easier to maintain.
How a named range works in a drop-down
A named range gives a cell, range, or formula a readable name. Instead of pointing a validation rule directly at a block of cells, you point it at the name. When someone selects a cell with the rule, Excel displays the values in that named range as a drop-down list.
For example, a workbook might have a worksheet called Lists, with department choices in cells A2:A5. You can name that range DepartmentList and use the name as the validation source. The entries might be Sales, Finance, Operations, and Support. The name describes the purpose of the range, while the list itself remains in its own worksheet.
This is especially useful because Excel’s Data Validation Source box does not reliably accept a direct reference to a range on another worksheet. A defined name provides a convenient reference to that range. It also separates the list data from the cells where people enter answers.
Create the named range
Put the allowed choices in one continuous column or row. A single-column list is usually easiest to review and extend. Do not include the heading in the named range, and avoid blank cells within the choices; a blank can appear as an empty option in the drop-down.
- Select the choice cells. For this example, the list is on a worksheet named Lists, with department names in A2:A5.
- Click the Name Box to the left of the formula bar, type
DepartmentList, and press Enter. The Name Box normally displays the active cell address, such as A2. - Alternatively, use Formulas > Define Name. Enter DepartmentList as the name, choose Workbook for Scope if you want to use it throughout the workbook, and check the Refers to range before clicking OK.
With the choices in Lists!A2:A5, the Refers to entry for the name is =Lists!$A$2:$A$5. The dollar signs keep the cell references fixed. If the sheet name contains spaces, Excel encloses it in apostrophes, as in ='Lookup Lists'!$A$2:$A$5. You can review or edit names later through Formulas > Name Manager.
Apply the name to Data Validation
After creating the name, select the cell or range that should have the drop-down. To apply the same rule to a block of cells, select the whole block first. Then use these steps in Excel for Microsoft 365 on Windows:
- Choose Data > Data Validation.
- On the Settings tab, set Allow to List.
- In Source, enter
=DepartmentList. - Leave In-cell dropdown selected if users should see the arrow, then click OK.
Here the list source is the named range that refers to Lists!A2:A5. The equal sign tells Excel to interpret the entry as a reference or formula rather than as literal list text. If the source is rejected, check the spelling of the name and confirm that it refers to a valid range in this workbook.
For a more helpful entry experience, use the Input Message tab to show a short prompt when the cell is selected. On the Error Alert tab, choose a style and write a message explaining the allowed entries. The Stop style blocks a user from confirming an invalid value through normal typing; Warning and Information alerts allow the user to continue after a prompt.
Keep the list current as choices change
A basic named range points to a fixed set of cells. If you add a new department below A5, the name will not automatically include A6. You can update the name in Formulas > Name Manager by changing its Refers to range, or choose a source design that expands with the list.
One option in current Excel is a dynamic-array spill range. Put the changing list in A2 on the Lists sheet and let it spill into the cells below. With the spill formula located in Lists!A2, you can define DepartmentList to refer to =Lists!$A$2#; then keep the validation Source as =DepartmentList. The # refers to the full spilled result, so the named range follows the spill size. The cell layout matters: the spill formula must be in A2, and the cells where it needs to expand must be clear.
Functions such as UNIQUE and SORT can generate a changing list, but they are available in Excel 2021 and Microsoft 365 rather than every older Excel release. If you use a dynamic-array formula to create choices, place it outside the cells that need validation and name its spill range. A spill error, such as blocked cells underneath the formula, prevents the list from expanding as intended.
Another maintainable approach is to keep choices in an Excel table, which expands when rows are added. A structured table reference may need to be wrapped in a defined name before using it as a validation source. Test the rule after adding a row, especially in workbooks shared with older Excel versions, because support for newer formula behavior varies.
Choose the right name scope
Names can have workbook scope or worksheet scope. A workbook-scoped name is available throughout the workbook and is usually the simplest choice for a shared validation list. A worksheet-scoped name is associated with one sheet; it can be useful where different worksheets intentionally use the same name for different lists, but that arrangement is easier to misunderstand.
For a general-purpose list stored on a separate Lists sheet, create a workbook-scoped name. Avoid giving the same name both workbook scope and worksheet scope unless there is a clear reason. Scope conflicts can make it unclear which range Excel will use in a formula, and a name that exists only at another sheet’s scope may not resolve as expected from the validation cell.
Names cannot contain spaces. They generally begin with a letter, an underscore, or a backslash, and cannot be formatted like a cell reference. Use a clear name such as DepartmentList or Project_Status rather than a vague label like List1. Names are not case-sensitive, but consistent capitalization makes them easier for people to read.
Common problems and practical limits
- The source says the name is invalid: Check for a missing equal sign, a spelling mismatch, or a name that is out of scope. In Name Manager, confirm that Refers to points to existing cells and that the name is not an unexpected sheet-scoped name.
- The list is missing new entries: The named range may still point only to the original cells. Expand its reference or use a suitable dynamic source, then reopen the validation dialog to verify the rule.
- The drop-down contains a blank: Look for empty cells inside the referenced range. Remove them or adjust the range so it includes only actual choices.
- The arrow is not visible: Reopen Data > Data Validation and confirm that Allow is List and In-cell dropdown is selected. The arrow normally appears when the validated cell is selected.
- Invalid data appears anyway: Data Validation is an input aid, not a security boundary. Pasting or filling data can introduce values that do not follow the rule, and existing values are not automatically corrected when a rule is added. Use the Circle Invalid Data command in the Data Validation menu to locate entries that fail a rule, then correct them.
Data Validation is available in Excel for the web, but some authoring and name-management tasks can be more straightforward in the desktop application. If you cannot create or edit the named range in your current Excel interface, set it up using Excel for Microsoft 365 on Windows and then confirm that the drop-down behaves as expected where the workbook will be used. On Mac, the same named-range approach applies, though ribbon placement and dialog appearance can vary by release.
Named ranges compared with other list sources
For a very short, fixed list used in one place, you can type the choices directly into the Source box, separated by commas. That avoids creating a range, but it is less convenient to maintain and may be affected by regional list-separator settings. A named range is generally clearer when choices are stored in cells, reused, or maintained by someone else.
A direct cell reference can be simple when the choices are on the same worksheet, but it is less readable than a meaningful name and can be awkward when the list is on another sheet. A named range provides a useful middle ground: the values stay visible and editable in cells, while the validation rule uses a compact label. For larger or frequently updated lists, a table or dynamic-array-based list can reduce the need to adjust a fixed range manually.
Choose based on how often the choices change and who maintains them. A fixed named range is easy to audit; a dynamic source can save maintenance but requires a correct setup and compatible Excel version. Whichever option you use, test a valid choice, an invalid typed value, and a newly added choice before distributing the workbook.
Frequently Asked Questions
Can Excel Data Validation use a named range from another sheet?
Yes. Create a name that refers to the cells on the other worksheet, then use the name with an equal sign in the list Source box, such as =DepartmentList. A workbook-scoped name is usually the simplest choice for this setup.
Do I need the equal sign before the named range?
Yes. In the Data Validation Source box, enter the name as a reference with a leading equal sign, for example =DepartmentList. Without it, Excel may treat the entry as literal text rather than a reference to the defined name.
Will a named range expand automatically when I add a choice?
Not if it refers to a fixed range such as A2:A5. Update the range, or use a dynamic source such as a named spill reference or a carefully designed table-based name. Test the result after adding an item.
Can I use a named range for a dependent drop-down?
Yes. A dependent list changes according to an earlier selection. It commonly uses separate named lists and a formula that selects the appropriate name. This requires careful naming and setup; the basic single-list method in this article does not create the dependency by itself.
Why does the validation rule allow a value that is not on the list?
Check the Error Alert settings and ensure the alert is enabled with Stop if you want normal typing of invalid entries to be blocked. Also remember that pasting data can bypass validation checks, so the rule should not be treated as data security or a guarantee that every existing cell is valid.
Related
- Excel Split Contents of a Cell Into Two Cells
- Excel Split Multiline Cell Into Rows
- Excel Split Empty Cells Into Multiple Rows?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Compare Two Sheets in Notepad++: A Practical GuideTo compare two text files in Notepad++, open both files in separate tabs, install the Compare plugin if…
- How to Add and Format Numbered Footnotes in WordTo add a numbered footnote in Microsoft Word, click where its reference number should appear, then choose References…
- How to Remove a Section Break in Word Without Losing TextTo remove a section break in Word, show formatting marks, locate the line labeled Section Break, then place…
- Merge Cells and Keep Both Values in Excel Without Data LossIn Excel, merging cells keeps only the value in the upper-left cell; any other cell contents in the…
- Unable to Share Calendar in Outlook 365? Try These FixesIf you can’t share a calendar in Outlook 365, first confirm you’re signed in to the account that…
- Free Inventory Spreadsheet: Build and Use One in ExcelYes—you can make a useful free inventory spreadsheet in Excel by listing each item once, recording its quantity…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.