Home > Validation > Drop Downs > Dependent

Create Dependent Drop-Down Lists

Debra Dalgleish - Contextures

See how to set up a dependent drop-down list in Excel -- conditional data validation, based on other cell. These dynamic dropdowns make it easy to enter data, so you don't have to scroll through a long list. My step-by-step videos show how to get started, and there are written steps, and Excel files to download.

test the dependent drop down list

1) What Is a Dependent Drop-Down List?

A dependent drop down list is a type of data validation in Microsoft Excel. Instead of always showing the same list, the dependent list of items changes automatically, based on the value in another cell.

For example, in the screenshot below, the values in column B control the drop down lists in column C:

  • Fruit was selected as Produce Type in cell B3, and the drop down showed a Fruit list in cell C3
  • Next, in cell B4, Vegetable was selected as Produce Type. In cell C4, the dependent drop downs changed automatically, and now it shows a Vegetable list

There are spreadsheet setup instructions for this technique, and a step-by-step video, in the sections below.

test the dependent drop down list

2) Video: Excel Dependent Drop-Down Lists

In this short video, I show how to set up a main drop-down list, with a dependent drop-down list in the next column.

The written instructions are below the video, and the full transcript is on the Dependent Drop-Down Lists Video page.

3) Get Started: Dependent Drop-Down Lists

Here are the step-by-step instructions for making dependent drop-down lists in Excel. This example uses 2 lists - Fruit and Vegetable.

Two Types of Produce

In the screen shot below, you can see the completed data entry sheet.

  • Select a produce type (fruit or vegetable) in column B, from the first drop-down list
  • Then, in column C, a dependent drop-down list shows items for the selected product type only.

test the dependent drop down list

Set-up Steps

There are a few steps for setting up the drop-down lists, and the details are in the sections below.

3a) Set Up the Worksheets

This example will have a workbook with two sheets -- a data entry sheet, and a sheet with lists.

  1. Create a new workbook
  2. Change the name for Sheet1 to "DataEntry"
  3. Insert a new sheet in the workbook, and name that sheet "Lists"

new workbook with two sheets

3b) Create Lists

Next, you will create lists with items for the drop-down lists.

In this example, you will set up 3 lists. The main list has produce types, and the other two lists have items for the dependent drop downs.

To create each list, you will do three things:

  1. Type the items on the Lists sheet
  2. Format the lists as an Excel table
  3. Name the lists.

3b1) Type the Main List

To type the lists, go to the Lists sheet. Start with the main list - Produce types.

  1. In cell B2, type the main list heading, Produce List
  2. In cells B3 and B4, type Fruit and Vegetable.

NOTE: The Produce list has one-word items in it -- Fruit and Vegetable. This is important, because those words will be used as Excel names, and two-word names are not allowed as names. If you need to use multiple-word items in the main list, use the Dependent Lists with Tables technique instead.

3b2) Add the Dependent Lists

Next, type the dependent lists, with the headings Fruit List (in cell D2) and Vegetable List (in cell F2). These lists can contain one-word items (apple), or multiple-word items (green beans).

type 3 lists

3b3) Format Lists as Tables

Next, follow these steps, to format each list as an Excel Table. This makes your list dynamic -- the list size will adjust automatically if you add or remove items, so your drop down will show the entire list.

Follow these steps for each of the 3 lists:

  1. Select a cell in the list, and on the Home tab, click Format as Table
  2. format as table

  3. Click on one of the Table Styles
  4. Add a check mark for My table has headers
  5. Click OK

    named table

Here is the Lists sheet, with all 3 lists formatted as Excel Tables

lists formatted as Excel tables

3b4) Create a Named Range

Next, follow these steps to create a named range for each formatted Excel Table. Later, you will use this name, when making the drop-down lists on the Data Entry sheet.

3b5) Name the Produce List
  1. Select the items in the Produce table (cells B3:B4), but not the table heading.
  2. Click in the Name box, to the left of the formula bar
  3. Type a one-word name for the list -- Produce
  4. Press the Enter key, to complete the name.
  5. First named range

3b6) Name the Fruit and Vegetable Lists

Next, follow these steps to name the fruit and vegetable lists.

  1. Select the fruit names in cells D3:D6
  2. Click in the Name box, to the left of the formula bar
  3. Type a one-word name for the list -- Fruit.
  4. Press the Enter key, to complete the name.
  5. Second named range

  6. Select the vegetable names in cells F3:F6.
  7. Click in the Name box, to the left of the formula bar
  8. Type a one-word name for the list -- Vegetable
  9. Third named range

  10. Press the Enter key, to complete the name.

3c) Add the Main Drop Down

