You have learned how to create a drop-down box in Excel in the data validation lesson. In this lesson, let’s understand how to construct two level cascading drop-down. Something like this:
In this lesson
- Demo of two level cascading drop-down
- Understanding the formula logic – INDEX array
- Creating necessary names
- Setting up data validation, extending it
- Closing remarks
Watch this lesson [15 mins]
Author: Chandoo
My name is Chandoo. I am going to make you awesome in Excel, VBA, Dashboards, Power BI & Power Pivot and answering your questions.
I live in Wellington, New Zealand. It is a beautiful, small city on the southern edge of the world. I run my own Excel Consulting & Training business from here.
I am married to my college sweet-heart, Jo. We have 2 kids – twins. They are 10 years old now. They are busy creating new worlds with lego bricks or playing or learning. We (Jo &I) are busy with cooking, eating, walking, talking and reading.
View all posts by Chandoo
Hi Chandoo,
So everytime I try to write an array using index function it shows me all the values, when I press enter.
for example, in the above video – after writing index formula for the cities it shows all four cities name. And when i define a name for the same, it doesn’t work. in your videos it shows something as casacading in the value of defined name range.
I am confused what I am doing wrong please help.
Thanks
V
Hi Vaishnavi… Since recording this video in 2019, Microsoft has released new functionality in Excel 365 that makes most array formulas spill. Please have a read here to understand the dynamic array functionality – https://chandoo.org/wp/dynamic-array-functions/
Than you Chandoo,
It makes sense now, the spill behaviour.