Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create Dynamic Dependent Drop Down List in Excel

Today I will be showing how you can create a dynamic dependent drop down list in Excel using both Formulas and Tables.


Download Practice Workbook

You can download the practice workbook from here.


What Is Dynamic Dependent Drop Down List ?

A drop-down list is a list of values from which you can choose a specific value for a cell.

In the image below, Cell E12 can select a value from a list of all the book-type names available. This is known as a drop-down list. Dynamic dependent drop-down list means a 2nd drop-down list whose content will update automatically when we select different content from the 1st drop-down list.

For example, I selected Poetry from the 1st drop-down list and the book name of the poetry type will appear in the 2nd drop-down list.

dynamic dependent drop down list

Whereas, if I select Science Fiction from the 1st drop-down list the 2nd drop-down list will change its content to the book name of a science fiction type book from the table.

Read more: How to Make a Drop Down List in Excel (Independent and Dependent)


2 Easy Methods to Create Dynamic Dependent Drop Down List in Excel

In this section, I will discuss 2 easy methods to create a dynamic dependent drop-down list in Excel.

1. Use Formulas to Create Dynamic Dependent Drop Down List

In the 1st method, I will use Excel formulas containing UNIQUE, OFFSET, COUNTA & FILTER functions to create a dynamic dependent drop-down list in Excel. For demonstration, I have used a dataset containing Book Type & Book Name.

Use Formulas to Creat Dynamic Dependent Drop Down List


STEP 1: Create Primary Drop Down List

For creating the 1st drop-down list follow the stepwise procedures given below.

  • Firstly, in Cell E4 write the following formula to create the list of content.
=UNIQUE(OFFSET(B5,0,0,COUNTA(B:B)-2,1))
  • Then, press Enter and an array of book types will be created.

Create Primary Drop Down List

  • After that, select the cell where you want to create the primary drop-down list. I have selected Cell E12.
  • Further, go to the Data tab and select Data Validation from there.

  • Furthermore, in the Data Validation window select List in Allow section.
  • In the Source section box, type $E$4 with a # sign at the end.
  • Finally, press OK.

  • Consecutively, we will see a drop-down list in Cell E12. We can select different Book Type from there.


STEP 2: Insert Second Drop Down List

It’s time to create the 2nd dynamic dependent drop-down list.

  • First, in Cell F4 write the following formula to create the list for Book Name.
=FILTER(C:C,B:B=E12)
  • Simultaneously, press Enter and we will see the Book Name list there.

Insert Second Drop Down List

  • Now, select Cell F12 and go to the Data tab.
  • Afterward, select Data Validation from there.

  • Next, from the Data Validation window select List in Allow section and in the Source section box, type $F$4 with a # sign at the end.
  • Finally, press OK.

  • Finally, we will see the dynamic dependent drop-down list.

  • If we change the element of 1st drop-down list, the content of 2nd drop-down list will change automatically.

dynamic dependent drop down list result


2. Utilize Table to Create Dynamic Dependent Drop Down List

Here, I will show the method to create a list from a table. For demonstration, I have used different book names under columns Novel, Autobiography, Science Fiction & Poetry.

Utlize Table to Create Dynamic Dependent Drop Down List

  • For making the table, select the whole dataset and press Ctrl + L.
  • Again, in the Create Table window, press OK.

Read more: How to Make a Drop-Down List Based on Formula in Excel


STEP 1: Make Primary Drop Down List

We will see the table is created. Now follow the steps given below.

  • First, write the following formula in Cell G4.
=TRANSPOSE(Table3[#Headers])
  • Instantly, we will see the primary list is created.

Make Primary Drop Down List

Note: In the formula, Table3 is the name of my table, change it if your table name is different.
  • Afterward, select Cell G10 and go to the Data tab.
  • Consecutively, select Data Validation from there.

  • After that, from the Data Validation window select List in Allow section.
  • Also, in Source section, type $G$4 with a # sign at the end.
  • press OK.

  • Simultaneously, we will see the primary drop-down list is created. We can select different Book Type from there.


STEP 2: Generate Second Drop Down List

It’s time to create the 2nd drop-down list which will be dynamic-dependent. Follow the steps given below.

  • First, write the following formula in Cell I4.
=INDIRECT("Table3["&G10&"]")
  • Then, press Enter and we will see the 2nd list created there.

  • Afterward, select Cell G13 and go to the Data tab.
  • Next, select Data Validation from there.

  • Further, from the Data Validation window select List in Allow section and in Source section select Cell $I$4 with a # sign at the end.
  • Simultaneously, press OK.

  • Finally, we will see the drop-down list in Cell G13.

  • We can change the element of 1st drop-down list and will see the content of the second list updated accordingly.

dynamic dependent drop down list result


Conclusion

Drop-down list is quite helpful for analyzing data in Excel. Here, I have shown 2 easy methods to create a dynamic dependent drop-down list in Excel. If you have any suggestions or queries, please leave a comment. Visit our ExcelDemy Website for similar articles regarding Excel.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

6 Comments
  1. Hi, I’m curious if you have to leave the full list of items used for the drop down menus visible? I’m trying to use this feature on an order form to eliminate typos and errors. Is it possible to create an order form with the drop down menu and not have the information visible at all times? If not, what would be the best way to accomplish this?

  2. Hi, is there a way to make the results of these lists editable?

    not the list itself but the results, e.g, if i click novel and then it populates with A Tale of Two Cities, can I then edit the column with the book title in it?

    So that every time it filters, we can have most of the same info, but a few things need to change, such as sizes etc, as we have parts but they aren’t standard, they all vary, and there are hundreds, too many to make drop down list out of. can this be done?

    Thanks

    • Hello Missy, If you change the list it will change the results, both are the same. Your question “can I then edit the column with the book title in it?” is not clear to us. It will be better for us, if you create a sample Excel file of what you want and share it with us. Thanks.

  3. Hi

    how do i add a sample workbook for you to see?

    Thanks

Leave a reply

ExcelDemy
Logo