Excel challenges

3 minute read

Excel Challenge 53: Dependent Dropdown Lists

Ilgar Zarbaliyev

Ilgar Zarbaliyev

Join the Excel conversation on Slack

Ask a question or join the conversation for all things Excel on our Slack channel.

Can your dropdown lists in Excel do magic? ✨ Dropdown lists that dynamically change values depending on what is selected in another cell are called dependent dropdown lists, and they are the focus of this Excel challenge.

Everything you need to participate is available on this page. To get started:

  1. Download the Excel file containing both the challenge and the dataset.
  2. Open the file and explore the Dataset sheet.
  3. Complete the tasks provided.

Ready to take the challenge?

Download the practice file to get started!

This challenge is designed to test your ability to work with Data Validation, Named Ranges, and key Excel functions.


Scenario

Anna has been asked to do the following Excel proficiency test as part of a job interview.

practice file screenshot

She needs to use the attached Excel file to:

  1. Create a dropdown list in cell L4 that allows selecting a value (Arizona, Texas, Colorado, Washington, Massachusetts, or Michigan) from the states in Row 3.
  2. In cell M4, offer only the cities that belong to the state selected in cell L4.
  3. Importantly, reset the city selection to blank when the state changes.

Great news: Anna completed the tasks and got the job! Can you figure out how she did it?


What you’ll gain from this challenge

By completing this hands-on task, you'll strengthen your ability to:

  • Implement data validation
  • Apply conditional formatting
  • Use and manage named ranges

Ready to take the challenge?

Download the practice file to get started!

Some clues

Need a quick refresher on dropdown lists? We've got you covered. Check out these handy resources to upgrade or sharpen your skills:


The solution💡

Ready to check out the GoSkills solution to this challenge?

Did you do it the same way, or would you like to share a different approach? Have your say in our Excel Slack community!

We hope you enjoyed taking part in this challenge. Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills. 

We recommend the crowd favorite Excel Basic and Advanced course. We have a 7-day free trial waiting for you! 🎁

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial

Join the Excel conversation on Slack

Ask a question or join the conversation for all things Excel on our Slack channel.

Ilgar Zarbaliyev

Ilgar Zarbaliyev

Ilgar creates content on data analysis, business intelligence, and innovative training methods. With over 20 years of experience, he is the Senior Manager at SumProduct Pty Ltd, a Microsoft MVP, and MCT Regional Lead for the CEE region. Currently pursuing a postgraduate degree in AI and ML from Texas McCombs School of Business, he also serves as a European Training Foundation Ambassador, promoting modern educational practices. In his free time, he enjoys watching films and football. Connect with him on LinkedIn here.