How to Create Dynamic Dependent Drop Down List in Excel

Dynamic Dependent Drop Down List Quickview

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?

Dynamic Dependent Drop Down List Quickview

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

Dynamic Dependent Drop Down List Quickview

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.

Dynamic Dependent Drop Down List Quickview

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.

Dynamic Dependent Drop Down List Quickview


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.

Data Set to Create Dynamic Dependent Drop Down List

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.

=UNIQUE(OFFSET(B4,0,0,COUNTA(B:B)-1,1))

[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

=UNIQUE(FILTER(B:B,((B:B<>"")*(B:B<>B3))))

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

OR

=UNIQUE(B4:B25) (Use this formula one if you don’t want to make the list dynamic)
[Here B4:B25 is the range of my first column. You use your one.]

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.

=UNIQUE(OFFSET(B4,0,0,COUNTA(B:B)-1,1))

Formula for Primary Drop Down List

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

=UNIQUE(OFFSET(Sheet1!$B$4,0,0,COUNTA(Sheet1!$B:$B)-1,1))
[Put the worksheet name reference (Sheet1! in my case) before the cell references].

New Name Box in Excel

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

Data Validation Tool in Excel

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

Data Validation Dialogue Box

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

Data Validation Dialogue Box

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

Data Validation Dialogue Box

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

Primary Dynamic Drop Down List

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

Primary Dynamic Drop Down List


Step 2: Creating the Second Drop Down List

The formula for the second drop-down list is:

=FILTER(C:C,B:B=E4)

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

Formula for Second Drop Down List

OR

New Name Dialogue Box in Excel

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.

Dynamic Second Drop Down List


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.

Data Set for Dynamic Dependent Drop Down List with Tables

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

Create Table Dialogue Box in Excel

Click OK.

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

Data Set Converted to Table in Excel


Step 1: Creating the Primary Drop Down List

The formula for the primary drop-down list is:

=TRANSPOSE(Table1[#Headers])
[Here Table1 is the name of my table. You use your one.]

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.

Formula for Dynamic Drop Down List with Table

OR

New Name Dialogue Box in Excel

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.

Dynamic Primary Drop Down List Using Table


Step 2: Creating the Second Drop Down List

The formula for the second drop-down list is:

=INDIRECT("Table1["&G4&"]")
[Here Table1 is the name of my table. G4 is the cell reference of my primary drop-down list.]

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.

Formula for Creating the Secondary Drop Down List

Or

New Name Box

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.

Dynamic Second Drop Down List Using Table

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.

Dynamic Second Drop Down List Using Table


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.

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

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo