Stacked bar chart without stacking

[Andrea]

I am trying to compare two values over time, both as they
change with time and in comparison to the other. I don't
have a lot of space, so I wanted to do a chart like the
stacked chart. However, I don't care what the total value
is, I just want it as a visual comparison reference. Here
is a sample date set:

1997 1998 1999 2000 2001
Germany $1,724 $1,758 $1,790 $1,844 $1,857
US $8,918 $9,300 $9,680 $10,081 $10,190

Is there a way to do a chart that doesn't add up the two
values?

 

[Debra Dalgleish]

Jon Peltier has instructions for stacked charts with vertical separation:

http://www.geocities.com/jonpeltier/Excel/Charts/StackedCharts.html

 

[Andrea]

Unfortunately, vertical separation isn't what I'm looking
for. I like how the Stacked Bar chart looks, but I don't
like how it adds the totals. Is there a way to perhaps do
the Clustered Column chart, but have the columns arranged
so that the smaller column is directly in front of the
larger one?

Thanks!
Andrea

 

[Andy Pope]

Hi Andrea,

You can use a clustered column chart can can the Overlap (located on the
format dialog) value to 100.
Fine if the difference between the 2 data series consistently favours on
or other. Use the Series Order option (also on the format dialog) to
bring the smaller column set to the front.

If the difference changes then you can cope with that by using some
formula and extra data series.

Assuming your example data was stored in cells A1:F3 then use the
formula below to create a 4 rour matrix of values. Enter in the column
for 1997's data and the right drag across.

=IF(B2>B3,B2,NA())
=IF(B3>B2,B3,NA())
=IF(B3<=B2,B3,NA())
=IF(B2<=B3,B2,NA())

What the formulas are doing is choosing the smallest value between
Germany/USA first then largest.

Now create your clustered column chart on the new data ( 5 row x 6 col
matrix. This includes labels)
Change the Overlap value.
The only other thing is to colour the second set of columns the same as
the first set for each country. ie: make USA blue for both sets of USA
columns.

Oh, and if the legend seems a little crowded the additional items can be
deleted. Click the legend once then the legend entry once (not the
legend marker) and the delete.

Hopefully this is clear. If not let me know.

Unfortunately, vertical separation isn't what I'm looking
for. I like how the Stacked Bar chart looks, but I don't
like how it adds the totals. Is there a way to perhaps do
the Clustered Column chart, but have the columns arranged
so that the smaller column is directly in front of the
larger one?

Thanks!
Andrea




vertical separation:

--

Cheers
Andy

http://www.andypope.info