How to Create a Drop Down List Using Data From Another Sheet

An Excel list can pull data from a different worksheet

A blank spreadsheet in Microsoft Excel

Ted French

Creating a drop-down list in Excel lets you enter data into a specific cell of a worksheet from a preset list of entries. Once the drop-down list is made, editing the entries is as easy as changing the cell data.

You might make a drop-down list in Excel to make data entry easier, to prevent data entry errors, or to restrict the number of locations for entering data.

Below is an example of a drop-down list in Excel that you can follow along with to make a simple list of data and a menu that pulls the list data into a cell located on a different sheet.

Enter All the Data

Excel Data Validation List

Ted French

The first step to creating a drop-down list in Excel is to enter the data. Make an additional sheet in Excel if you don't already have one, and then follow these steps in order.

  1. Open Sheet1 and type Cookie Type: into cell D1.
    1. The drop-down list will be created in E1 on this sheet, right next to this entry.
  2. Open Sheet2 and type the following:
    1. A1: Gingerbread 
    2. A2: Lemon
    3. A3: Oatmeal Raisin 
    4. A4: Chocolate Chip 

Tip: You can format these cells however you wish; it won't interfere with the functionality of the drop-down list.

Name the List Data Selection

Excel Data Validation List

Ted French

A named range lets you refer to the list data as a common name instead of as individual cells. To do this, select the cells that will be in the list and name the selection.

  1. Select the cells used for the drop-down list: A1–A4 on Sheet2.
  2. Click the Name Box above Column A and below the Excel menu.
    1. It might say A1 or A4 right now.
  3. Type Cookies in the Name Box.
  4. Press Enter.​
    1. Cells A1 through A4 on Sheet2 now have the range name of "Cookies."

Tip: Named ranges have many uses in Excel including using them in formulas and when creating charts.

Make the Drop Down List

Opening the Data Validation Dialog Box

Ted French

The Excel drop down list is created through the Data Validation dialog box. You'll select the list option and then explain where the list data is located.

  1. Select E1 from Sheet1.
    1. If you want your drop-down list located elsewhere, go to that cell instead.
  2. Click Data > Data Validation from the menu/ribbon.
  3. Click List from the Allow menu in the Settings tab.
  4. Type =Cookies in the Source text box.
  5. Click OK.

In E1 on Sheet1 is now a small arrow you can click to see the list items you made. Clicking one immediately inserts the data into the cell.

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

How to Edit the Drop Down List Items

Editing the Drop Down List Items

Ted French

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

Since you used a name range as the source for the list, changing the cookie names in cells A1 through A4 of Sheet2 immediately changes the names in the drop-down list.

For example, if you change Oatmeal Raisin to be Shortbread, and then re-open the drop-down list in Sheet1, you'll see that the new entry is available and the original is not. ​

Of course, this is because the drop-down list is gathering data from the list of entries in the other worksheet, so anything you change in the second sheet is reflected in the drop-down list.

Options for Protecting the Drop Down List

Protecting the Drop Down List in the Excel

Ted French

Since your data is on a different worksheet than the drop down list, you have two options for protecting the list data.

If security is not a concern, hiding the worksheet containing the list data is a good option since it makes it easier to update the list when needed.

If security is a concern, you can add a password to the sheet so that only after it's entered can someone edit the list.