Summary: In this article we’ll learn how to create Cascading dropdowns in Excel. Excel dropdown lists are very useful especially when we want to create data entry forms.

Excel functions used in this article: OFFSET, COUNTA, CHOOSE, MATCH. Make sure you are familiar with these functions to better understand this article.

Difficulty: Advanced

To follow along with article you can download the Sample Workbook “How to create Cascading dropdowns in Excel.xlsx” (11Kb) by clicking here

Cascading Dropdowns

Consider the data given below (UK Premier League teams and football players):

We have the list of teams listed in column A, and the list of players for each team in columns B, C, D and E.  We need to create a dynamic cascading dropdown which allows us to select a player based on the team selected in a previous dropdown.

#1 – Create Named Ranges – one for Team names and one for Player names

» Back to contents

The first thing that needs to be done before we create dropdowns is to create named ranges for Team names and Player names.

Step 1: Click on ‘Define Name’ in the Formulas ribbon.

Step 2: In the ‘New Name’ dialog box, fill the appropriate details as shown below.

Similarly create new named ranges for the list of players in each team.

Note: Keep in mind to create dynamic ranges for player names by using the below formula.

=OFFSET(Sheet3!$B$2,0,0,COUNTA(Sheet3!$B:$B)-1)

After creating all the named ranges, you can check them by clicking ‘Name Manager’ in the Formulas ribbon.

Now that we have created all the named ranges, the next thing that we need to do is to create dropdowns.

#2 – Create Dropdowns – using Data validation lists – one for Team and one for Players

» Back to contents

For this example, we need to create two data validation lists. One for the team name and the other for the list of players

Step 1: Select the cell for the team name dropdown box. (I2 in this example)

Step 2: Click on Data Validation from the Data ribbon.

Step 3: In the Data Validation dialog box, within the settings field, choose ‘list’. And for the source, choose the ‘Team’ name range as shown below.

Step 4: Select the cell for the player name dropdown box (J2 in this example). And click on Data Validation from Data ribbon.

Step 5: In the Data Validation dialog box, within the settings field, choose ‘List’. And for the source, write the formula as described below.

=CHOOSE(MATCH($I$2,Team,0),Chelsea,Arsenal,Tottenham,Liverpool)

Dropdown boxes are good to go!

#3 – About the authors – Excel experts on data analytics, BI and reporting services

» Back to contents

This article was contributed by Perceptive Analytics. Nagendra Kumar, Ritwick P Rao, Jyothirmayee Thondamallu and Chaitanya Sagar contributed to this article.

Perceptive Analytics provides Excel Expert, data analytics, business intelligence and reporting services to e-commerce, retail, healthcare and pharmaceutical industries. Its client roster includes Fortune 500 and NYSE listed companies in the USA and India.

Note from Victor

I’d like to thank Perceptive for writing this tutorial… and if this article got you excited and you want to learn more Excel topics just post your requests below in the comments.

Cheers,
Victor Chan (Launch Excel)