Image: AndreyPopov, Getty Images/iStockphoto
The data you want to use in an Excel drop down list usually won’t be in the same sheet as the drop down. The good news is that identifying a list on another sheet requires only an extra click to access the sheet. We can illustrate this as follows. (You can work with your own data or download the demonstration .xlsx and .xls files.)
- If you have only one sheet, add a new one. (“Different sheet” in the demonstration file.)
- Select E4 in the new sheet and repeat the instructions for creating a drop down from a previous Excel article through step 4.
- When you get to the step 5, where you identify the source, click inside the Source Control.
- Click the Lists sheet tab or the tab that contains your list items.
- Select the list (A1:A4).
- Click OK, which will return you to the new sheet, where you’ll find a populated list in E4 (Figure A).
Figure A
With an extra click to identify the sheet, you can easily display list items on another sheet.
Get more Excel tips
Read 56 Excel tips every user should master and the tutorials how to add a condition to a drop down list in Excel, how to add color to a drop down list in Excel, how to change an Excel conditional format on the fly, and how to combine Excel’s VLOOKUP() function with a combo box for enhanced searching. Also, check out these free PDF download compilations Build your Excel skills with these 10 power tips and 13 handy Excel data entry shortcuts.