How to create cascading drop downs in Excel – video

Posted on August 31st, 2015 in Excel Howtos , Learn Excel - 8 comments

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.

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.

Written by Chandoo
Tags: , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

8 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

Leave a Reply