# How to Create Dynamic Dependent Drop Down List in Excel

Get FREE Advanced Excel Exercises with Solutions!

Today I am going to show you how you can create a dynamic dependent drop down list in Excel using both Formulas and Tables.

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

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

## 1. Applying 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: Creating 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.

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

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

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

### STEP 1: Making 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.

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: Generating 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.

Read More: Create Excel Drop Down List from Table

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF