Last week we talked about creating drop down lists in Excel using data that is in the same worksheet as the drop-down menu. But that looks cluttered — what if you could put the data on a separate sheet and just have the drop-down menu? You can! Here’s how:
To create a drop-down menu with the data in a separate worksheet:
- In Excel, at the bottom of each workbook there are multiple worksheets you can use. Double-click the second sheet and give it a name (in this example, I’m using “data”).
- In the second sheet, enter the data for the pre-selected list. In this example, I’m entering the data in A1, A2, A3, and A4
- Return to the first sheet and click the cell in which you want the drop-down list to be displayed. In this example, I’m using cell B1.
- Click the Data tab. Then, in Data Tools, click Data Validation.
- In the Settings tab, in Allow, select List.
- In Source, enter the sheet and the cells with the data you entered in step 2, and then click OK.
In this example, I have selected cells A1 – A4 from the sheet “Data” as the source by typing in the following:
A drop-down arrow now appears in the cell you chose in step 3(in my case, B1). Clicking the arrow on the list displays the data pieces, and you can now select one of them.