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.
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)
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 instead:
=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)
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)
- 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:
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))
- 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:
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.