How to Create a Drop Down List in Excel

Notebook covering up financial data
borisyankov / Getty Images

Excel's data validation options include creating a drop-down list that limits the data that can be entered into a specific cell to a pre-set list of entries. When a drop-down list is added to a cell, an arrow is displayed next to it. Clicking on the arrow will open the list and allow you to select one of the list items to enter into the cell.

The data used in the list can be located on the same worksheet as the list, on a different worksheet in the same workbook, or in a different workbook.

01
of 05

Entering the Tutorial Data

Screenshot of Excel showing tutorial data

In this tutorial, we will create a drop-down list using a list of entries located in a different workbook. Advantages of using a list of entries located in a different workbook include centralizing list data if it is used by multiple users and protecting the data from accidental or intentional change.

When the list data is stored in a separate workbook both workbooks must be open in order for the list to work.

Following the steps in the tutorial topics below walks you through creating, using, and changing the drop-down list similar to the one seen in the image above. As mentioned, for this tutorial the data for the drop-down list will be located in a different workbook from the drop-down list.

Opening Two Excel Workbooks

  1. Begin by opening 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.

Entering the Tutorial Data

  1. Enter the data as shown below into cells A1 to A4 of the data-source.xlsx workbook as seen in the image above.
  2. Save the workbook and leave it open.
  3. Enter the data as shown below into cell B1 of the drop-down-list.xlsx workbook.
  4. Save the workbook and leave it open.

Data For Cells A1 to A4 in data-source.xlsx:

  • A1Gingerbread
  • A2Lemon
  • A3Oatmeal Raisin
  • A4Chocolate Chip

Data For Cell B1 in drop-down-list.xlsx:

  • B1Cookie Type:
02
of 05

Creating Two Named Ranges

Screenshot of Excel showing creation of a named range

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 using them in formulas and when creating charts. In all cases, a named range is used in place of a range of cell references indicating the location of data in a worksheet.

When used in a drop-down located in a different workbook, two named ranges must be used. One is created for the list items in the workbook where the data is located. The second is created in the workbook where the drop down list is located — this named range links to the named range 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. Rather, it will, as mentioned, link to the Cookies range name in the data-source.xlsx workbook.

Screenshot of Excel showing our second named range

This 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. In the Name line type Data.
  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.
03
of 05

Using a List for Data Validation

Screenshot of Excel showing data validation entry

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 can be entered 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 located.
  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.
  4. Click on the Data Validation option in the menu to open the Data Validation dialog box.
  5. Click on the Settings tab in the Data Validation dialog box.
  6. Click on the down arrow at the end of the Allow line to open the drop-down menu.
  7. 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.

Since the data source for the drop down list is located on a different workbook, the second named range created earlier will be entered in the Source line in the dialog box.

  1. Click on the Source line.
  2. Type = Data in the Source line.
  3. Click OK to complete the drop-down list and close the Data Validation dialog box.
  4. A small down arrow icon should now be located on the right side of cell C1 Clicking on the down arrow should open the drop-down list containing the four cookie names entered into cells A1 to A4 of the data-source.xlsx workbook.
  5. Clicking on one of the names in the drop-down list should enter that name into cell C1.
04
of 05

Changing the Drop Down List

Screenshot of Excel showing changed data

Since we 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 located in cells A1 to A4 of the data-source.xlsx workbook immediately changes the names in the drop-down list.

If the data is entered directly into the dialog box, making changes to the list involves going back into the dialog box and editing the source line.

In this step, we will 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.
05
of 05

Options for Protecting the Drop Down List

Screenshot of Excel showing how to protect a worksheet

Since our data is on a different worksheet from the drop-down list options available for protecting the list data include: