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.

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.

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.

• Then, press Enter and an array of book types will be created.

• 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.

• Simultaneously, press Enter and we will see the Book Name list there.

• 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.

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.

• 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.

• Instantly, we will see the primary list is created.

• 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.

• 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.

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

• How to use IF Statement to Create a Drop-Down List in Excel
• Create Drop Down List in Multiple Columns in Excel (3 Ways)
• Conditional Drop Down List in Excel (Create, Sort and Use)
• How to Remove Drop-Down List in Excel
• Create a Drop Down List From Another Sheet in Excel (2 Methods)
• Multiple Dependent Drop-Down List Excel VBA (3 Ways)