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.
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.
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.
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!
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.
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)
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.
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.
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.