How to Create a Drop Down List in Excel

Using Data from a Different Workbook
Ted French

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:

Tutorial: Using Data Stored in a Different Workbook

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.

Note: 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.

These tutorial instructions, however, do not include formatting steps for the worksheets.

This will not interfere with completing the tutorial. Your worksheet will look different than the example on page 1, but the drop-down list will give you the same results.

Tutorial Topics

  • Opening Files and Saving the Tutorial Data
  • Creating Two Named Ranges
  • Opening the Data Validation Dialog Box
  • Entering the Data Source and Completing the Drop Down List
  • Changing the List Items
  • Options for Protecting the Drop Down List
01
of 06

Entering the Tutorial Data

Using Data from a Different Workbook
Using Data from a Different Workbook. © Ted French

Opening Two Excel Workbooks

As mentioned, for this tutorial the data for the drop-down list will be located in a different workbook from the drop-down list.

For this tutorial follow these steps:

  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.

Entering the Tutorial Data

  1. Enter the data below into cells A1 to A4 of the data-source.xlsx workbook as seen in the image above.
  2.  A1 - Gingerbread
    A2 - Lemon
    A3 - Oatmeal Raisin
    A4 - Chocolate Chip
    
  3. Save the workbook and it leave it open
  4. Enter the data below into cells B1 of the drop-down-list.xlsx workbook.
  5.  B1 - Cookie Type:
    
  6. Save the workbook and it leave it open
  7. The drop down list will be added to cell C1 of this workbook
02
of 06

Creating Two Named Ranges

Using Data from a Different Workbook
Using Data from a Different Workbook. © Ted French

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 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 list 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
  • A second is created in the workbook where the drop down list is located. This second named range links to the named range in first workbook

Tutorial Steps

The First Named Range

  1. Select cells A1 - A4 of the data-source.xlsx workbook to highlight them
  2. Click on the Name Box located above column A
  3. Type "Cookies" (no quotes) 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

This 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.

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 Name Manager 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 > Name Manager on the ribbon to open the Name Manager 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 Name Manager dialog box
  7. Click Close to close the Name Manager dialog box
  8. Save the workbook
03
of 06

Opening the Data Validation Dialog Box

Using Data from a Different Workbook
Using Data from a Different Workbook. © Ted French

Opening the Data Validation Dialog Box

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.

Tutorial Steps

  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. Leave the dialog box open for the next step in the tutorial
04
of 06

Using a List for Data Validation

Using Data from a Different Workbook
Using Data from a Different Workbook. © Ted French

Selecting a List for Data Validation

As mentioned there are a number of options for data validation in Excel in addition to a drop down list.

In this step we will choose the List option as the type of data validation to be used for cell D1 of the worksheet.

Tutorial Steps

  1. Click on Settings tab in the dialog box
  2. Click on the down arrow at the end of the Allow line to open the drop down menu
  3. 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

Entering the Data Source and Completing the Drop Down List

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.

Tutorial Steps

  1. Click on the Source line
  2. Type " = Data " (no quotes) 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 located on the right side of cell C1
  5. 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
  6. Clicking on one of the names should enter that name into cell C1
05
of 06

Changing the Drop Down List

Using Data from a Different Workbook
Using Data from a Different Workbook. © Ted French

Changing the List Items

To keep the drop down list up to date with changes in our data, it may be necessary to periodically change the choices in the list.

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 edit 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.

Tutorial Steps

  1. Click on cell A2 in the data-source.xlsx workbook (Lemon) 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 to open the list
  4. Item 2 in the list should now read Shortbread instead of Lemon
06
of 06

Options for Protecting the Drop Down List

Using Data from a Different Workbook
Using Data from a Different Workbook. © Ted French

Options for Protecting the Drop Down List

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