Home > Validation > Dependent > Video Dependent Drop Down Lists Video Show different items in an Excel dependent drop down list, based on the value selected in another cell. Watch the video below, to see how to set up the drop downs. The full transcript and sample Excel workbook are below the video. For written step-by-step written instructions, and other examples, go to the Create Dependent Drop Downs page.

Video: Dependent Drop Down Lists

In this video, see how to set up dependent drop down list. Then, select Fruit or Vegetable in the first column, and the dependent drop down will only show matching items.

The video timeline is below, and the full transcript is in the next section. Click here to get the completed sample workbook.

Video Timeline

Here is the video timeline, to help you find a specific part of the video, if you want to skip forward, or watch a section again.

• 00:00 Intro
• 00:39 Set Up the Worksheets
• 01:15 One-Word Names
• 01:41 Create Tables
• 02:55 Name the List Items
• 04:15 Main Drop Down List
• 05:48 Dependent Drop Down Lists

Transcript: Dependent Drop Down Lists

Here is the full transcript for the Dependent Drop Down Lists video.

Intro

In this video, you'll see how to create two drop down lists. The first one, for produce type, we select fruit or vegetable. Then in the next column, there's a dependent drop down list, and it will change based on what you've selected in the other column.

So here, it shows a list of vegetables. If I go to the next row and select fruit, Column C shows a list of fruit.

Set Up the Worksheets

Here's the new workbook where we're going to set up the drop down lists. We've got two sheets. One is called Data Entry, and the other one is called Lists. So you can start a new blank workbook and just name the two sheets.

I have typed Produce Type and Item on this sheet. This is where we're going to create the drop down lists.

And on the other sheet, I've got three lists set up. So one is called Produce List, and I've got Fruit and Vegetable here, and I've created a fruit list, and vegetable list, and I've got four vegetables.

One-Word Names

This list, the produce list, is our main list. We have two items, and it's important that we're using a one-word name in this list for each of the produce types. We're going to be using those names later, and Excel won't let us use anything with a space in it.

Create Tables

Each of these lists is going to be changed into a formatted Excel table. I'll do the first one. I'll just click anywhere in this list. And on the Home tab, go to Format as Table, and select any format that you like.

My list is B2 to B4, and it does have a header. So I'll click OK, and there is our formatted list. This table has a drop down list in the header cell, that lets us filter or sort, and we've also got a little corner tab here that lets us change the size of this table. And if we add new items, the table would automatically expand to include them.

We're going to do the same thing for the fruit and vegetable list. So for this one, I'll click anywhere in this list, Format as Table. I'll select that color. Again, it does have headers. And the same for vegetable. I'll make that green. Has header.

Name the List Items

The next step will be to name the cells that have items in them in each list. So I'm going to select cells B3 and B4, where we have the produce type names, and to name these two cells, I'm going to click on the Name Box here. I'm going to type a one-word name. And this list, I'm going to call Produce, so I'll type it in here, and then press the enter key. And that completes the name.

Now, it still shows Table1, but if I click the arrow here, you can see Produce.

I'm going to give the fruit list a name, and it has to be an exact match for the item in this produce list. So I'll select these four cells, click in the Name Box, and type fruit, and press enter. Upper or lower case doesn't matter, as long as the spelling is the same.

So now we have a fruit list, and I'm going to do the same here, and this will be called Vegetable. And press enter.

So now, we can see all three names, Fruit, Produce, and Vegetable, and the table names as well.

Main Drop Down List

Next, I'm going to set up the data entry sheet. So go over to it. I've got my headings entered already, and I'm going to create a table from these two cells, and then we'll add items in the rows below.

So with one of those heading cells selected, on the Home tab, Format as Table, and select the style you like. And again, my table has headers. And OK. So it creates a table with one blank row below the headings.

We're going to set up a very simple drop down in this cell, where we can select one of the produce types, fruit or vegetable.

So I'll go to the Data tab, click here for data validation, and on the Settings tab, we want a list, so instead of any value, I'll click and select List.

For the source, we want to use that produce list that we created. If you are on a Windows computer, you can press the F3 key, and you'll see the names you've created. So click Produce, and OK, and it puts in an equal sign, and the name of that range.

If you don't have that shortcut available, you can just type =produce, and then click OK.

Now we've got a drop down arrow when this cell is selected. And we can select fruit or vegetable.

Dependent Drop Down Lists

And in this cell, we're going to create a drop down that depends on what we've selected here. So if Fruit is selected, it will show the fruit list, and if we've chosen Vegetable, we'll only see vegetables here.

To create this drop down, on the Data tab, again, click Data Validation. We want a list, and this time, we're going to use a formula.

So I'll type =. The function we're going to use in this formula is called INDIRECT. And then click an open bracket.

And what we want to use as the name of our list is whatever name has been selected here. So I'm gonna type B3, close that bracket.

So, instead of typing fruit or vegetable here, we're going to say, use the INDIRECT function to get the name that we want to use from cell B3, and create a reference to that list. So I'm going to click OK.

So now it should create a list based on that produce type. And so now, we can select lemon. If I go to the next row and type fruit again, it would show the fruit list.

If I change this to vegetable, it's going to show the list of vegetables. So this is dependent drop down list.

We create a main drop down, and then a dependent drop down, where the items in that list depend on the cell that we told it to look at.

Get the Sample File

For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com.

Get the Sample File

Get the completed workbook for Dependent Drop Down Lists. The zipped file is in xlsx format, and does not contain macros.

More Tutorials

Data Validation Basics

Create Dependent Drop Down Lists

Dependent Drop downs from a Sorted List

Dependent Lists With INDEX

Hide Previously Used Items in a Drop down List

Use a Data Validation List from Another Workbook

Data Validation Criteria Examples

Data Validation Tips

Data Validation With Combo Box