How do I create a multi level dependent drop-down list in Excel?

• This new dynamic array way of making dropdown lists only works in Excel 365 and Excel 2021. In pre-dynamic Excel, you will have to do it the long old-fashioned way as described in Creating a dependent drop down in Excel 2019, 2016 and earlier.
• This solution is for a single row. If you want to copy your picklists down multiple rows, then follow the instructions in Dependent drop-down list for multiple rows.

Last week we started to explore the capabilities of Excel Data Validation and learned how to create a simple drop-down list in Excel based on a comma-separated list, range of cells or a named range.

Today, we are going to investigate this feature in-depth and learn how to create cascading drop down lists that display choices depending on the value selected in first dropdown. To put it differently, we will make an Excel data validation list based on the value of another list.

• Creating a multiple dependent dropdown list
• Cascading drop-down lists with multi-word entries
• Block changes in the primary drop-down list
• Make a dynamic depending drop-down list

Example for Creating Multiple Dependent Drop-Down Lists

Let's take a look at the below data for which you intend to create a dependent drop-down list using the offset formula.

Here you can see three different leagues, each with its list of teams. In order to simplify the concept, each league can only have a small number of teams instead of featuring a full list.

On the left-hand side, you have two choices to select a league and its corresponding team. Your team selection will depend on what league you choose, as choice two is dependent on the first choice.

Our goal is to create a simple drop-down menu for league names and a dependent drop-down menu for each league's list.

Creating Simple Drop-Down Menu for Football Leagues

1. Go to the Data tab and click on Data Validation.

2. Select the List in Allow option in validation criteria.

3. Select cells E4 to G4 as the source.

4. Click OK to apply the changes.

In three easy steps, you can create a simple drop-down list. Then copy and paste the formula to the rest of the cells down the row.

Related: How to Create a Dropdown List in Microsoft Excel

Create Dependent Drop Down Lists

Easy steps for Excel dependent drop down list. Conditional data validation based on other cell, like Region and City lists. Videos, written steps, get free Excel file.

How to Create a Dependent Drop Down List in Excel

• -- By Sumit Bansal

Watch Video – Creating a Dependent Drop Down List in Excel

An Excel drop down list is a useful feature when you’re creating data entry forms or Excel Dashboards.

It shows a list of items as a drop down in a cell, and the user can make a selectionfrom the drop down. This could be useful when you have a list of names, products, or regions that you often need to enter in a set of cells.

Below is an example of an Excel drop down list:

In the above example, I have used the items in A2:A6 to create a drop-down in C3.

Sometimes, however, you may want to use more than one drop-down list in Excel such that the items available in a second drop-down list are dependent on the selection made in the first drop-down list.

These are called dependent drop-down lists in Excel.

Below is an example of what I mean by a dependent drop-down list in Excel:

You can see that the options in Drop Down 2 depend on the selection made in Drop Down 1. If I select ‘Fruits’ in Drop Down 1, I am shown the fruit names, but if I select Vegetables in Drop Down 1, then I am shown the vegetable names in Drop Down 2.

This is called a conditional or dependent drop down list in Excel.

Naming the Ranges

The first step in creating the multiple drop down lists is to name each range of cells containing the entries for each list.

Each range that contains the entries for a list will need to be named. This is vital as it will establish the relationship between the lists.

For this to work, the name given to a range will need to match the wording of the item from the previous list.

The image below shows the items being used forsome of the lists. Each range will be named the same as the header for each list. For example, range C2:C5 is named Films, which precisely matches the wording of Films in the Category list.

To create a named range;

1. Select the range of cells you want to name.
2. Click in the Name Box and enter the name you wish to use.