Excel drop-down lists are created with Data Validation. When the list values live on another sheet, the most reliable approach is either typing the cross-sheet reference directly or using a named range.
Does this affect you?
Use this for Excel for Microsoft 365, Excel 2021, and Excel 2019 on Windows or Mac when a form or data-entry sheet needs options stored on a separate sheet in the same workbook.
Type the cross-sheet reference directly
This works in current Excel versions and is quickest when accepted.
- Click the cell where the drop-down should appear.
- Open Data > Data Validation.
- Set Allow to List.
- In Source, type a reference such as =Products!$A$2:$A$10.
- If the sheet name has spaces, wrap it in single quotes, such as ='Product List'!$A$2:$A$10.
- Click OK.
If Excel rejects the reference or the dialog behaves oddly, use a named range instead.
Use a named range as the source
This is the most dependable method across versions.
- Go to the sheet containing the option list.
- Select the option cells, such as Products!$A$2:$A$10.
- Click the Name Box to the left of the formula bar.
- Type a name with no spaces, such as ProductList, and press Enter.
- Go back to the drop-down cell.
- Open Data > Data Validation > List.
- In Source, type =ProductList and click OK.
More control
Older Excel may require names
Some older file formats and older Excel versions block direct validation references to another sheet. Named ranges avoid that limitation.
Fixed ranges do not grow automatically
If you add new items below A10, a fixed range will not include them. Edit the named range in Formulas > Name Manager or use an Excel Table for a growing source list.
Keep the source in the same workbook
Data Validation lists cannot reliably point directly to another workbook. Copy the source list into the current file, even on a hidden sheet, then reference it there.
Sources
- Microsoft Support – Create a drop-down list (2025)
- Microsoft Support – Apply data validation to cells (2025)
- Microsoft Support – Define and use names in formulas (2024)