Dynamically Grouping Related Events [Excel Combo Charts with Pizzazz]

Posted on August 27th, 2009 in Charts and Graphs - 22 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

| More
Subscribe for PHD Email updates and get a free excel e-book with 95 tips & tricks

Comments
Geoff August 27, 2009

A great enhancement to an already useful chart.

Thanks for posting this article!

Cheers,
Geoff

paresh August 27, 2009

Great !!!!!!!!

Rich August 27, 2009

download not going anywhere.

Chandoo August 27, 2009

@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

Kapil Kithani August 31, 2009

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..!

Chandoo August 31, 2009

@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)

Kapil Kithani September 1, 2009

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 ? :)

Chandoo September 1, 2009

@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)

Kapil Kithani September 2, 2009

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

Sanjay September 25, 2009

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.

Sanjay September 25, 2009

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

lawrance September 28, 2009

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 September 29, 2009

@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.

Fakhar February 14, 2010

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

Fakhar February 15, 2010

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

Amit February 15, 2010

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

Chandoo February 15, 2010

@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”.

Chandoo February 15, 2010

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

Amit February 16, 2010

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

Chandoo February 16, 2010

@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.

RSS feed for comments on this post. TrackBack URI

Leave a comment

   Name (required)

   E-mail (required, never displayed)

   URL


If you have a question, please ask in the forums

Recommended Excel, Charting, VBA books