Stacked charting

[John]

Hi

I have three series; Income Target, Confirmed Income and Provisional Income.
I would like to stack Confirmed Income and Provisional Income on top of each
other to make combined income and then I want the Income Target next to it
so I can compare. Unfortunately as far as I can see charts either only stack
all series or place all of them side by side, they don't mix between
stacking and placing side by side. Is this correct or am I missing
something.

Regards

 

[Risky Dave]

John,

I had to do something very similar recently.

Have a look at Jon Peltier's excellent page and the links from it:

http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html

Dave

 

[AdamV]

ClusterStack is overkill for this.

All you need to do is chart the first two series as a stacked bar, and then
move the target to the secondary axis. Once it is there you can choose a
different chart type of you want to, although you could leave it as a stacked
bar with only one bar in its stack.
If you have Excel 2000/2003 you plot all three, then right click the Target,
goto Format Data Series, choose the Axis tab and select "Secondary Axis".
In 2007, the principle is the same, but the choice of Axis is under the
"Series Options" section
In order that the Target does not obscure the other series, you should make
the Gap Width bigger for that series, resulting in narrower bars. How much
narrower is down to you.

A neat way to show these is to actually have a fourth series containing very
small values (eg 0.001). Stack this series on top of Target against the
secondary axis. Make the Target series invisible with no fill and no border,
and then the extra fourth series will appear as a nice floating horizontal
line across the income figures at the level of the target. This is easier to
get right in Excel 2007 - older versions can make these disappear if they
coincide with other elements such as the top of another bar.
You should also tidy up to remove the secondary axis to avoid too much
visual clutter.

 

[Jon Peltier]

I show a few alternatives near the end of this blog post:
http://peltiertech.com/WordPress/2008/04/10/the-perils-of-being-in-3d/

I was initially talking about the evils of 3D charts, but in the process
showed a handful of effective ways to show the goal in a flat 2D chart. The
first is similar to your floating "line".

- Jon