Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Dynamically Grouping Related Events [Excel Combo Charts with Pizzazz]

Posted on August 27th, 2009 in Charts and Graphs - 24 comments

Yesterday we have posted how to use excel combo charts to group related time events. In the comments, Art Johnson says,

This is awesome. I love this blog. I have dealt with this issue before. Usually my issue is monthly anomolies caused by fiscal months of 4 weeks followed by 4 weeks, and then 5 weeks in each quarter. This causes a spike in March, June, Sept., and Dec. It’s one reason I prefer to look at quarterly trends rather than monthly. This chart is quite nice to see these effects. Is there a way to just toggle between two charts? One of weekends and one of weekdays? [...]

This effect can be easily achieved with a cup of coffee, one combo box form control and the good old IF formula. Look at it yourself.

Dynamic grouping of events in Excel Charts - Dynamic Excel Combo Chart

I am not going to provide the complete recipe. But here is the gist. I am sure you can take the help of that coffee in case you are stuck.

  1. Add a combo-box form control using forms tool bar or Developer ribbon. (not able to find the developer toolbar in excel 2007? see this)
  2. Set the input range for combo box to two cells where the values “weekdays” and “weekends” are mentioned.
  3. Also set the “cell link” for the combo box to some free cell like IV32000
  4. Now change the dummy series (the range where the column chart values for zebra lines are mentioned) values to a formula.
  5. The formula should be able to change the dummy values based on the selection in combo box. This is your homework to figure out.
  6. That is all. You now have a chart that dynamically groups events based on user selection. Pretty cool eh ?

Download the workbook and see it yourself

Click here to download the workbook and play with it.

Where can you use this technique?

Oh, several places. To begin with,

  • To highlight new products vs. older products in a product-wise sales chart
  • To highlight top 10 vs. bottom 10 values in a big chart
  • To highlight values of a certain product / project vis-a-vis the whole set of values

What do you think about this idea?

Have you ever tried similar ideas in a report or dashboard? What is your experience? Personally I find dynamic charts more effective compared to static charts. Users like them, they like to play with the control(s) and make their own observations. Do you agree?

PS: If you are looking for a way to compare 2 KPIs or metrics in charts, see the part 5 of dashboard tutorial

Your email address is safe with us. Our policies

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

