A visitor to the Microsoft newsgroups wanted his area chart to show a different color for positive and negative values. This is easy enough to do in a column chart. One technique is simply to use the Invert if Negative formatting option, the other it to make a conditional chart with one series for positive values and the other for negative values. Neither of these techniques works for area charts, but it’s possible to adjust the data to make an area chart with two series, one for positive and one for negative.

Del Cotter has written up a very similar approach in Excel area chart with colour invert if negative. It’s the first I’ve seen of his Information Ocean blog, but I’ve added its feed to my reader, and I’ll keep up with it.

We’ll use the following sample data, selected because it crosses zero several times. The first two columns are the category labels and values, the second two columns have split out negative and positive values, and the fifth column will be used later in the exercise. The formulas that split out signed values are simple:

C2:   =MIN(0,B2)
D2:   =MAX(0,B2)
 

Here is a simple column chart of the original data:

Without too much trauma you can use Invert if Negative to format negative values differently than positive values:

Excel 2007 partly breaks the Invert if Negative functionality described above: if you use solid fills, the negative column can only be filled with white. You’ll need to use the multiple-series approach shown below.

You can construct a conditional chart using the Positive and Negative values computed in columns C and D:

Here is a simple area chart of the original data, as boring as the column chart:

There’s no Invert if Negative setting for area chart fills, so the only apparent option is to plot the Negative and Positive series together. This chart uses the unstacked area chart; the stacked version is uglier and more confusing. The dotted line shows where the series is expected to go.

The problem is that each series goes to zero at the category where the other series has a nonzero value, rather then both meeting in between. What is needed is to interpolate between points that cross zero, in order to have a point at zero. Since this may occur anywhere between a given pair of original categories, we need to use a numerical scale, and a date scale axis is as close as we can get with an area (or line or column) chart. The following shows a second range of calculations in the worksheet.

Cells B10, B12, B14, B16, and B18 link back to cells B2:B6. The blue cells in column B contain either a value of zero or a value midway between the surrounding values, according to this formula:

B11:   =IF(B10*B12<0,0,(B10+B12)/2)
 

What this formula does is enter zero if the connecting line crosses zero (i.e., if the values on either side have a negative product), otherwise it returns the average of the two values.

Cells A10, A12, A14, A16, and A18 contain numerical values of 1, 101, 201, 301, and 401. These are the pseudo-dates for our date scale interpolated axis. These values start at 1, because the first date that is reliably recognized in a chart is 1/1/1900 (i.e., 1). The spacing is 100, which should give sufficient resolution for a chart of reasonable size. The green cells in column A contain a formula that interpolates between the numbers on either side according to the value in the corresponding blue cell. The formula is a simple interpolation:

A11:   =(B11-B10)/(B12-B10)*(A12-A10)+A10
 

Here’s an area chart of the Values data, with a dotted line showing where it should go for a smooth interpolation. (Note for example that the category axis places 67.67 equidistant between 1 and 101.)

We can plot the Negative and Positive series on the chart (stacked or unstacked, they are identical), and almost get what we want, although the category axis spacing is still off, as shown by the dotted lines.

After the category axis is converted to a date scale, and the base unit changed to days, the alignment of the chart series is correct.

The axis labels are not what we need to clearly show the data. Add a series using the original category labels in A2:A6, and the zeros in cells E2:E6 as Y values. Convert the series to a line series. There are only five points, not nine, so the points are plotted at the first five category labels on the interpolated date scale.

Move the new series to the secondary axis group. The new Y axis appears on the right, with a scale of zero to one.

We need a secondary category axis, and Excel usually provides only the value axis when we first move a series to the secondary axis group. Add a secondary category axis, which appears at the top of the chart.

Format the scale of the secondary Y axis so the category axis no longer crosses at the maximum. This makes it cross at zero (the bottom of the chart.

All that’s left is to hide what we don’t want to show. Format the primary category axis so it has no category tick labels. Format the secondary value (Y) axis so it shows no line, no tick marks, and no tick mark labels. Format the secondary category axis so it has no mile and no tick marks, but keep the labels.

An alternative labeling approach that doesn’t need to muck with secondary axes involves adding labels into column E of the second range. The labels are either the original category labels or blanks.

Start with the chart with Positive and Negative series that used the date scale axis to shape the area series correctly. Add a series using A10:A18 for X and B10:B18 for Y. It gets added as another area series (the chart below is a stacked area type).

Convert the added series to a line type.

Use Rob Bovey’s Chart Labeler to add the labels from A10:A18 to the new series.

Format the line series so it uses no line and no marker to hide it, and adjust the position of the labels as appropriate.

Related Posts:

• Area Chart With Gap
• Stacked Area Chart Challenge
• Category Labels That Don’t Overlap the Data
• Fill Below an XY Chart Series (XY-Area Combo Chart)
• Fill Between XY Chart Series (XY-Area Combo Chart)
• Date Axis with Centered Years
• US Employment Slump Chart – How To
• Growth Rates in a Panel Chart
• Two Color XY-Area Combo Chart – Guest Post
• Clustered-Stacked-Column Combo Chart With Lines