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.

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:

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

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

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:

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

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

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

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

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

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

Further Readings

• How to Create Drop Down List in Excel with Multiple Selections
• Make a Drop-Down List Based on Formula in Excel (4 Ways)
• Conditional Drop Down List in Excel (Create, Sort and Use)