Excel Tutorial: How to create Cascading Dropdowns

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


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

[IMAGE] Cascading dropdowns in Excel

Cascading Dropdowns

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

[IMAGE] 1-table

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.

[GIF] Cascading dropdown animation

Contents

Click on the links below to learn more…

#1 – Create Named Ranges – one for Team names and one for Player names
#2 – Create Dropdowns – using Data validation lists – one for Team and one for Players
#3 – About the authors – Excel experts for data analytics, BI and reporting

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

[IMAGE] 2-define-name

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

[IMAGE] 3-formula-manager

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)

[IMAGE] 4-offset-formula-explained

{IMAGE] 5-formula-name-box

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

[IMAGE] 6-name-manager

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.

[IMAGE] 7-data-validation

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.

[IMAGE] 8-data-validation-dialog

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)

[IMAGE] 9-choose-formula

[IMAGE] 10-data-validation-dialog

Dropdown boxes are good to go!

[GIF] Cascading dropdown animation

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

[IMAGE] Victor pointing left

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)

Victor Chan
Victor has used Excel a lot since 2002. He's a Chartered Accountant (Fellow of the ICAEW) with MEng in Manufacturing Engineering from the University of Cambridge. He's on a mission to help you master Excel and VBA!
Join over 9,000 subscribers

GET BETTER AT EXCEL

Recent Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Contact Us

Please send us an email and we'll get back to you, asap.

Not readable? Change text.