How to Create Dependent (Cascading) Drop Down List in Excel Using 5 Different Techniques

So far in this Data Validation series, we have learnt to create normal drop down list and dynamic drop down list using different techniques with data validation in Excel.

And, today, in this chapter, we will show you how to create Dependent Drop Down list in Microsoft Excel, using different methods.

Dependent Drop Down list is also known as cascading data validation, and, it limits the choices in a drop down list, depending on the value selected in the other cell that contains data validation. In other words, it depends on the value selected in the first drop down list which determines the values to be displayed in the second drop down list.

This is a very common scenario of working with large data or some dynamic reports, where you want to have 2^nd cell display a list that is dependent on the list item chosen in first drop down.

As we know that in excel, there are plenty of ways for doing a particular task and similarly, there are many ways of creating dependent data validation in Excel. And, today, we will demonstrate 5 different techniques to create dependent data validation list.

The raw data can be in any order or format and every time you can’t change the data or format to get what you are looking for.

So, we’ve taken one data set but in 3 different formats to get the dependent drop down list. And, As you can see our data is on the left side that is from column A to column E, and we will have our expected output on the right side that is in column J & K. Column J is going to have the primary validation list whereas the column K will be dependent and will display the values depending on the value selected in the column J.

1^st Example:-

2^nd Example:-

3^rd Example:-

1^st Example:-

We have product list for each product code from column A8 to E13. And, we want to select the product code in J10, then depending on the selected product code, a product name in cell K10.

First Method:-

The first method is very simple and short, and it requires only 3 steps to get the depending drop down list. However, it only works successfully until you don’t make any changes to your range. Once, you modify your data then you will have to modify the named range first in order to get the updated cascading data validation.

Follow the steps given below:-

• Select the entire table from A8 to E13

• Then go to the “Formulas” tab, then under “Defined Names” category, click on “Create from Selection”
• You can also use the keyboard shortcut CTRL+ SHIFT + F3
• Create Names from Selections dialog box will appear

• It asks to confirm which rows and column to be used to create the names for other rows and columns. We confirm to use the “top row” to create the names and uncheck the 2^nd option and then we click on OK

Note: - Spaces and other special characters except underscore and period are not allowed as names. By default, it will be converted into underscore. So, use underscore character and period to separate words. Also, the first letter cannot be a number; it must be a letter, an underscore or a backslash.

• Now, to confirm that each range has a name, we go to the “Name Manager” (press CTRL + F3)
• There we can see all 5 named ranges available
• And also we can see that each range name has underscore instead of blank in middle of the string

Now, we will create drop down list:-

• Select cell J10 and press ALT++D+L to open Data Validation dialog box
• Select List > then enter the range A8:E8 in Source tab

• Click on OK
• Now we will create dependent list in cell K10
• Open Data Validation dialog box by pressing the key ALT+D+L
• Select List, in source enter this function :- =INDIRECT(SUBSTITUTE($J$10," ","_"))

In data validation, to create the dependent list, we have used INDIRECT function to return the value based on the primary data validation list. And, to replace underscore with space, we will use SUBSTITUTE function inside INDIRECT function.

• Click on OK

When we select any product code in cell J10 then product list of selected product code will appear in cell K10. For example: - We have selected ETV 501, now you can see dependent product list is appearing in cell K10

Note: - Whenever you will add the product name and product code that will not appear in the list.

For Example: - We have added Product 26 under the ETV 505 product code, but when we select ETV 505 product then added product does not appear in the drop down list.

So, this is how you can create dependent drop down list using simple technique in just 3 simple steps.

2^nd Example:-

In this example, we will see how to get a dependent dropdown list when you have your data like shown in this vertical table.

We will use two different methods to create dependent drop down list. Both are almost similar techniques. However, the one is without the named range and the other will have the named range.

1^st Method:-

To do the same, we will use OFFSET, MATCH & COUNTIF functions together.

Since we know OFFSET function is used to create the dynamic range, therefore, to create “Dynamic data validation” list, we use OFFSET function to return the dynamic range.

MATCH is used to return the relative position of an item in a list in Excel. And, here it will help us to match the category selected in primary drop down list in our range on the sheet, and it will return a number.

And, COUNTIF is used to get the count of cells that matches criteria. And, here we will use this to count the number of rows to show up by using the COUNTIF function.

Follow the steps given below:-

• Select the cell J21, in which we will create our primary data validation list
• Press the key ALT+D+L to open Data Validation dialog box
• Select list from allow category
• Click in Source tab and select the range  from B20:B24

• And click on OK

• Go to cell K21 and open the data validation dialog box once again
• Then we select List and, in source, enter below function:
• =OFFSET($E$19,MATCH($J$21,$D$20:$D$32,0),0,COUNTIF($D$20:$D$32,$J$21))

• Click on OK
• In cell K21, we can see all the corresponding values of selected product code:-

So, this is how you can get the dependent list by taking cell references in the function.

2^nd Method:-

In the next method, we will use named range in the same function to get the cascading data validation. First we’ve to create the dynamic list for product code. In case of any new product added to the data, the drop down should be updated to display the same.

To do the same, follow the steps given below:-

