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#

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

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)

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.

Excel Dependent Drop Down List: Knowledge Hub

<< Go Back to Excel Drop-Down List | Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF