Excel Dependent Drop Down List (Create with Easy Steps)

Up till now, we have learned to create drop-down lists in Excel. Today I will be showing how you to create a dependent drop down list in Excel.

Excel Dependent Drop Down List: Create with Easy Steps

Here we’ve got a data set with the Names, Authors, and Book Types of some books of a bookshop called Martin Bookstore from which we want to make a drop down list.

Our objective today is to create a dependent drop-down list.

First, we will create a drop-down list of the Book Types (First Drop Down List)
Then we will create a drop-down list of the Authors based on the Book Types (Middle Drop Down List)
Finally, we will create a drop-down list of the Book Names based on the Authors (Last Drop Down List)

Step 1: Create the First Drop Down List Using UNIQUE Function

To create the first drop-down list, select any cell in your workbook and apply the UNIQUE function:

=UNIQUE(D5:D30,FALSE,FALSE)

Here, D5:D30 is the range of my array (Book Type). You use your one.

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

Click on Data Validation. You will get the Data Validation dialogue box.
From the Allow option, select List.
Then in the Source box, enter the cell reference of the formula where you entered the previous formula, along with a HashTag (#) at the end.

In this example, it is $J$5#

Click on OK. You will get a drop-down list generated handsomely with the Book Types in your selected cell.

Note:

To generate a dynamic drop-down list, use this formula combining UNIQUE, OFFSET and COUNTA functions:

=UNIQUE(OFFSET(E5,0,0,COUNTA(E:E)-1,1),FALSE,FALSE)

Don’t worry. Just replace the column array D5:D30 with the OFFSET function of this form:

=OFFSET(start_cell,0,0,COUNTA(column)-1,1)

Step 2: Create the Middle Drop Down List (First Dependent List) with UNIQUE-FILTER Combination

Now we will create a drop-down list of the Authors based on the Book Type selected from the first list.

Select any cell in your worksheet and enter this formula combining UNIQUE and FILTER functions.

=UNIQUE(FILTER(C5:C30,E5:E30=G5),FALSE,FALSE)

Here C5:C30 is the range of this drop-down list (Authors), and D5:D30 is the range of the previous drop-down list (Book Types). You use your one.
And F5 is the cell reference of the first drop-down list. You use your one.

Then select the cell where you want to insert the drop-down list and go to Data > Data Validation > Data Validation in the Excel toolbar.

Click on Data Validation. You will get the Data Validation dialogue box.
From the Allow option, select List.
In the Source box, enter the reference of the cell where you inserted the previous formula, with a HashTag (#) at the end.

In this example, it is $L$5#.

Click on OK. You will find your drop-down list generated in your selected cell.

Now if you have more drop-down lists to be generated before going to the last drop-down list, follow the same procedure.
For example, let’s have one more column Country after the column Author, containing the countries of the authors, like this.

First, we will generate the first drop-down list (Book Type) in the mentioned way.
Then we will generate the second drop-down list (Author) based on the first drop-down list using this formula:

=UNIQUE(FILTER(C5:C30,(D5:D30=I5)*(E5:E30=G5)))

Then we will generate the third drop-down list (Country) based on the first and second drop-down lists using this formula:

=UNIQUE(FILTER(D5:D30,E5:E30=G5))

If you have one more drop-down list to create in the middle, create it based on the first, second, and third drop-down lists. And so on.

Note:

To generate a dynamic drop-down list, use this formula instead.

=UNIQUE(FILTER(OFFSET(C5,0,0,COUNTA(C:C)-1,1),OFFSET(D5,0,0,COUNTA(D:D)-1,1)=F5),FALSE,FALSE)

Replaced all the column arrays, C5:C30 and D5:D30 with an OFFSET function of this form:

=OFFSET(start_cell,0,0,COUNTA(column)-1,1)

Step 3: Create Another Dependent Drop Down List

To create the last drop-down list, you have to use all the previous lists. But you need not use the UNIQUE function this time.

In our original data set, the last drop-down list will be of the Names of the books. It will depend on the first and second drop-down lists.

Select any cell in your worksheet and enter this formula:

=FILTER(B5:B30,(C5:C30=G5)*(D5:D30=F5))

Here B5:B30 (Book Name) is the range of my last drop-down list, C5:C30 (Author) is the range of my second drop-down list.
And D5:D30 (Book Type) is the range of my first drop-down list. You use your one.
G5 is the cell reference of the second drop-down list, and F5 is the cell reference of the first drop-down list. You use your one.

Then select the cell where you want to enter the drop-down list and go to Data>Data Validation>Data Validation in the Excel Toolbar.

Click on Data Validation. You will get the Data Validation dialogue box.
From the Allow option, select List.
In the Source box, enter the cell reference of the previous formula along with a HashTag (#) at the end.

In this example, it is $N$5#