How to create dependent drop down list in Excel with multiple selections

How to make a dynamic dependent dropdown list in Excel an easy way

by | updated on January 28, 2022

The tutorial shows how to create an Excel drop down list depending on another cell by using new dynamic array functions.

Creating a simple drop down list in Excel is easy. Making a multi-level cascading drop-down has always been a challenge. The above linked tutorial describes four different approaches, each including a crazy number of steps, a bunch of different formulas, and a handful of limitations relating to multi-word entries, blank cells, etc.

That was the bad news. The good news is that those methods were designed for pre-dynamic versions of Excel. The introduction of dynamic arrays in Excel 365 has changed everything! With new dynamic array functions, creating a multiple dependent drop-down list is a matter of minutes, if not seconds. No tricks, no caveats, no nonsense. Only fast, straightforward and easy-to-follow solutions.

• Make a dynamic drop down list in Excel
• Create a multiple dependent drop down list
• Create an expandable dropdown excluding blank cells
• Sort drop down list alphabetically

Notes:

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

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

How to Create a Dependent Drop Down List in Excel

• -- By Sumit Bansal

FREE EXCEL TIPS EBOOK - Click here to get your copy

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 Excel, you may create a dependent drop down list quickly and easily, but, have you ever tried to create multi-level dependent drop down list as following screenshot shown? This article, I will talk about how to create a multi-level dependent drop down list in Excel.

Create multi-level dependent drop down list in Excel

Create multi-level dependent drop down list in Excel with an amazing feature

Create multi-level dependent drop down list in Excel

To create a multi-level dependent drop down list, please do with the following steps:

Firstly, create the data for the multi-level dependent drop down list.

1. First, create the first, second and third drop down list data as below screenshot shown:

Secondly, create range names for each drop down list values.

2. Then, select the first drop down list values (excluding the header cell), and then give a range name for them in the Name box which besides the formula bar, see screenshot:

3. And then, select the second drop down list data and then click Formulas > Create from Selection, see screenshot:

4. In the popped out Create Names from Selection dialog box, check only Top row option, see screenshot:

5. Click OK, And the range names have been created for each second drop down data at once, then, you should create range names for the third drop down list values, go on clicking Formulas > Create from Selection, in the Create Names from Selection dialog box, check only Top row option, see screenshot:

6. Then, click OK button, the third level drop down list values have been defined range names.

Thirdly, create Data Validation drop down list.

7. And then, click a cell where you want to put the first dependent drop down list, for example, I will select cell I2, then, click Data > Data Validation > Data Validation, see screenshot:

8. In the Data Validation dialog box, under the Settings tab, choose List from the Allow drop down list, and then enter this formula: =Continents into the Source text box, see screenshot:

Note: In this formula, Continents is the range name of the first drop down values you created in step 2, please change it to your need.

9. Then, click OK button, the first drop down list has been created as below screenshot shown:

10. And then, you should create the second dependent drop down, please select a cell where you want to put the second drop down list, here, I will click J2, and then go on clicking Data > Data Validation > Data Validation, in the Data Validation dialog box, do the following operations:

• (1.) Choose List from the Allow drop down list;
• (2.) Then enter this formula: =INDIRECT(SUBSTITUTE(I2," ","_")) into the Source text box.

Note: In the above formula, I2 is the cell which contains the first drop down list value, please change it to your own.

11. Click OK, and the second dependent drop down list has been created at once, see screenshot:

12. This step, you should create the third dependent drop down list, click a cell to output the third drop down list value, here, I will select cell K2, and then click Data > Data Validation > Data Validation, in the Data Validation dialog box, do the following operations:

• (1.) Choose List from the Allow drop down list;
• (2.) Then enter this formula: =INDIRECT(SUBSTITUTE(J2," ","_")) into the Source text box.

Note: In the above formula, J2 is the cell which contains the second drop down list value, please change it to your own.

13. Then, click OK, and the three dependent drop down list has been created successfully, see the below demo:

Create multi-level dependent drop down list in Excel with an amazing feature

Maybe the above method is troublesome for most of the users, here, I will introduce an easy feature-Dynamic Drop-down List of Kutools for Excel, with this utility, you can quickly create 2-5 levels dependent drop down list with only several clicks. Click to download Kutools for Excel!

After installing Kutools for Excel, please do as this:

1. First, you should create the data format as below screenshot shown:

2. Then, click Kutools > Drop-down List > Dynamic Drop-down List, see screenshot:

3. In the Dependent Drop-down List dialog box, please do the following operations:

• Check the 3-5 Levels dependent Drop-down list option in the Type section;
• Specify data range and output range as you need.

4. Then, click Ok button, now, the 3-level drop down list has been created as the following demo shown:

Click to Download Kutools for Excel and free trial Now!

More relative drop down list articles: