Excel Tutorial: How to create Cascading Dropdowns

Last updated on July 28, 2018 By

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

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.


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

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.


Dropdown boxes are good to go!

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

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.

Victor Chan (Launch Excel)

Related Posts:

Author: Victor Chan

Victor expertly teaches Microsoft Excel to people all over the world. He has millions of views of his popular Excel explainer videos on YouTube. These show time-saving shortcuts and real-world applications explained with easy-to-follow visuals.

Victor has over 20 years of experience using Excel as a professional for Big 4 Audit Firm Deloitte and two global tech companies. He knows firsthand that being more productive with Excel can lead to greater job satisfaction and career growth.

Connect on YouTube, LinkedIn, Twitter.

Recommended Posts

Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.


Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.


Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.