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.

What is a 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, the cell E4 can select a value from a list of all the book type names available

This is called a drop-down list.

Now, what is a dependent drop-down list?

A dependent drop-down list is a drop-down list that depends on the output of another drop-down list.

In the image below, cell F4 contains a list of all the books of the type selected in E4.

If you select Novel in E4, F4 will show you the names of all the novels available.

Same for poetry and other book types.

This is called the dependent drop-down list.

Finally, what is a dynamic dependent drop-down list?

In a dynamic dependent drop-down list, if you add any value in the main column, the drop-down list will update automatically.

See in the image below, I have added a new book type called Non-Fiction. And it has also been added automatically in the drop-down list of cell E4.

Download Practice Workbook

How to Create Dynamic Dependent Drop Down List in Excel

Here we have a data set with the Book Types and Book Names of some books in a bookshop called Martin Bookstore.

Our objective today is to create a dynamic dependent drop-down list in Excel from this data set.

Case 1: Creating Dynamic Dependent Drop Down List with Formulas

Step 1: Creating the Primary Drop Down List

There are three formulas available for creating the primary drop-down list.

[Here B4 is my first cell of the first column (Avoiding the Column Header), and B:B is my first column. You use your one.]

OR

[Here B:B is my first column. B3 is the Column Header of my first column. You use your one.]

OR

There are two steps to create the drop-down list.

i. Getting the List Items Using the Formula

You can again accomplish it in two ways.

a) By Entering the Formula Directly in a Cell

Select any cell in your worksheet and enter any of the three formulas directly in the cell:

I entered the 1st formula, the OFFSET one.

Or you can use a Named Range to do so.

b) By Using a Named Range

• Go to Formula>Define Name>Define Name in your excel toolbar.

Image 5.

• Click on Define Name. You will get the New Name dialogue box.

In the Name option, put any one-word name (I put BookType).

Then in the Scope option, select Workbook.

And in the Refers to option, enter any of the above three formulas. I entered the first one (the OFFSET one).

• Then click OK. The formula will be saved as the name BookType (Or the one you used).

ii. Creating the Drop Down List Using the List

If you have followed any of the two methods, you are ready to create the primary drop-down list.

• Select the cell where you want to enter the drop-down list. Then go to Data>Data Tools>Data Validation in your Excel Toolbar.

• Click on Data Validation. From the drop-down options, again choose Data validation. You will get the Data validation dialogue box.

Under the Allow option, choose List.

• Then under the Source option, put the reference of the cell where you inserted the formula with a Hashtag(#) sign in the end (=$H$4# in this example).

• Or if you used the Named Range option, just put that name under the source option (BookType in this example).

• Then click OK. You will find a primary dynamic drop-down list has been created in your selected cell (E4 in this example).

It is a dynamic drop-down list. Add any new book type in the Book Type column. You will find it added automatically to the drop-down list.

Here I have added “Non-Fiction”.

Step 2: Creating the Second Drop Down List

The formula for the second drop-down list is:

[Here C:C is my second column, B:B is my first column, and E4 is the cell reference of the primary drop-down list. You use your one].

You can use any of the two methods, By Entering the Formula Directly in a Cell or By Using a Named Range described in the previous section Getting the List Items Using the Formula to get the list items.

OR

Then follow the steps described in the section Creating the Drop Down List Using the List. You will find a dynamic second drop-down list created in your selected cell.

Case 2: Creating Dynamic Dependent Drop Down List with Tables

If you have the data set in an arranged format like this (Different types of things in different columns), then you can follow this procedure.

First, select the whole data set and press Ctrl + L on your keyboard. You will get a Create Table dialogue box.

Click OK.

You will find your data set has been converted to a table.

Step 1: Creating the Primary Drop Down List

The formula for the primary drop-down list is:

Then use any of the two methods, By Entering the Formula Directly in a Cell or By Using a Named Range described in the section Getting the List Items Using the Formula of the previous procedure.

OR

Then follow the steps described in the section Creating the Drop Down List Using the List. You will find a dynamic primary drop-down list created in your selected cell.

Step 2: Creating the Second Drop Down List

The formula for the second drop-down list is:

Then use any of the two methods, By Entering the Formula Directly in a Cell or By Using a Named Range described in the section Getting the List Items Using the Formula of the previous procedure.

Or

Then follow the steps described in the section Creating the Drop Down List Using the List. You will find a dynamic second drop-down list created in your selected cell.

This is a dynamic drop-down list. If we add any new book of any type, it will be added automatically to the list.

Here I have added a new book called “Crime and Punishment” in the column Novel.

Conclusion

Using these methods, you can create a dynamic drop-down list of your own with both formulas and tables. Do you have any questions? Feel free to ask us.