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.
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.
- 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)
- Set the input range for combo box to two cells where the values “weekdays” and “weekends” are mentioned.
- Also set the “cell link” for the combo box to some free cell like IV32000
- Now change the dummy series (the range where the column chart values for zebra lines are mentioned) values to a formula.
- The formula should be able to change the dummy values based on the selection in combo box. This is your homework to figure out.
- 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
26 Responses to “Dynamically Grouping Related Events [Excel Combo Charts with Pizzazz]”
A great enhancement to an already useful chart.
Thanks for posting this article!
Cheers,
Geoff
Great !!!!!!!!
download not going anywhere.
@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
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..!
@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)
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 ? 🙂
@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)
Thats was really helpful... Yes it was taking a zero on the 1st value.. 🙂 Thanks once again chandoo...
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.
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
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...
@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.
[...] 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. [...]
[...] colored. The following chart was created using layering, which is relatively simple compared to a graph within a graph approach explained by Chandoo [...]
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
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
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
@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".
@Fakhar.. my bad, read "scroll bar" as "combobox". I guess you have added "checkbox" instead of combos
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
@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.
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?
this is awesome.
Very good post. Can you also show other practical usages?
saludando a esos comentarios