To make a dropdown in Excel that uses choices stored on another worksheet, create a workbook-level named range for the choices, then use that name as the list source in Data Validation. For example, name the entries in Lists!A2:A6 CategoryOptions, select the cells that need the dropdown, and set Data > Data Validation > Allow to List with =CategoryOptions as the Source.
What a sheet dropdown list does
An Excel dropdown list limits or guides what someone enters in a cell by showing a set of permitted choices. It is created with Data Validation, not with a worksheet tab setting. The list may be written directly into the validation rule, stored in cells on the same sheet, or kept on a separate worksheet and connected through a defined name.
A list on another sheet is useful when several entry sheets share the same options, or when you want to keep reference data out of the way of the form people fill in. For example, a workbook might have an Orders sheet with a Product Type column and a Lists sheet containing choices such as Hardware, Supplies, and Services. If the source list is maintained in one place, it is easier to update consistently than multiple copies of the same options.
A dropdown is a helpful input control, but it is not a complete data-security feature. Users may still be able to paste invalid entries into a cell or alter the validation settings if the workbook is not protected. Treat validation as a way to reduce mistakes and standardize entries, rather than as a guarantee that every cell will always contain an approved value.
Create a dropdown from another worksheet
In Excel for Microsoft 365 on Windows, the reliable method is to define a name for the source cells. Assume the choices are in Lists!A2:A6 and the cells that need a dropdown are on an Entry worksheet. The list should normally contain one choice per cell, with no heading included in the selected range.
- On the Lists worksheet, select the cells containing the choices, such as A2:A6.
- Click the Name Box to the left of the formula bar, type CategoryOptions, and press Enter. The name must start with a letter or underscore, cannot contain spaces, and should not resemble a cell address.
- Go to the Entry worksheet and select the cell or range where people will make a selection.
- Choose Data > Data Validation. In the Settings tab, set Allow to List and make sure In-cell dropdown is selected.
- In Source, enter
=CategoryOptions, then select OK.
Click one of the validated cells to see its dropdown arrow. Depending on the cell selection and Excel’s display, the arrow may only appear when the cell is active. Select an option to enter it. If you apply the rule to several cells at once, each cell will use the same source list.
The name avoids a common restriction: Excel’s Data Validation Source box generally does not accept a direct reference to a different worksheet, such as a reference written directly to Lists!A2:A6. A workbook-level name provides a usable reference to those cells. In Excel for Mac, the same general approach applies; use Data > Data Validation, though ribbon arrangement and dialog appearance can vary slightly by release.
Check and manage the named range
Names make the setup easier to read and maintain, but they should point to the intended cells. To inspect one, open Formulas > Name Manager, find CategoryOptions, and check its scope and Refers to entry. A workbook-level name is appropriate when dropdowns on multiple worksheets need the same list. If a name is accidentally limited to a single worksheet, it may not work as expected elsewhere.
If the source list changes location, update the name rather than rebuilding every validation rule. In Name Manager, select the name, choose Edit, and adjust the reference. For example, if the options move from column A to column D on Lists, change the reference to the new range. Use absolute row and column references when editing the reference so that it remains fixed when copied or used from another sheet.
Names are also useful for understanding a workbook later. CategoryOptions is more descriptive than a bare cell address, especially if the source range may move. Keep names distinct and specific; if a workbook has separate lists for regions, departments, and product types, give each its own name rather than using a vague label such as Choices.
Keep the dropdown list up to date
A named range based on a fixed set of cells does not automatically grow when you type a new item immediately below it. If CategoryOptions refers to A2:A6 and you add an option in A7, the existing dropdown will still use only A2:A6 until you expand the name. This is one of the most common reasons a new choice fails to appear.
For a short, rarely changed list, expanding the reference in Name Manager is simple. If the list is maintained regularly, consider formatting the source as an Excel Table: select the heading and entries, then choose Insert > Table. Give the table and its column clear names, and maintain the options by adding entries to the table. A defined name can refer to the table’s data column, and the validation rule can continue to use that defined name. This approach is more maintainable than guessing how many blank cells to include in a fixed range.
Keep the source data clean. Avoid blank rows between choices, duplicate spellings, and accidental spaces at the start or end of an entry. Those details can create empty-looking dropdown choices or choices that appear identical but are treated as different text. If a blank is intentional, decide whether it should be available as a selectable option; it is different from simply leaving spare blank cells in the source range.
Configure what happens when someone types
Data Validation can do more than display the list. In its Settings, Allow set to List creates the dropdown, while Ignore blank determines whether an empty cell is permitted under the rule. The Error Alert tab controls the response when someone enters a value that is not on the list. A Stop alert blocks an invalid typed entry and asks the user to correct it; Warning and Information alerts allow the user to proceed after a prompt.
The Input Message tab can show a short instruction when a validated cell is selected. For example, a message might say Select one department from the list. This is useful in forms where the meaning of a column is not obvious, or where users need to know that only one choice is expected. Keep messages concise so that they clarify the rule instead of repeating the column heading.
Validation does not automatically create a multi-select dropdown. Choosing a second item replaces the first value in the cell. Excel has no standard Data Validation setting that appends selections to a cell. Adding that behavior usually requires a macro or another design, which can introduce compatibility and maintenance issues. For most workbooks, one choice per row is easier to filter, summarize, and analyze.
Common problems and fixes
- The list source is rejected: Check that the source is a defined name written with a leading equals sign, such as
=CategoryOptions, and that the name exists in the workbook. A direct reference to another sheet is a frequent cause of this error. - A new option is missing: The named range may still end at the old last row. Expand it in Name Manager or use a suitable table-based source that grows as rows are added.
- The dropdown arrow is not visible: Select the cell and check Data > Data Validation to confirm that Allow is List and In-cell dropdown is selected. The arrow is not necessarily shown while the cell is inactive.
- Some cells have the rule and others do not: Select a cell that works and inspect its validation settings, then apply the rule to the missing cells. Copying or filling cells can also copy validation, but check the result rather than assuming every destination received it.
- Invalid values appear anyway: Data can be pasted or filled into validated cells without the same prompt that appears during ordinary typing. Review important data, and use appropriate worksheet protection or cleanup checks if consistency matters.
- The dropdown contains duplicates or odd blanks: Inspect the source cells for duplicate values, empty cells, and extra spaces. Correcting the source is usually better than trying to hide a messy list in the validation settings.
Choose the right list source
For a handful of fixed choices, typing items directly into the Source box can be quick, but it is less convenient to maintain and may be subject to a character limit. It also makes the choices harder to reuse across worksheets. A same-sheet cell range is easy to set up when the list belongs beside the data, while a named range is usually the clearest solution when the list lives on another sheet.
Choose a fixed named range when the options change infrequently and someone can update the range when needed. Choose a table-backed named source when options are added over time and you want the source to expand with the table. Whichever method you use, keep the authoritative list in one place, explain who may edit it, and test the dropdown after adding or removing an item.
Do not confuse a dropdown list with a filter dropdown in a table heading. A filter dropdown changes which existing rows are displayed; Data Validation supplies choices for a cell being entered or edited. They may look similar on screen, but they serve different purposes and are configured in different places.
Frequently Asked Questions
Can an Excel dropdown list use a different worksheet?
Yes. Define a workbook-level name for the source cells on the other worksheet, then enter that name as the Data Validation list source. For example, if the named range is CategoryOptions, use =CategoryOptions.
Why can’t I type a sheet reference directly into the Source box?
Excel commonly rejects a direct cross-worksheet reference in the Data Validation Source field. A defined name acts as an intermediary: it refers to the cells on the source sheet, while the validation rule refers to the name.
How do I add another choice to the dropdown?
Add the value to the source list, then make sure the named range includes its cell. If the name points to a fixed range, expand the range in Formulas > Name Manager. A table-backed source can be more convenient for lists that grow regularly.
Can a dropdown allow more than one selection in the same cell?
Not through standard Data Validation settings. Selecting another item replaces the existing value. Multi-select behavior requires a different workbook design or custom automation, and should be considered carefully if the file must work across different Excel platforms.
Will validation stop users from pasting an unlisted value?
Not reliably. Validation can reject invalid values entered normally, depending on the Error Alert setting, but pasting can bypass the prompt. Check data after imports or pastes when entries must match the approved list.