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.

=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

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

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)

Related Posts:


Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!


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.

RECOMMENDED READING

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.

JOIN FREE EMAIL NEWSLETTER

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