Stacked Column Chart with multiple data groups

Hello,

Looks like you are after a clustered stacked column chart. These don't come pre-defined in Excel, but you can create them by re-arranging your data a bit.

Jon Peltier has a step by step walk through on his site.

cheers, teylyn

Yes, if you want clustered stacked columns, then that's what you need to do.

Don't get overwhelmed. Take it one step at a time. If you get stuck, pipe up and I'll be glad to help.

Originally Posted by dspblues

I've been following it, but my results aren't matching his. Do I need to rearrange the data? It's not setup the same way.

I find that much of charting in Excel is not about the charts themselves, but about the way data is arranged in the spreadsheet. I would suggest that, yes, you should arrange your data the same way Jon arranges his data -- at least until you are completely familiar with how and why his spreadsheet layout works for this chart type.

I find that much of charting in Excel is not about the charts themselves, but about the way data is arranged in the spreadsheet. I would suggest that, yes, you should arrange your data the same way Jon arranges his data -- at least until you are completely familiar with how and why his spreadsheet layout works for this chart type.

I'd do that, but my data is slightly different than his.

Yes, it is, but not dramatically so. Can I assume that you are allowed to copy/paste/rearrange/manipulate the spreadsheet? This is one of those charts where data layout will be critical. We cannot create a clustered stacked column chart from the data layout as it is, but, it should not be overly difficult.

The first thing I note in Jon's tutorial is that he will have his data in columns. In his example, he puts "commodities" across the x axis (going down the column), the budget values are on the y axis (also going down each column), and each quarter's actual and budget as individual data series. The main difference between Jon's layout and yours is that you have the commodity actual below the commodity forecast values, where Jon would have the actual values off to the right of the forecast values. Would you be able or allowed to move (cut and paste will work) D8:P10 do that these values start in Q5? Once you have done that, your data layout should be the same as Jon's, and you should be able to follow the tutorial to get the same result that he gets.

I would suggest going that far just to get a handle on how it all works. If, at the end, you decide you want quarters on the x axis (instead of commodities), it should be the same thing, only you will want to transpose the data before you start.

Part of the difference is I have 3 "commodities", but I really want them in the same stacked column. What I want in separate columns is the Forecast vs. actual.

If I understand what you mean by this, as I suggested above, you should simply need to "transpose" the data (so quarters are down the left side of the table) and commodities are across the top of the table. It is, otherwise the same procedure.

In Jon's tutorial, he has columns labeled "Q1 actual", "Q2actual", "Q1 budget",.... Your column labels would be "apples forecast", "oranges forecast", "bananas forecast", "apples actual", "oranges actual", "bananas actual".