fbpx
Search
Close this search box.

How to create cascading drop downs in Excel – video

Share

Facebook
Twitter
LinkedIn

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.

cascading-drop-downs-demo

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

9 Responses to “How to create cascading drop downs in Excel – video”

  1. Jeff Weir says:

    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:

    1. Your sub-categories can have non-unique items. So my new approach will let you use the master categories of "Wine" and "Grapes" as well as letting you use the non-unique sub categories of "Red" and "White". (In the approaches outlined above, you would need to give those subcategories unique names of "Red Wine", "Red Grapes", "White Wine", "White Grapes" respectively)
    2. I've turned the whole thing into a one-touch macro. So no more mucking about with those confusing relative references in the Name Manager

    Stay tuned...I should have a guest post up and running in a week or four.

    • Safir Zed. says:

      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.

      • Jeff Weir says:

        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.

  2. Amey says:

    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.

  3. Amit says:

    Dear Chandoo Group,

    it is awesome, it has really improved my knowledge

  4. Amit says:

    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

  5. Vamsi says:

    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.

  6. Emmanuel Tshifunga says:

    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

  7. Plooi Fiets says:

    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

Leave a Reply