Drop-Down List From Another Sheet in Excel

A drop-down list from another sheet keeps your form clean: the choices live on a separate sheet, and the form only shows the arrow. In Excel you build it with Data Validation, pointing the list's source at the range on the other sheet or at a named range. This guide shows both ways, how to make the list grow by itself, and how to fix a drop-down that breaks.

Why keep the list on another sheet?#

Putting the items on their own sheet makes the workbook easier to manage. The form sheet stays tidy, and there is one place to add, remove or fix an item. Every drop-down that uses the list updates at once.

A common setup is a sheet called Lists with one column per list:

And a sheet called Orders where people pick a product in column B. The steps below put a drop-down in Orders!B2:B20 that reads from Lists!A2:A6.

How to create a drop-down list from another sheet#

Select the cells first, then point Data Validation at the other sheet:

1. On the Orders sheet, select B2:B20.
2. Go to Data > Data Validation (on Windows you can also press Alt, then A, V, V).
3. In Allow, choose List.
4. Click in the Source box, then click the Lists sheet tab and select A2:A6. Excel fills in =Lists!$A$2:$A$6.
5. Make sure In-cell dropdown is ticked and click OK.

The reference has two parts. Lists! is the sheet name followed by an exclamation mark, and $A$2:$A$6 is the range. The $ signs lock it, so every cell in B2:B20 uses the same list. The guide to referencing another sheet explains this format in detail.

Pointing straight at another sheet works in Excel 2010 and later, including Microsoft 365 and Excel for Mac. If the sheet name has spaces, Excel adds single quotes: ='Product List'!$A$2:$A$6. When you type the reference by hand, add them yourself.

Use a named range for a cleaner source#

A named range is a name you give to a group of cells, such as Products for Lists!A2:A6. It makes the source easy to read and keeps working if you move the list.

1. On the Lists sheet, select A2:A6.
2. Click in the Name Box, the box left of the formula bar that shows the cell address.
3. Type Products (no spaces) and press Enter.
4. In Data Validation, set the Source to =Products.

You can see and edit every name in Formulas > Name Manager. The basics of setting up a drop-down, including typed lists and error alerts, are in the guide to creating a drop-down list.

Make the list grow when you add items#

A fixed range like A2:A6 won't include a seventh product typed in A7. To let the list grow, turn it into an Excel table and point a name at the table column:

1. Click inside the list on the Lists sheet and press Ctrl+T (Mac: ⌘T). Tick My table has headers and click OK.
2. Rename the table to ProductTable in Table Design > Table Name.
3. Go to Formulas > Define Name, type the name Products and set Refers to to =ProductTable[Product].
4. Use =Products as the Data Validation source.

Now any row you add to the table appears in the drop-down. Data Validation won't accept a table reference typed directly into the Source box, which is why the name sits in between.

In Microsoft 365 you can also build a sorted list of unique items with a formula on the Lists sheet and point at its spill range, for example =Lists!$D$2#. The guide to dynamic drop-down lists shows this method and dependent drop-downs.

Hide or protect the list sheet#

Once the drop-down works, you can stop people from editing the list by accident:

• Hide the sheet. Right-click the Lists tab and choose Hide. The drop-down still works. Right-click any tab and choose Unhide to bring it back.
• Protect the workbook structure. Go to Review > Protect Workbook so others can't unhide, rename or delete the sheet.
• Protect the sheet. Go to Review > Protect Sheet if the list must stay visible but locked.

Use the chosen item to pull details#

A drop-down often feeds a lookup. When someone picks Monitor in Orders!B2, the price can fill in next to it with VLOOKUP on the same list:

=VLOOKUP(B2,Lists!$A$2:$B$6,2,FALSE)

This returns 210. In Excel 2021 or Microsoft 365, XLOOKUP does the same with separate ranges: =XLOOKUP(B2,Lists!$A$2:$A$6,Lists!$B$2:$B$6). See how to use XLOOKUP across sheets for more. To count how many orders chose each product, COUNTIF works across sheets too; the article on COUNTIF from another sheet shows how.

Why is my drop-down list from another sheet not working?#

Check these common causes:

1. No arrow appears. In-cell dropdown is unticked in the Data Validation box, or you selected a different cell.
2. "The source currently evaluates to an error." The sheet name is misspelled, missing quotes around a name with spaces, or the named range doesn't exist.
3. Items are missing. The source range stops too early. Extend it, or switch to a table so it grows.
4. Blank lines at the end of the list. The source range is longer than the list. "Ignore blank" doesn't remove blank items from the list; it only allows an empty cell. Shorten the range or use a table.
5. The list sheet was deleted or renamed. Deleting it breaks the source. Renaming is safe, because Excel updates the reference.
6. The list is in another workbook. It only works reliably while that file is open. Copy the list into your workbook instead.

Practise it#

Cross-sheet references are the part people get wrong under time pressure. Practise worksheets and cross-sheet references in a live spreadsheet: you'll write formulas that read from other sheets, with every answer checked instantly. Then try the lookup exercise on this page to pull a price for the chosen item.