Entering the Data to Create a Drop-Down List
When you add a drop-down list to a cell in Excel, an arrow appears next to it. Clicking on the arrow opens the list so you can select one of the items to enter into the cell. For example, if you’re using a spreadsheet to track RSVPs for an event, you could filter that column by Yes, No, and Not Yet Replied. The data used in the list can be located on the same sheet as the list, on a different sheet in the same workbook, or in a different workbook. In this example, the drop-down uses a list of entries located in a different workbook. The advantages of this method include centralizing list data for multiple users and protecting it from accidental or intentional change.
Data for Cells A1 to A4 in data-source.xlsx
A1 — GingerbreadA2 — LemonA3 — Oatmeal RaisinA4 — Chocolate Chip
Data for Cell B1 in drop-down-list.xlsx
B1 — Cookie Type:
Creating Two Named Ranges
A named range allows you to refer to a specific range of cells in an Excel workbook. Named ranges have many uses in Excel including in formulas and when creating charts. In all cases, you must use a named range instead of a range of cell references indicating the location of data in a worksheet. When using a drop-down list from another workbook, you need to use two named ranges. One is for the list items and the second is in the workbook where the drop-down list is — this named range links to the one in the first workbook.
The First Named Range
The Second Named Range
The second named range does not use cell references from the drop-down-list.xlsx workbook. Instead, it links to the Cookies range name in the data-source.xlsx workbook, which is necessary because Excel will not accept cell references from a different workbook for a named range. It will, however, except another range name. Creating the second named range, therefore, is not done using the Name Box but by using the Define Name option located on the Formulas tab of the ribbon.
Using a List for Data Validation
All data validation options in Excel, including drop-down lists, are set using the data validation dialog box. In addition to adding drop-down lists to a worksheet, data validation in Excel can also be used to control or limit the type of data that users can enter into specific cells in a worksheet.
Changing the Drop-Down List
Since this example used a named range as the source for our list items rather than the actual list names, changing the cookie names in the named range in cells A1 to A4 of the data-source.xlsx workbook immediately changes the names in the drop-down list. Follow the steps below to change Lemon to Shortbread in the drop-down list by changing the data in cell A2 of the named range in the data-source.xlsx workbook.
Options for Protecting the Drop-Down List
Since the data in this example is on a different worksheet than the drop-down list, the options available for protecting the list data include:
Protecting the worksheet by locking cells A1 to A4 on sheet 2 Requiring a password to modify the workbook Having the workbook open as Read-only
