Cascading drop downs enhance usability of your dashboards & interactive workbooks. A cascading drop-down is a 2 or more level selection mechanism. When you have 100s of selection choices, instead of creating one massive drop down or combo-box, you can set up multiple levels of drop downs, so that users can narrow down their selection. For example, users can select Country, State and then City using cascading drop downs.
There are many ways to setup cascading drop downs. You can use formulas coupled with either data validation or form controls. You can also use Slicers. In this video we will review these techniques.
If you use Excel 365, you can also use XLOOKUP to set up cascading drop down list in Excel. Check out this article for that technique.
Cascading drop downs in Excel – Video Tutorial:
You can also watch this video on our YouTube channel.
Download Cascading Drop Downs Example Workbook
Please click here to download the cascading drop downs workbook. Examine the named formulas & slicer settings to learn more.
Companion material to learn more
Please go thru below links to learn more about cascading drop downs in Excel.
9 Responses to “How to create cascading drop downs in Excel – video”
Hi Chandoo. I've come up with a brand new approach since I wrote those two guest posts of mine you reference above ( i.e. the Cascading Drop Downs With VBA and Cascading Dropdowns that reset without VBA links).
My new approach lets you instantly create cascading dropdowns based on a hierarchy stored in a hidden PivotTable (or in a data source that you could make a PivotTable out of). The beauty of this approach is twofold:
Stay tuned...I should have a guest post up and running in a week or four.
Hi Jeff Weir,
First of all, many thanks to Chandoo, you, and all other Excel power-users / experts, helping us in our day-to-day activities.
I'm looking forward to have a glance at your new approach: as a matter of fact, i've been trying to implement long-ass conditional drop-down lists for our internal job mapping, and I have many many non-unique items (e.g. Directors in each specific Job Family, the mapping being a 3-levels one).
Where will it be available ?
Many thanks once again, and keep up the amazing work 🙂
Kind Regards,
Safir Zabari.
I should have something available in the next few weeks, Safir. I have a few other things I'm working on that I need to finish first. I'll drop you a line when I've finished the beta version, and you can test it for me if you like.
Hi Chandoo.
Nice post again. You could also add a worksheet change event for the target of select state. Once select a state is changed it would automatically clear the select a city rather than showing conditional formatting. I have done this on worksheet level as well as on userform level. Thanks a lot for such an awesome post.
Dear Chandoo Group,
it is awesome, it has really improved my knowledge
Hello Chandoo Group,
this was a nice post , and i have a shortcut and very easy trick for making dropdown of state nd city
thanks
This is the first time I'm coming across your site. And I have been browsing it for the past 20 mins. So many wonderful articles. Hopefully I might become an expert one day by following your site.
Dear Chandoo
I'm so appreciative of your website for much des cover I have made since then. I had problems with drop down list and vlookup that are sorted out.
Congo DRC
Hi,
does the tutorial allow also to have this for multiple rows into 1 excel. At the moment, this is fixed to the cell C3 and C4. Could you have this for e.g. 5 rows going from C3-D4 to G3-G4? Each click in C3 will allow for a new selection from the root of the hierarchy.
BTW: using the process as described als allows for multiple levels in the hierarchy. I have this currently working for 4 levels, but still limited to 1 starting cell.
Regards,
Plooi