Go to to get the sample workbook
Select an item from an Excel drop down list, and see only the related items in the next drop down list. In this example, select Fruit from the first drop down, and only fruit names appear in the next column’s drop down list.
Instructor: Debra Dalgleish, Contextures Inc.
Get Debra’s weekly Excel tips:
More Excel Tips and Tutorials:
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
07:39 Get the Sample File
Video Transcript (abridged)
In this video, you’ll see how to create two dropdown lists. The first one, for produce type, fruit or vegetable. In the next column, there’s a dependent dropdown list, it will change based on what you selected in the other column.
Here it shows a list of vegetables. If I go to next row and select fruit, Column C shows list of fruit.
Here’s a new workbook where we’re going to set up dropdown lists. We’ve got 2 sheets. One is Data Entry, other one is called Lists. Start a new blank workbook and name the 2 sheets.
I typed Produce Type and Item on this sheet, where we’re going to create dropdown lists.
On the other sheet, I’ve got 3 lists set up, Produce List with Fruit and Vegetable, and a fruit list, and vegetable list, with 4 vegetables.
The produce list is our main list. We have 2 items, and it’s important to use a one-word name in this list for each produce type. We’ll those names later, and Excel won’t allow spaces.
Each list will be changed to a formatted Excel table. In the first one, click anywhere in the list. On the Home tab, go to Format as Table, select a format that you like.
My list is B2 to B4, and has a header. Click OK, and there’s our formatted list. It has a dropdown list in the header cell, to filter or sort, and a corner tab here to change the size of this table. If we add new items, the table automatically expands to include them.
We’ll do the same thing for the fruit and vegetable list. Click anywhere in this list, Format as Table. Select a color. Again, it has headers. And same for vegetable, green, has header.
The next step is name cells with items in each list. Select cells B3 and B4, with produce type names, and to name these 2 cells, click the Name Box. Type a one-word name, Produce, then press Enter, to complete the name.
It still shows Table1, but click the arrow to see Produce. Give the fruit list a name, it has to be an exact match for the item in this produce list. Select these 4 cells, click in Name Box, type fruit, press Enter. Upper or lower case doesn’t matter, as long as spelling is the same.
Now we have a fruit list, do the same here, this will be called Vegetable, press enter. Now we can see all 3 names, Fruit, Produce, and Vegetable, and table names.
Next, set up the data entry sheet. I’ve got headings entered, and I’ll create a table from these 2 cells, then add items in rows below.
With a heading cell selected, on Home tab, Format as Table, select style you like. Again, my table has headers, OK. It creates a table with one blank row below headings.
We’ll set up a very simple dropdown in this cell, where we can select a produce types, fruit or vegetable.
Go to Data tab, click data validation, on Settings tab, select List.
For source, we’ll use our produce list. On a Windows computer, press F3 key, to see names you’ve created. Click Produce, OK, it puts in an = and the name of that range.
If you don’t have that shortcut, type =produce, click OK.
Now we’ve got a dropdown arrow when this cell is selected, select fruit or vegetable.
In this cell, we’ll create dropdown that depends on what we selected. If Fruit is selected, it will show fruit list, if we chose Vegetable, we’ll only see vegetables.
To create this dropdown, on Data tab, click Data Validation. We want a list, and this time, we’ll use a formula.
Type =. The function we’re going to use in this formula is INDIRECT, then click an open bracket.
We want to use whatever name has been selected, so type B3, close that bracket.
Instead of typing fruit or vegetable, we’ll use INDIRECT function to get name that we want to use from cell B3, and create a reference to that list, click OK.
It will create a list based on that produce type. Select lemon. Go to next row and type fruit again, it shows fruit list.
Change to vegetable, it shows list of vegetables. So this is dependent dropdown list.
We create a main dropdown, then a dependent dropdown- items in that list depend on cell we told it to look at.