How to Create a Drop-Down List in Excel

This article explains how to create a drop-down list in Excel. It includes information on using a list for data validation options, for protecting a drop-down list, and for making changes to a drop-down list. The instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Microsoft 365.

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.

1. Open two blank Excel workbooks.

2. Save one workbook with the name data-source.xlsx. This workbook will contain the data for the drop-down list.

3. Save the second workbook with the name drop-down-list.xlsx. This workbook will contain the drop-down list.

4. Leave both workbooks open after saving.

5. Enter the data as shown below into cells A1 to A4 of the data-source.xlsx workbook as seen in this image.

6. Save the workbook and leave it open.

7. Enter the data as shown in the image into cell B1 of the drop-down-list.xlsx workbook.

8. Save the workbook and leave it open.

Data for Cells A1 to A4 in data-source.xlsx

• A1 — Gingerbread
• A2 — Lemon
• A3 — Oatmeal Raisin
• A4 — 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

1. Select cells A1 to A4 of the data-source.xlsx workbook to highlight them.

2. Click on the Name Box located above column A.

3. Type Cookies in the Name Box.

4. Press the Enter key on the keyboard.

5. Cells A1 to A4 of the data-source.xlsx workbook now have the range name of Cookies.

6. Save the workbook.

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.

1. Click on cell C1 in the drop-down-list.xlsx workbook.

2. Click on Formulas > Define Name on the ribbon to open the Define Name dialog box.

3. Click on the New button to open the New Name dialog box.

4. Type Data in the Name line.

5. In the Refers to line type ='data-source.xlsx'!Cookies

6. Click OK to complete the named range and return to the Define Name dialog box.

7. Click Close to close the Define Name dialog box.

8. Save the workbook.

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.

1. Click on cell C1 of the drop-down-list.xlsx workbook to make it the active cell — this is where the drop-down list will be.

2. Click on the Data tab of the ribbon menu above the worksheet.

3. Click on the Data Validation icon on the ribbon to open the drop-down menu. Select the Data Validation option.

4. Click on the Settings tab in the Data Validation dialog box.

5. Click on the down arrow at the end of the Allow line to open the drop-down menu.

6. Click on List to choose a drop-down list for data validation in cell C1 and to activate the Source line in the dialog box.

7. Since the data source for the drop-down list is in a different workbook, the second named range goes in the Source line in the dialog box.

8. Click on the Source line.

9. Type =Data in the Source line.

10. Click OK to complete the drop-down list and close the Data Validation dialog box.

11. A small down arrow icon should appear on the right side of cell C1. Clicking on the down arrow will open the drop-down list containing the four cookie names entered into cells A1 to A4 of the data-source.xlsx workbook.

12. Clicking on one of the names in the drop-down list should enter that name into cell C1.

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.

1. Click on cell A2 in the data-source.xlsx workbook to make it the active cell.

2. Type Shortbread into cell A2 and press the Enter key on the keyboard.

3. Click on the down arrow for the drop-down list in cell C1 of the drop-down-list.xlsx workbook.

4. Item 2 in the list should now read Shortbread instead of Lemon.

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