How to Create Multi Level Dependent Dropdown List in Excel

You can organise and speed up data entry in your spreadsheets by using drop-down lists. You can enhance this capability by building a hierarchy of choices that dynamically updates based on the decisions made in other cells using a multi-level dependent drop-down list. In this lesson, we'll look at detailed steps for setting up and customising an Excel drop-down list with multiple levels of dependency. This function will come in handy whether you're managing data, making forms, or just trying to increase the speed at which you enter data.

You will be able to construct a drop-down list with multiple levels of dependency at the end of this tutorial, with the options in each level changing dynamically based on the choices made in the levels before it. You'll be able to do this to develop data entry forms that are structured and simple to use, which will make it simpler to enter and analyse information into your Excel spreadsheets. So, let's get started and discover how to make an Excel multi-level dependant drop-down list!

Creating Multi-Level Dependent Dropdown List in Excel

Here we will first name the ranges, then create the data validation list to complete the task. So let us see a simple process to know how you can create a multi-level dependent drop-down list in Excel.

Step 1

Consider an Excel sheet where the data in the sheet is similar to the below image.

First select the range of cells, then name them as continents.

Select Cells > Name Box > Continents > Enter.

Step 2

Then select the second range of cells, click on formulas, and click on Create from Selection.

Select cell > Formulas > Create From Selection.

Step 3

Then check the box named Top Row and click OK.

Step 4

Then repeat steps 2 and 3 for the third range.

Step 5

Now click on the empty cell and click on Data Validation under Data." Then set Allow to List, enter source as =Continents, and click Ok.

Empty Cell > Data > Data Validation > Allow > Source > Ok.

Step 6

Again, click on data and select data validation. Then set Allow to List and enter the source as =INDIRECT(SUBSTITUTE(m2," ","_")) and click Ok.

Empty Cell > Data > Data Validation > Allow > Source > Ok.

Step 7

Finally, click on data and select data validation. Then set Allow to List and enter the source as =INDIRECT(SUBSTITUTE(J2," ","_")) and click Ok

Empty Cell > Data > Data Validation > Allow > Source > Ok.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can create a multi-level dependent drop-down list in Excel to highlight a particular set of data.