To create an Excel data-validation dropdown from another worksheet, put the choices on that sheet, define a workbook-level name for the choice cells, then use that name as the list source. In Microsoft Excel for Microsoft 365 on Windows, choose Formulas > Define Name, and then Data > Data Validation > List; a direct source reference such as a sheet-and-cell address is generally not accepted in the validation box.
Why a named range is needed
Data Validation’s List option expects a list source it can resolve in the current workbook. Excel restricts a direct reference to cells on a different worksheet in the Source box, so entering a cross-sheet address there commonly produces an error. A defined name provides a bridge: it refers to the cells on the source sheet, while the validation rule refers to the name.
This arrangement separates the list of permitted values from the cells where people enter data. For example, a worksheet called Lists can hold department names, while an Entry worksheet contains the dropdown cells. The list can be stored out of the way or on a hidden worksheet, but it still needs to be maintained if the available choices change.
Create the named range and dropdown
Suppose the worksheet Lists contains the department names in A2:A6, with a heading in A1, and you want the dropdown in Entry cells B2:B100. With those locations, name the choices CategoryOptions, referring to =Lists!$A$2:$A$6. The fixed dollar signs keep the reference anchored to those cells. After the rule is applied, each target cell offers the five values from Lists!A2:A6.
- Enter one allowed value in each cell of Lists!A2:A6. Avoid including the heading in the range.
- Select Formulas > Define Name. In the New Name dialog, enter
CategoryOptionsas the name, set Scope to Workbook, and enter=Lists!$A$2:$A$6in Refers to. Select OK. - On the Entry sheet, select B2:B100, or select the individual cells that should have dropdowns.
- Choose Data > Data Validation. On the Settings tab, set Allow to List. Leave In-cell dropdown selected if users should see the arrow in the cell.
- In Source, enter
=CategoryOptions, then select OK. Test a target cell by opening its dropdown and checking that the listed choices are correct.
A name used for this purpose should begin with a letter, underscore, or backslash, contain no spaces, and not look like a cell address. Use underscores or compact words instead of spaces, as in Category_Options. Workbook scope makes the name available from the other worksheet. A sheet-scoped name may not resolve as expected when a validation rule is used elsewhere, so workbook scope is the straightforward choice for a shared list.
Make the list expand when choices are added
A fixed range such as A2:A6 will not include a new value entered in A7. One maintainable option is to convert the source list into an Excel Table. Select the list, including its heading, choose Insert > Table, and confirm that the table has headers. On the Table Design tab, give the table a clear name, such as CategoryTable. Tables extend when entries are added in the row immediately below them.
For a table headed Category, create a workbook-level defined name called CategoryOptions whose Refers to entry is =CategoryTable[Category]. Then keep =CategoryOptions as the validation Source. This means the validation source points to the named range, and the name points to the table column. As the table grows, its column reference includes the new rows, so the dropdown can include newly added categories without changing each validation rule.
Test this setup by adding a new category directly below the table, confirming that the table expands, and opening a dropdown in a validated cell. If the new value does not appear, check that the entry joined the table and that the defined name points to the correct table column. A blank row inside the table can also appear as an empty choice; keeping the source column contiguous and free of empty rows makes the menu easier to use.
Lists on sheets with spaces in their names
Spaces in a worksheet name do not prevent a named range from referring to it. When you enter the reference in Define Name, enclose a sheet name with spaces in apostrophes. For a list on a sheet called Product Lists, with values in A2:A8, the Refers to entry is ='Product Lists'!$A$2:$A$8. The name can still be a simple value such as ProductOptions, and the validation source remains =ProductOptions.
This is one reason a defined name is usually easier to maintain than trying to put a cross-sheet address directly in the validation Source box. It also makes the rule more readable: someone inspecting the validation settings can see the purpose of ProductOptions without needing to interpret a cell address. Choose a name that describes the content rather than a temporary location.
Common problems and practical fixes
- Excel rejects the source. Check for a missing equals sign before the name, spelling differences, or a name that is scoped only to the source sheet. Use Formulas > Name Manager to inspect the name and its Refers to value.
- The dropdown is empty or incomplete. Verify that the name points to the actual values, not the header or an empty section. For a fixed range, confirm that every intended choice is inside the selected cells.
- New choices are missing. A fixed address will not grow automatically. Extend the defined range, or use the table-column approach and make sure new entries are added to the table.
- Some users can type a different value. In Data Validation, check that the Error Alert tab is enabled and set to Stop if entries outside the list should be blocked. A dropdown is an input aid, not a guarantee that data already in the workbook is valid.
- The arrow is not visible. Reopen Data Validation and confirm that In-cell dropdown is selected. The arrow typically appears when the validated cell is selected; it is not permanently displayed beside every cell.
- Changing the source did not fix old entries. Editing a validation rule does not automatically replace values already entered in the target cells. Use Find, filters, or a review process to locate and correct existing entries separately.
Validation settings can also be overwritten when cells are pasted over, depending on what is copied. If a dropdown unexpectedly disappears, select the affected cell and inspect Data > Data Validation rather than assuming that the list name is broken. For a large input area, apply the rule to the whole intended range and spot-check several cells, including the first and last.
Blanks, duplicates, and data quality
Excel’s list dropdown displays values from its source; it does not automatically remove duplicate choices or sort them alphabetically. If the source contains “Sales” twice, users may see two indistinguishable entries. Clean and sort the source list when clarity matters. If a list is generated from records elsewhere, decide whether repeated values are meaningful before using those records directly as choices.
Blank cells in the source can create blank entries in the dropdown. The Ignore blank setting in Data Validation concerns whether the validated cell may be left empty; it does not necessarily remove blank cells from the source list. Keep the choices together without blank gaps, and distinguish between allowing an unanswered target cell and showing a blank option as one of the choices.
Validation is useful for consistent entry, but it should not be treated as a security control. Users can paste data, import content, or alter workbook settings in ways that may bypass the intended restriction. For critical records, combine validation with review or other controls appropriate to the workbook.
Dependent dropdowns are a separate setup
A dependent dropdown changes its options based on a choice in another cell—for example, choosing a department first and then seeing only roles in that department. A single named range for a list on another sheet does not create that dependency; it only supplies the same set of options wherever the rule is applied.
Dependent lists need an additional design, such as separate named ranges for each group and a rule that selects the relevant range, or a formula-based setup. Names based on category text can be awkward when labels contain spaces or punctuation. Plan and test the categories and target cells before applying the rules across a large form. If all users need the same choices, a basic named range is simpler and less error-prone.
Excel for Mac and Excel for the web
The named-range method is also applicable in Excel for Mac. The ribbon layout can differ slightly by version, but use the Formulas tab to create or manage the name and the Data tab’s Data Validation command to make the List rule. The key details are unchanged: use a workbook-level name that refers to the other sheet, then put that name in the validation Source field.
Excel for the web supports common dropdown and validation tasks, but some workbook-management options can differ from the desktop interface and may be less convenient for setting up or troubleshooting names. If the name cannot be created or edited in the browser version available to you, open the workbook in desktop Excel, set up the name and rule there, then save the workbook. When collaborating, confirm that the dropdown still works in the version your users will use.
Frequently Asked Questions
Can I use a cell range on another sheet directly as the Source?
Usually not by entering a direct cross-sheet reference in the Data Validation Source box. Define a workbook-level name for that range and use the name as the source instead.
Do I need to hide the worksheet that contains the list?
No. The source worksheet can remain visible or be hidden. Hiding it is an organizational choice, not a requirement for the dropdown to work.
Will the dropdown update when I add a choice?
Only if its source includes the new cell. A fixed named range stays fixed; a name referring to a suitable Excel Table column can include rows as the table expands.
Can the dropdown source contain values from multiple columns?
A standard List validation source is intended to provide one list of choices, so use one source column or create a separate one-column range that contains the desired values.
Does validation remove values that are no longer allowed?
No. Changing the list or rule does not automatically clean existing entries. Review the target cells and correct any values that are no longer valid.
Related
- Excel Color Scales: How Conditional Formatting Works
- Close Excel Shortcut Key for Windows and Mac
- Close Group Excel Shortcut: Close Workbooks and Rows
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Power Query Keep Duplicates: How the Command WorksYes. Power Query’s Keep Duplicates command keeps every row whose value—or combination of values—in the selected columns occurs…
- Excel Data Validation Default Value ExplainedExcel data validation has no setting that automatically chooses a default value for a cell. To give a…
- Making a Calendar in Google Sheets: A Practical GuideTo make a calendar in Google Sheets, enter a date from the month you want in a cell…
- Google Sheets Timestamp: Insert, Format, and AutomateTo insert a fixed timestamp in Google Sheets, select a cell and press Ctrl+; to enter the current…
- Excel Find and Replace Results: Export Matches and CellsExcel does not have a one-click command to export the results of Find and Replace as a separate…
- Remove a Dropdown from a Google Sheets TableTo remove a dropdown in Google Sheets, select the affected cell or range, open Data > Data validation,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.