Problem with Stacking Column Chart

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I want to do a stacking column chart which compares two
values on the same column. Let's say Jan 03 in red and Jan
02 in blue. 03 value is 500, and 02 value is 800. I want
it to show red column up to 500, and then blue column up
to 800 (so it is just showing blue for 300 worth). The
only thing I can get a stacked column to do is add them
together. In other words, it puts the red up to 500, and
then blue up to 1300. That's not what I want. I have
messed around with 3D columns as well, and can get it to
show it okay, but then if I have another column where the
numbers may be reversed, it doesn't work. In other words,
if 02 value was 500 and 03 was 800. Then the colors would
be swapped. I have seen this done somewhere before, but
can't remember where. It would be a lot easier to explain
with a picture which I have on an excel file if anyone
wants to look at it. Thanks.
 
Mike -

You can do this with a clustered column chart, if the larger number is
the first series. Make the chart, double click either series, and on the
Options tab, change the overlap to 100%.

Or make a stacked column, and chart the difference between 2003 and 2002
as the second series; the 300 will stack on top of the 500.

- Jon
 
Thanks anyways, but this doesn't solve the problem really.
If the year 2003 was higher than 2002 for all months, then
overlapping would work. But if one month is higher in
2002, and then the next month is higher in 2003, it blocks
some of the data. You can only have one series on top of
the other. So you have to pick one.
 
So do you always want to show the smaller one in front, even if it's a
different year? Personally, I'd prefer side by side (clustered) columns,
but try this:

Col A: Year
Col B: data for 2003
Col C: data for 2004
Col D: Bottom if 2003
Col E: Bottom if 2004
Col F: Top if 2003
Col G: Top if 2004
Col H: Value if it's a tie

Suppose the first data is in row 2, with meaningful headers in row 1
(except that A1 is blank, of course). Put these formulas into the
indicated cells:

D2: =IF(B2<C2,B2,0)
E2: =IF(B2<C2,0,C2)
F2: =if(B2<C2,0,B2-C2)
G2: =if(B2<C2,C2-B2,0)
H2: =if(B2=C2,B2,0)

Fill these formulas down as far as you need it. Make a stacked column
chart from columns A and D-H: select the part of column A you need,
including the blank at the top, then hold Ctrl while selecting the same
part of D-H, including the headers at the top, then start the chart wizard.

Format the data from D and F the same way (for 2003), E and G the same
(for 2004), and H another way. You'll have duplicate legend entries for
each year. Select the legend, then select the text of the legend entry
(not the colored box or you'll hose the series), and press Delete.

- Jon
 
Back
Top