• Select B19, then press CTRL + F3 to open the “Name Manager” window
• Now, we click on “New” and “Define Name” dialog box appears
• We can see, the name already appears in the name box -that’s because we’ve selected B9 before opening the “Name Manager” window. And, as B19 has text in it, if we want, we can change it to some other name.

• Enter below mention formula:-

=OFFSET('DependentDropDownList'!$B$20,0,0,COUNTA('DependentDropDownList'!$B$20:$B$32))

• Click on OK

As we have created a dynamic list for unique products, now, we will create dynamic range for product code range which is in column D.

Follow the same steps which we have followed for unique product:-

• Select the cell D19, open Define Name dialog box
• You will found name is already there
• In refers, enter the below formula:-

=OFFSET('Dependent Drop Down List'!$D$20,0,0,COUNTA('Dependent Drop Down List'!$D$20:$D$35))

• Click on OK
• Now both dynamic ranges are ready. So, we go to J22 and press “ALT + D + L” and select “List”
• In source, we will have the named range which we defined it for “Unique Product Code” so, we press F3 to see all the available named ranges
• We can see “Unique Product Code” named range, so we click on it and then click on OK and we hit enter

• The moment we hit enter, we get drop down arrow in cell J22, which holds the list of unique product codes

• Select cell K22 and open the “Data Validation” dialog box
• We will use the same function which we have used in the last method but with named range
• Select list, and then in source, enter below formula:-

=OFFSET($E$19,MATCH($J$22,Product_Code,0),0,COUNTIF(Product_Code,J22))

• Click on OK
• Now, we’ve got the primary drop down as well as child list of products
• Select “ETV-101” product from J22, and in K22, we can only see the names that fall under this “ETV-101” product. And, when we change any product (“ETV-103) in J22, K22 displays the corresponding values for that code

Now we will see what happens when we add any new product code to the list? Will these drop-down lists be updated?

Let’s add new product in the list; Follow the steps given below:-

• Add product code in the list of Unique_Prod_Code

• Also add the Product_Code and Product_Name in the data:-

• Now check the drop down list -added product code and name is appearing

3^rd Example:-

We have the dynamic headings directly from the table, and we will add new products to the range. The table is in the same format which we used for 1^st method.

4^th Method:-

Follow the steps given below:-

• Select the heading A40:E40
• Create the dynamic range for headings first, open “Define Name” dialog box
• Write “Heading” in the place of name, and then in “refers to”, enter below mentioned formula:-
• Enter the below function:-
• =OFFSET('Dependent Drop Down List'!$A$40,,,,COUNTA('Dependent Drop Down List'!$40:$40))
• Click on OK

• Dynamic “Heading” range is ready now

And, now we will create the named range for each heading, follow the steps given below:-

• Select the table from A40 to E50
• CTRL + SHIFT + F3 keyboard shortcut
• We uncheck the 2^nd option
• And, before we click on OK, ensure the 1^st option “Top row” is selected

• Now we are ready with both ranges

Now, we will prepare the parent drop down list

• Select the cell J42
• Open the Data Validation dialog box

• Then after selecting “List”, we press F3 in source to get the named range for headings. We click on “Heading” and then click on OK and hit enter. We’ve got the parent list in J42 now

• To create the list of Item Detail, select the cellK42
• Open Data Validation dialog box  by pressing the key ALT+D+L
• Select List then enter below function in Source tab:-
• =OFFSET(INDIRECT(SUBSTITUTE($J$42," ","_")),,,COUNTA(INDIRECT(SUBSTITUTE($J$42," ","_"))))

• Click on OK

Now select some item in the J42, say we select “Item 01” and the look at the K42 drop down list. And, like previous 3 methods, we got dependent list here as well.

So, what’s new?  In the first example, you could not add any product to the list but here, you can add any new product. So, say we add new product to this item. We go to A45, and we type “ETV-501 Prod 05” and then come back to K42 and here you go. You can see, the new product has been added.

• Now add few products under the new item

When we select “Item 06”,we go to K42 and click on drop down list. Surprisingly, nothing happens when we click on the drop down arrow. That’s because we have created everything dynamic and forgot to create dynamic range for table which is why the products do not get displayed in the child list.

To do that, we need to use different techniques. There are two methods of doing that. You can either create the table or simply use the OFFSET function only. And, in the next method, we will use OFFSET function, and we’ll see the trick to expand the table range as well.

• So, we first go to J43 and press “ALT + D + L”
• We select “List” and, then in source, we press F3 and select “Heading” and click on OK and then hit enter

• Now, we go to K43, and after selecting “List”, we go to “Source” and enter below mentioned function

=OFFSET($A$40,1,MATCH($J$43,$40:$40,0)-1,COUNTA(OFFSET($A$40,1,MATCH($J$43,$40:$40,0)-1,1000,1)))

• Click on OK

Now, we go back and select “Item 06 in the J43 cell and return to K43 and click on drop down arrow. But, this time list shows products which we have added for new item.  And, we select first product “ETV-506 Prod 01”.

This is how you can create the dependent drop down list using different methods on any type of data format.

Video: How to Create Dependent (Cascading) Drop Down List in Excel Using 5 Different Techniques in Microsoft Excel

Click on the video link for quick reference to the use of it. Subscribe to our new channel and keep learning with us!

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com