Next, you will add the main drop down (Produce Type) on the DataEntry sheet. This is a normal data validation drop-down list - not a dependent drop down

3c1) Start the Data Entry Sheet

First, you will enter headings on the data entry sheet, and set up a named table.

  1. On the DataEntry sheet, type the headings in the range of cells,B2:C2 -- ProductType and Item
  2. Select cell B2, and on the Home tab, click Format as Table
  3. Click on one of the Table Styles
  4. Add a check mark for My table has headers
  5. Click OK

start data entry sheet

3c2) Add the Main Drop Down

Next, follow these steps, to add the main drop-down list, in the Produce Type column

  1. On the DataEntry sheet, select cell B3
  2. On the Ribbon, click the Data tab, then click Data Validation.
  3. In the Data Validation dialog box, choose List, from the Allow drop-down list
  4. In the Source box, type an equal sign and the list name: =Produce
  5. Leave the Ignore Blank check box checked
  6. Leave the In-cell dropdown check box checked
  7. start data entry sheet

  8. (optional) Go to the Input Message tab, and enter a message that appears when the data validation cell is selected.
  9. (optional) Go to the Error Alert tab, and enter a warning message that appears if invalid data is entered in one of the data validation cells. Or, uncheck the Show Error Alert box, if you don't want error messages or warnings to appear.
  10. Click OK, to complete the data validation setup.
  11. To test the drop down, click the arrow in cell B3, and select Fruit from the list

select fruit from the drop down list

3d) Add the Dependent Drop Down

Next, you will create a second drop-down list -- a dependent drop down list in the Item column. This cell will have an INDIRECT formula to create the data validation drop down list. Read more about the INDIRECT function here.

  1. On the DataEntry sheet, select cell C3
  2. On the Ribbon, click the Data tab
  3. In the Data Tools group, click the Data Validation button
  4. On the Settings tab, from the drop-down list in the Allow box, choose List
  5. In the Source box, type an equal sign and INDIRECT function, referring to the first data cell in the Produce Type column:
    In this example, cell C3 is active, so the following formula has a cell reference to the Product Type cell in row 3: =INDIRECT(B3)
    Note: If you click on cell B3, Excel will add an absolute reference -- $B$3. Remove the $ signs, because we need a relative reference in this formula
  6. Click the OK button (or press the Enter key on your keyboard).

INDIRECT formula for dependent drop down list

Note: If cell B3 is empty, you will see the message shown below. Click Yes to continue.

  • The Source currently evaluates to an error. Do you want to continue?

Source error message

3e) Test the Drop-Down Lists

To test the dependent drop-down lists, follow these steps

  1. Fruit should be selected in cell B3 -- if not, select it now.
  2. Select cell C3, then click the arrow, and then select one of the fruit items from the list

    test the dependent drop down list

  3. Next, press the Tab key, to start a new row in the data entry table.
  4. In the Produce Type column, select Vegetable from the Produce drop down list.
  5. Move to the Item column, in the same row.
  6. In the Item column, click the drop-down arrow, and select an item from the Vegetable list

test the dependent drop down list

NOTE: If a Produce Type has not been selected, the Item drop down in that row will not work.

5) Advanced Dependent Drop Downs

Complex Dependent Drop Downs

  • If you need more than 2 or 3 named ranges for your dependent lists, I recommend using the Dependent Lists with Tables technique instead. That method is much easier to set up and maintain.

More Advanced Techniques

To see advanced dependent drop-down list techniques, go to the Advanced Dependent Drop Downs page.

On that page, you'll see how to:

  • Show a short list or full list
  • Use IF with INDIRECT
  • Prevent invalid selections
  • Fix OFFSET formula list problems

5) Download Sample Files

  1. Download the sample file for dependent data validation Intro - Fruit/Vegetable example
  2. Download the example file for dependent drop down list with IF - City/Country/World example
  3. Download the sample file for dependent data validation Advanced - 3rd list; 2-word/illegal
  4. Download the sample file for dependent data validation with Dynamic Lists - Fruit/Other Stuff/Veg example
  5. Download the sample file for Clear Dependent Cell After Selecting
  6. Download the sample file for Short or Long Drop Down List of Customers.

NOTE: There are other techniques for setting up dependent drop down lists, so see which setup method might be best for your needs.

More Dependent Drop Down Tutorials

Dependent Drop Down Setup Choices

Dependent Lists with Tables

Dependent Drop Down from Dynamic Arrays

Dependent Drop Downs from a Sorted List

Dependent Lists With INDEX

Advanced Dependent Drop Downs

 

 

Last updated: April 13, 2024 4:11 PM