0

I want to create two stacked graphs in one chart object. One should be Stacked Column, and another Stacked Area. They represent revenue per partner per week of the year. On x-axis are weeks of the year, on y-axis is value of revenue. For every week I should have stacked values of revenues. For every year I should have different chart type to be able to compare them visually.

I managed to create sample image but it is not sufficient because when I start I get everything as column or area, then I have to manually format every series (to change type and more cumbersome to change color), and when I want to add partner or new week it is not easy. In general, it is very time consuming and error prone.

Is there easier way to "overlay" two charts, in this case stacked column and stacked area, which have same data structure?

I am also open to suggestions how to better organize data.

@dav: Let's say in column A I have week numbers from 1 to 52. I row 1 I have names of partners. In cell B2 I have revenue from first partner in first week. In C2 I have revenue from second partner in first week. In B3 I have revenue from first partner in week two, etc.

All of this is on sheet 2012. I also have sheet 2013 with same week numbers and same partners but with revenues in year 2013.

I want to create stacked area chart that will show total revenue in 2012 with contribution of every partner. I know how to do this.

After that I want to create another stacked chart, stacked column chart that will show total revenue in 2013 with contribution of every partner. I also know how to do this.

Afterwards, I want to combine these two charts in one so that I can visually compare last year's week with this year+s week. This is what I have trouble with.

Sample chart, almost

Improve this question
4
  • Could you please re-state what the columns and areas are supposed to show? It's unclear to me based on your question and example. Also, what do you mean by changing chart type every year?
    – dav
    Commented Oct 23, 2013 at 16:38
  • I've tried to clarify, but I don't know if I succeeded. :)
    – Milan
    Commented Oct 23, 2013 at 19:58
  • Your clarification helped, thanks. If I understand correctly, you're trying to compare 52 weeks x 2 years x 19 partners for about 2000 data points. I'm concerned that your data vis choice won't provide you good results (especially mixing stacked bar and area charts). Are you open to other methods? If so, what are you trying to accomplish? Comparing two partners to each other, comparing one partner over two years? Finding the highest (or lowest) partner? Should it be interactive, or do you only care about one (or more) partners?
    – dav
    Commented Oct 25, 2013 at 13:21
  • dav, thank you for trying to help. I am aware that I might end up wit too many points, but truth is that I'm not really comparing entire year, more like 8 weeks this year against 8 weeks last year. I am open to suggestions, of course. My intention was to get quick overview of last two months of this year versus last two months of last year and this year partner contribution versus last year partner contribution at the same time just to easily spot trends.
    – Milan
    Commented Oct 25, 2013 at 21:08

1 Answer 1

Reset to default
1

You might have good results using a stacked-clustered column chart. Here is a simplified example.

enter image description here

This is not a native Excel chart type, but with a little clever rearrangement of the data, it's not hard (only tedious) to produce. I have written a tutorial about creating clustered and stacked column charts in Excel.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .