How to Create Dependent Drop Down List in Excel

Dependent Drop Down List in Excel

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.


Download Practice Workbook


How to Create Dependent Drop Down List in Excel

Here we’ve got a data set with the Names, Authors, and Book Types of some books of a bookshop called Martin Bookstore.

Data Set to Create Dependent Drop-Down List in Excel

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: Creating the First Drop Down List

  • To create the first drop-down list, select any cell in your workbook and enter this formula:
=UNIQUE(D4:D29,FALSE,FALSE)

Formula for the First Drop Down List

  • Here, D4:D29 is the range of my array (Book Type). You use your one.

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

=UNIQUE(OFFSET(D4,0,0,COUNTA(D:D)-1,1),FALSE,FALSE)

Don’t worry. Just replaced the column array D4:D29 with an OFFSET function of this form:

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

]

Formula for Dynamic First Drop Down List in Excel

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

Data Validation Tool in 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$4#

Data Validation Box in Excel

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

First Drop Down List Created in Excel


Step 2: Creating the Middle Drop Down List (s)

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:
=UNIQUE(FILTER(C4:C29,D4:D29=F4),FALSE,FALSE)

Formula for the Middle Drop Down List

  • Here C4:C29 is the range of this drop-down list (Authors), D4:D29 is the range of the previous drop-down list (Book Types). You use your one.

And F4 is the cell reference of the first drop-down list. You use your one.

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

=UNIQUE(FILTER(OFFSET(C4,0,0,COUNTA(C:C)-1,1),OFFSET(D4,0,0,COUNTA(D:D)-1,1)=F4),FALSE,FALSE)

Replaced all the column arrays, C4:C29 and D4:D29 with an OFFSET function of this form:

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

]

Formula for Dynamic Second Drop Down List

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

Data Validation Tool in Excel

  • 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$4#.

Data Validation Dialogue Box in Excel

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

Second Dependent Drop Down List Created in Excel

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.

Data Set to Create Drop Down List in Excel

  • First, we will generate the first drop-down list (Book Type) in the mentioned way.
  • Then we will generate the second drop-down list (Country)  based on the first drop-down list using this formula:
=UNIQUE(FILTER(D4:D29,E4:E29=G4))

Formula for Second Dependent Drop Down List

  • Then we will generate the third drop-down list (Authors) based on the first and second drop-down list using this formula:
=UNIQUE(FILTER(C4:C29,(D4:D29=H4)*(E4:E29=G4)))

Formula for Second Drop Down List

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

Step 3: Creating the Last 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(B4:B29,(C4:C29=G4)*(D4:D29=F4))

Formula for the Last Drop Down List

  • Here B4:B29 (Book Name) is the range of my last drop-down list, C4:C29 (Author) is the range of my second drop-down list.
  • And D4:D29 (Book Type) is the range of my first drop-down list. You use your one.
  • G4 is the cell reference of the second drop-down list, and F4 is the cell reference of the first drop-down list. You use your one.

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

=FILTER(OFFSET(B4,0,0,COUNTA(B:B)-1,1),(OFFSET(C4,0,0,COUNTA(C:C)-1,1)=G4)*(OFFSET(D4,0,0,COUNTA(D:D)-1,1)=F4))

Replaced all the column arrays, C4:C29, D4:D29, and E4:E29 with an OFFSET function of this form:

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

]

Formula for Dynamic Last Drop Down List

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

Data Validation Tool in Excel

  • 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$4#

Data Validation Box in Excel

  • Then click OK. You will get the drop-down list generated in your selected cell.

Dependent Drop Down List in Excel


Conclusion

Using these methods, you can generate any drop-down list dependent on another list in Excel. 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