Create a Drop Down List in Excel From Another Worksheet

Screenshot/Microsoft Excel

Creating a drop down list in Excel allows you to enter data into a specific cell of a worksheet from a preset list of entries.

The benefits of using a drop-down list include:

  • Making data entry easier
  • Preventing data entry errors
  • Restricting the number of locations for entering data

Excel Drop-Down List Step by Step Tutorial Topics

  • Entering the Tutorial Data
  • Creating a Named Range for the Data
  • Starting the Drop-Down List
  • Using a List for Data Validation
  • Entering the Data Source and Completing the Drop-Down List
  • Changing the List Items
  • Options for Protecting the Drop Down List

Entering the Tutorial Data

Excel Data Validation List
Excel Data Validation List. © Ted French

The first step to creating a drop down list in Excel is to enter the data.

Note: The tutorial instructions do not include formatting steps for the worksheet.

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.

Enter the data below into the cells indicated on sheets one and two of an Excel workbook.

Tutorial Steps

  1. Enter the following data into the correct cells on Sheet 1 of the worksheet:
    D1 - Cookie Type:
    
  2. Click on the Sheet tab for Sheet 2.
     
  3. Enter the following data into the correct cells on Sheet 2 of the worksheet:
     
    A1 - Gingerbread
    A2 - Lemon
    A3 - Oatmeal Raisin
    A4 - Chocolate Chip
    

The drop down list will be added to cell E1 on sheet 1.

Creating a Named Range for the List Data

Excel Data Validation List
Excel Data Validation List. © Ted French

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, the named range is used as the source for the list items.

Tutorial Steps

  1. Drag select cells A1 - A4 on Sheet 2.
  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 on sheet 2 now have the range name of "cookies"
  6. Save your worksheet

Opening the Data Validation Dialog Box

Opening the Data Validation Dialog Box
Opening the Data Validation Dialog Box. © Ted French

All data validation options in Excel, including drop down lists, are set using the data validation dialog box.

The data validation dialog box is located under the data tab of the ribbon.

Tutorial Steps

  1. Click on the Sheet 1 tab at the bottom of the screen to switch to sheet 1
  2. Click on cell E1 to make it the active cell - this is where the drop down list will be located
  3. Click on the Data tab of the ribbon menu above the worksheet
  4. Click on the Data Validation icon on the ribbon to open the drop down menu
  5. Click on the Data Validation option in the menu to open the Data Validation dialog box

Using a List for Data Validation

Excel Data Validation List
Excel Data Validation List. © Ted French

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.

Some of the more commonly used options:

  • List
  • Dates and/or times
  • Whole numbers
  • Text
  • Formulas (set under the Custom option)

In this step, we will choose the List option as the type of data validation to be used for cell E1 on sheet 1.

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 D1 and to activate the Source line in the dialog box

Entering the Data Source and Completing the Drop Down List

Excel Data Validation List
Excel Data Validation List. © Ted French

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

Steps

  1. Click on the Source line
  2. Type " = cookies " (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 E1
  5. Clicking on the down arrow should open the drop down list containing the four cookie names entered into cells A1 to A4 on sheet 2
  6. Clicking on one of the names should enter that name into cell E1

Editing the List Items

Editing 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 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 on sheet 2 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 Shortbread to Oatmeal Raisin in the drop down list by changing the data in cell A3 of the named range.

Steps

  1. Click on cell A3 on sheet 2 (Shortbread) to make it the active cell
  2. Type Oatmeal Raisin into cell A3 and press the Enter key on the keyboard
  3. Click on the down arrow for the drop down list in cell E1 of sheet 1 to open the list
  4. Item 3 in the list should now read Oatmeal Raisin instead of Shortbread

Options for Protecting the Drop Down List

Protecting the Drop Down List in the Excel
Protecting the Drop Down List in the Excel. © Ted French

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

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 a password can be added when protecting the worksheet to prevent changes to the list items.

Creating a Drop Down List on a Different Worksheet

A drop down list allows you to enter data into an Excel spreadsheet from a preset list of entries.

Part 1 covers the steps to creating a drop down list with data on the same sheet as the drop down list.

This tutorial covers creating a drop down list on a different worksheet.

Example: Creating a drop down list with data on a different worksheet

    Enter the following data into the correct cells on Sheet 1 of a worksheet:
    E1 - The Cookie Shop
    D2 - Cookie Type:
    Click on the Sheet tab for Sheet 2.
    Enter the following data into the correct cells on Sheet 2 or a worksheet:
    A1 - Gingerbread
    A2 - Lemon
    A3 - Oatmeal Raisin
    A4 - Chocolate Chip
    Highlight cells A1 - A4 on Sheet 2.
    Type " cookies " (no quotes) in the Name Box and press the Enter key on the keyboard.
    Click on the Sheet tab for Sheet 1
    Click on cell E2 - the location where the results will be displayed
    Click on the Data tab
    Click on the Data Validation option from the ribbon to open the menu
    Click on the Data Validation in the menu to bring up the dialog box
    Click on Settings tab in the dialog box
    From the Allow menu choose List
    Type =cookies on the Source line in the dialog box
    Click OK to close the dialog box and return to the worksheet
    A down arrow should appear next to cell E2
    When you click on the arrow the drop down list should open to display the four cookie names