Sunday 15 June 2008

Excel area chart with colour invert if negative

My intention is for this blog to be commentary on graphs and data, rather than instructions on drawing graphs using any particular program. But I can make an exception, and here's a technique I've worked out for making Area charts in Excel that change colour below the zero line. This is something that comes as a standard option in bar charts. consider the following table:



This makes a bar chart okay

But not such a good area chart

We can create a table that splits the positive and negative values



but the result is disappointing

The areas simply go to zero at the next value, which is not what we want. This happens even when the Excel Time-scale X axis type is selected by going to Chart..Chart Options..Axes and selecting "Time-scale". However, Time-scale has one feature that lets us easily fix the problem. Unlike the ordinary category axis, it does not present values in the same order as they appear in the table, but in strict time order. So if we make a new set of rows below our first set



where the formulae are (left to right, assuming the original table header started at cell A1)

=IF($A2*$A3>0,NA(),$A2)
=IF($A2*$A3>0,NA(),$B2+($B3-$B2)*$A2/($A2-$A3))
=IF($A2*$A3>0,NA(),0)
=IF($A2*$A3>0,NA(),0)


then the two areas should meet the zero line at the same interpolated date!

Remember, this only works if you've selected the "Time-scale" option in Chart Options. If you're looking for more Excel help, see the Jon Peltier and Andy Pope links on the right.

3 comments:

Jon Peltier said...

I've worked out a nearly identical protocol, but your post has beaten mine, which won't hit the airwaves until probably Monday. So I've had to rewrite my intro, giving you credit for being first to post.

Jon Peltier said...

Note that in addition to choosing the time scale or date scale category axis option, you also have to format the axis scale so that it uses days, not months, for the base unit.

derek said...

Ah. That'll be what was responsible for a subtle but niggling bend in the straight line. I couldn't get it to become a gross break, but I couldn't get it to go away, either.