fbpx
Search
Close this search box.

Using Combo Charts to Group Related Time Events [Charting Goodness]

Share

Facebook
Twitter
LinkedIn

This is a guest post by Paresh Shah

In his latest book, Now You See It, Stephen Few discusses techniques and best practices to gain insights from data. Of course Excel does not directly facilitate most of this techniques, but the objective can be achieved without too much work for some techniques.

On pages 165 and 166 of the book, Few discusses how grouping related time intervals can facilitate analysis of data. As an illustration he explains that when viewing data of daily website visits, it helps in separating weekdays and weekends to differentiate expected traffic during these periods. The use of this technique would make it easier for the analyst to identify any anomalous movement in ether the weekend or the week day.

Fortunately excel combo charts can help you do that.

Given below is a combination chart of daily visits to a web site [ hypothetical ] where in the days of the week are shaded. The website visit data has been plotted as a line chart.

Excel combo chart - Group related time events to make your charts better

The website visit data has been plotted as a line chart. The shading has been achieved by using column chart – the data for the secondary series has been plotted on a second axis. A constant data value for Monday to Friday, 3 and a second constant value for Saturday and Sunday, 0 has been assigned for each date of the month. The secondary axis has thereafter been hidden. The maximum value for the second axis has been manually set at 3 to get the columns to run from the top to bottom and gap between the columns has been set to zero [ Format data series ->Gap width->No Gap ]. The secondary axis has thereafter been hidden.

The concept can be used for other groupings too, months grouped by year, by quarter etc without too much effort.

Download this excel combo chart and play with it to learn more

Click here to download the tutorial workbook and learn by changing things.

Added by PHD

Thank you Paresh. That is an innovative way to achieve zebra lines / bands on the charts to group related events.

Hello there, my dear reader, if you have enjoyed this charting trick, say thanks to Paresh.

Further Resources on Excel Combo Charts

PS: the link to Now You See It uses my Amazon referral ID. I suggest reading the book if your job involves telling stories using charts.

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

Excel School made me great at work.
5/5

– Brenda

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.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

10 Responses to “Using Combo Charts to Group Related Time Events [Charting Goodness]”

  1. Geoff says:

    Thanks for the article & attached spreadsheet Paresh.

    Do you think the column plots on the secondary axis could also be used to more easily mark key weeks (eg. when a new product is released?

  2. Martin says:

    Thanks both, Paresh and Chandoo, for this article.

    The first idea that crossed my mind after reading this, is to combine it with the waterfall chart I've created to show monthly data, and use this technique to group by quarters, to easily identify those periods.

    Thanks !!

  3. 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?
    I couldn't see the shading, but when I downloaded, I was able to darken it a bit. THANKS for this.

  4. Paresh says:

    Great community here ! Thank you too.

    Geoff - We can do this if we put the constant value only for the days of the key week with zero for others. It would shade that week.

    Art - We could create another series, with constant on the weekend days. Plot this on the secondary axis also. Hide one of the series [ no fill and no line] as required. This would achieve the effect.

  5. Paresh says:

    Great community here ! Thank you too.

    Geoff - We can do this if we put the constant value only for the days of the key week with zero for others. It would shade that week.

    Art - We could create another series, with constant on the weekend days. Plot this on the secondary axis also. Hide one of the series [ no fill and no line] as required. This would achieve the desired effect.

  6. [...] 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 [...]

  7. Chandoo says:

    @Art... I have taken your query and posted an extension to this tutorial where you can learn how to dynamically switch focus from say weekdays to weekends. Check it out http://chandoo.org/wp/2009/08/27/dynamic-event-grouping-in-charts/

  8. LK says:

    Is there a way of making this work when the line graph is a column chart as well?

  9. Hui... says:

    @LK
    If you right click your line and change the chart type to Column is that what you want ?

  10. LK says:

    @ Hui

    No, I was trying to figure out how to incorporate this into a Waterfall/Cascade Chart.
    Due to the Hide Series (+ and -), the Gray Shaded bar was only showing above the Cascade Chart but not below.
    I tried adjusting the order of the series but that did not work.

    I used Jon Peltlier's tutorial to create the original waterfall and had 7 series: EndPoints, Hide (+), Red (-), Green (-), Hide (-), Red (+), Green (+)
    and then the Series for Shading.

Leave a Reply