Enter the Data for the Drop-Down Menu
In this example, the drop-down menu is on Sheet1, and the data is on Sheet2. Add another sheet in Excel if you don’t already have one.
Name the List Data Selection
A named range lets you refer to the list data as a common name instead of as individual cells. To create a named range, select the cells that will be on the list, and name the selection. Here’s how:
Make the Drop-Down List
Now you’re ready to create the drop-down list. In this example, the drop-down will appear in cell E1 on Sheet1.
Editing the Drop-Down List Items
To keep the drop-down list up to date with changes in your data, change the choices in the list. Because this example uses a named range as the source for a list, changing the cookie names in cells A1 through A4 of Sheet2 immediately changes the names in the drop-down list on Sheet1. For example, if you change Oatmeal Raisin to Shortbread, the drop-down list shows the new entry.
Options for Protecting the Drop-Down List
Your data is on a different worksheet than the drop-down list, so you have two options for protecting the list data.
If security is not a concern, hide the worksheet containing the list data. This makes updating the list easier. If security is a concern, add a password to the sheet so that only people who have the password can edit the list.
