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.
Read More: How to Create Dependent Drop Down List with Multiple Words in Excel
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.
- 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
Download Practice Workbook
You can download the practice workbook from here.
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.
Related Articles
- Conditional Drop Down List in Excel
- How to Use IF Statement to Create Drop-Down List in Excel
- Excel Dependent Drop Down List
- How to Make Dependent Drop Down List with Spaces in Excel
- Create Excel Filter Using Drop-Down List Based on Cell Value
- Excel Formula Based on Drop-Down List
- How to Extract Data Based on a Drop Down List Selection in Excel
- How to Populate List Based on Cell Value in Excel
- How to Change Drop Down List Based on Cell Value in Excel
<< Go Back to Excel Dependent Drop Down List |Â Excel Drop-Down List | Data Validation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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?
Hi, you can learn about how to hide source data from this article.
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.
Hi
how do i add a sample workbook for you to see?
Thanks
Hello, Missy! Please send us your problems here: [email protected]