24 Responses to “Dynamically Grouping Related Events [Excel Combo Charts with Pizzazz]”

  1. Geoff says:

    A great enhancement to an already useful chart.

    Thanks for posting this article!

    Cheers,
    Geoff

  2. paresh says:

    Great !!!!!!!!

  3. Rich says:

    download not going anywhere.

  4. Chandoo says:

    @Geoff and Paresh.. thank you :)
    @Rich… I am sorry, I just noticed it. I have fixed the link. try downloading it now. (you might get a older copy of the page due to caching et al) Here is the updated link : http://chandoo.org/img/n/dynamically-group-related-events.xls

  5. Kapil Kithani says:

    Dear All,

    I am so pleased with this, that I cant tell… I was actually looking for this for so long…. ! :) Thanks so very much to Chandoo and his website….
    One query though.. while I created this chart, w/o downloading the xls sheet, please can you tell me how to start the line graph with the 1st value itself. My chart starts with Zero on the Y axis and then goes to the 1st data point.. (223 in my case)… :( Please help me for that

    Thanks in Advance..!

  6. Chandoo says:

    @Kapil… You are welcome :)

    make sure the sizes of both column chart and line chart are same. Otherwise Excel might assume few values (and thus start the line at zero)

  7. Kapil Kithani says:

    I am sorry Chandoo, I did not get you….. but the line chart starts at 0 and then moves up to the “first” value…

    i have although made sure now that both col and line chart are same in size. please can you help ? :)

  8. Chandoo says:

    @Kapil.. that seems a bit strange. Do this:

    Select the line chart.
    In the formula bar, it shows the series data range.
    What is the first cell’s value in that data range?

    Is it blank or zero? If so, just edit the range and change the starting point to next cell (the one with 223)

  9. Kapil Kithani says:

    Thats was really helpful… Yes it was taking a zero on the 1st value.. :) Thanks once again chandoo…

  10. Sanjay says:

    I had some data where I tried to have this weekdays/weekend effect. I add the extra effect. But I had trouble keeping my real data in front and making the effect as background. To be more precise, my weekdays/weekend bar is overlapping and obscuring my real data unlike here (line is above gray background). How to fix it? Thanks for wonderful site and tips.

  11. Sanjay says:

    Hi Chandoo,
    Just to add a note to previous comment that the original data was plotted in bar chart, not line chart. Therefore, it works only when the original data format is line. However, it does not work if the original data format is bar graph like mine :(. Any tips to make it work keeping the original data in bar chart??

    Thanks

  12. lawrance says:

    Hi chandoo.. thanks for this great website..

    is it possible to insert the dynamic chart above into powerpoint slide? I try to linking as usual, but the excel chart won’t be dynamic anymore…

    thanks…

    • Chandoo says:

      @Lawrance… I am not aware of any method to do this in ppt. You can however insert the excel file in the presentation and ask your viewers to open it. Also, you can insert an animated gif and show it on the slide. One more tricky solution is to use VBA, but I wont recommend it as it is too much work.

      Anyways, let me know if you come across a way to do this.

  13. [...] Band / highlight selected dates to so that users know what they are looking. This can be done using simple formulas and a combo-box control. Here is an example of conditionally banding charts in excel. [...]

  14. [...] colored. The following chart was created using layering, which is relatively simple compared to a graph within a graph approach explained by Chandoo [...]

  15. Fakhar says:

    Hi,
    Again excellent note but I need some help on combo chart in excel 2007 as when I click on Format Control box, and under control there are options for value(checked/uncheked/mixed),cell link and 3-D shading but no box for input range and drop down lines. Moreover, please explain the logic of step 3 above.
    Would really appreciate your help
    Thanks in advance
    Fakhar

  16. Fakhar says:

    Hi Chandoo,

    Ref my email dated February 14,2010 I’ve found that developer ribbon and it worked but now two quwstions 1) is this option available in excel 2003 as well 2) same question as in my previous email, what is the logic of step 3
    and when you and your team are going to update this note on Waterfall Charts using Excel
    Posted on August 10th, 2009 in Charts and Graphs , Featured – 20 comments to incorporate factors with negative values
    Thanks in advance
    Best regards
    Fakhar

  17. Amit says:

    HI,
    I want to seperate “-” and “%” from the below text
    (5% Bank of XYZ – M 05/02/2011)means I want the output like this Bank of XYZ.

    Please Help

  18. Chandoo says:

    @Fakhar… Thanks for the comments

    first up, I am not sure if you have added scroll bar control (form control, not active-x) to excel. If you add it, you should see all the properties as I have shown in the post (they dont change between 2003 and 2007).

    Coming to your question on waterfall charts, I am not sure if I understand it, but my remark in the post is directed towards “stacked charts” not “waterfall charts”.

  19. Chandoo says:

    @Fakhar.. my bad, read “scroll bar” as “combobox”. I guess you have added “checkbox” instead of combos

  20. Amit says:

    HI,
    I want to seperate “-” and “%” from the below text
    (5% Bank of XYZ – M 05/02/2011)means I want the output like this Bank of XYZ.

    Please Help

  21. Chandoo says:

    @Amit… you can use MID and FIND formulas to do this. For eg, assuming your text is in cell A1,

    =TRIM(MID(A1,FIND(“%”,A1)+1,FIND(“-”,A1)-FIND(“%”,A1)-1)) will work.

  22. Deborah Forbes says:

    This is awesome, but I’d like to carry it a step further. I create several different pivot tables to create pie charts from for our firm (by source, Type and tier) for Revenue, AUM, and Clients I’d love to create the checkbox functionality. Currently, I have to create three separate pivot tables because I can’t chart on non-contiguous data (2007) in a pie chart.
    How cool would I look to have one chart in my presentation and just click on the checkbox. any ideas?

  23. neha jain says:

    this is awesome.

Leave a Reply