How to Create a Drop-Down List Using Data From Another Excel Worksheet

An Excel list can pull data from a different worksheet

What to Know

  • Sheet1 contains the drop-down list and Sheet2 is your data source.
  • On Sheet2: Select the data you want to use and type a name for it in the Name Box.
  • On Sheet1: Select a cell, then select Data > Data Validation. Under Allow, select List. In Source, type =my list name.

This article explains how to create a drop-down list in Excel that pulls data from a different worksheet. This allows you to enter data into a specific cell of a worksheet from a preset list of entries. Instructions cover Excel 2019, 2016, 2013, and 2010 and Excel for Mac 2019, 2016, and 2011.

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.

  1. Open Sheet1 and type Cookie Type: into cell D1.

    Creating a drop-down list in Excel
  2. Open Sheet2. In cells A1 through A4, type: Gingerbread, Lemon, Oatmeal Raisin, and Chocolate Chip.

    Creating a drop-down list in Excel

    Format these cells however you wish. It won't interfere with the functionality of the drop-down list.

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:

  1. Select the cells you want to use for the drop-down list. For this example, select cells A1–A4 on Sheet2.

  2. Select the Name Box above Column A and below the Excel menu. It might say A1 or A4.

    Creating a drop-down list in Excel
  3. Type Cookies in the Name Box.

    Creating a drop-down list in Excel
  4. Press Enter.​

  5. Cells A1 through A4 on Sheet2 now have the range name of Cookies.

    Named ranges have many uses in Excel. For example, they make creating formulas and charts easier.

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.

  1. Select E1 from Sheet1. (If you want your drop-down list located elsewhere, go to that cell instead.)

  2. Select Data from the ribbon.

    Creating a drop-down list in Excel
  3. Click the Data Validation icon in the Data Tools group and choose Data Validation or Validate from the drop-down menu, depending on the version of Excel you are using.

    Creating a drop-down list in Excel
  4. Select the Settings tab in the pop-up.

  5. Select List from the Allow menu.

    Creating a drop-down list in Excel
  6. Type =Cookies in the Source text box.

  7. Select OK.

    Type Cookies and click OK
  8. A small arrow appears in cell E1 on Sheet1. Select this arrow to see the list items. Choose an item in the list to insert the data into the cell.

    To delete the drop-down list, select cell E1, reopen the Data Validation dialog box, and select Clear All.

    The drop-down button

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.

Creating a drop-down list in Excel

For example, if you change Oatmeal Raisin to Shortbread, the drop-down list shows the new entry.

Creating a drop-down list in Excel

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.
FAQ
  • How do I remove a drop-down list in Excel?

    To remove a drop-down list from your excel spreadsheet, select the cell containing the drop-down and then select Data > Data Validation > Settings > Clear All > OK.

  • How do I add a filter to a drop-down list in Excel?

    Select the cell with the drop-down list and then select Data > Data Validation > Settings > List. Then enter the range of items you want to have appear in the drop-down list under Source.

Was this page helpful?