Excel Dependent Drop Down List (Create with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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


Steps to Create a 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.

a data set with the Names, Authors, and Book Types

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 enter this formula:
=UNIQUE(D5:D30,FALSE,FALSE)

select any cell in your workbook and enter this formula

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.

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$5#

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


Note:

=UNIQUE(OFFSET(E5,0,0,COUNTA(E:E)-1,1),FALSE,FALSE)
  • Don’t worry. Just replace the column array D5:D30 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

Read more: How to Create Drop Down List in Multiple Columns in Excel


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:
=UNIQUE(FILTER(C5:C30,E5:E30=G5),FALSE,FALSE)

Formula for the Middle Drop Down List

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

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$5#.

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 (Author) based on the first drop-down list using this formula:
=UNIQUE(FILTER(C5:C30,(D5:D30=I5)*(E5:E30=G5)))

Formula for Second Dependent Drop Down List

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

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

Formula for Dynamic Second Drop Down List

Read more: How to Make a Drop Down List in Excel


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

Formula for the Last Drop Down List

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

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$5#

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


Note:

  • To generate a dynamic drop-down list, use this formula instead:
=FILTER(OFFSET(B5,0,0,COUNTA(B:B)-2,1),(OFFSET(C5,0,0,COUNTA(C:C)-1,1)=G5)*(OFFSET(D5,0,0,COUNTA(D:D)-1,1)=F5))
  • Replaced all the column arrays, C5:C30, D5:D30, and E5:E30 with an OFFSET function of this form:
=OFFSET(start_cell,0,0,COUNTA(column)-1,1)

Formula for Dynamic Last Drop Down List


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.


Further Readings

Rifat Hassan
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 the 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo