Deviation Column Charts

This page shows how to use column charts to show deviations from budget. Two ways are shown to differentiate positive and negative deviations: the Invert if Negative formatting option for a single series, and the use of separate series in the same chart.

The Data

The data used in this example is shown below. Column A contains category labels, which may relate to companies, or divisions, or time periods (years, quarters, etc.). Row 2 contains column headers for Budget, Actual, and the various deviations (these will become series names in the charts produced below). Note that cell A2, the intersection of the categories (column A) and the series names (row 2) is left blank. This blank cell helps Excel parse the chart source data into categories, series names, and series data.

	A	B	C	D	E	F
1	Budget and Actual Revenues
2		Budget	Actual	Dev	Pos Dev	Neg Dev
3	AB	1200	1250	4.2%	4.2%	0.0%
4	CD	1000	900	-10.0%	0.0%	-10.0%
5	EF	900	950	5.6%	5.6%	0.0%
6	GH	1150	1100	-4.3%	0.0%	-4.3%

The deviations from budget revenue in columns D, E, and F are calculated using the following formulas, and the cells are formatted as percents with one decimal place (see Number Formats). Column D shows deviation of Actual from Budget, while columns E and F show positive and negative deviations, respectively.

Range	Formula in top cell
D3:D6	`=(C3-B3)/B3`
E3:E6	`=MAX(C3-B3,0)/B3`
F3:F6	`=MIN(C3-B3,0)/B3`

The Charts

The raw data with the Budget and Actual numbers is easily plotted. Select the range A2:C6 in the worksheet, start the chart wizard, and choose the clustered column chart type in step 1. Since there is the blank cell in the top left of the selected range, Excel correctly determines category labels for the bottom of the chart and names for the two series.

To focus on the deviation of the Actual numbers from Budget, plot the Dev values in the table. Select A2:A6, then hold CTRL while selecting D2:D6, start the chart wizard, and choose the clustered column chart type in step 1.

That's great, shows just what I want, you say. But how can I distinguish positive from negative deviations? There are two ways: use the Invert if Negative setting for the series fill color, or use two series, one each for positive and negative deviations.

To use the Invert if Negative setting, double click the series, and check the Invert if Negative box under the color selection palette in the Patterns tab (indicated by the encircled checkbox at right). This produces the chart below.

But this is boring, you say. How can I get a better color than White for my negative values? My boss wants it to be Red.

Excel has inverted the negative columns to use the default second color, but the Patterns tab of the Format Series dialog only allows you to specify the first color.

To choose a second color, double click the series again (or do this in the same double click where you selected Invert if Negative). On the Patterns tab, click the Fill Effects button under the Area color palette, to see the Fill Effects dialog (left). Select the Two Colors option (indicated by the red arrow), then pick the desired color using the Color 2 dropdown. Click OK twice, to get the chart below.

This isn't what I wanted, either, you say. I HATE gradient fills.

I hate gradient fills too, but your chart needs to show them, for a moment at least, so your choice of second color sticks. Double click the chart again, and in the Patterns tab, click again on the first color in the Area color palette. Click OK, and you get solid columns, with positive values shown in your selected color 1, and negative values shown in your selected color 2.

The result is shown below at left. If you add a legend (below at right) you see that the chart consists of a single series.

The second way to show positive and negative deviations with distinct formatting is to use a separate series for each desired format. This is the secret to Conditional Charts, described elsewhere on this web site. Select A2:A6 in the data, then hold CTRL while selecting E2:F6, start the chart wizard, and choose the stacked column chart type in step 1. This produces the chart shown below at left. If you add a legend (below at right), you see that the chart consists of two series.

If you choose a clustered column chart type for this data, the positive columns would be offset to the left and the negative columns to the right, as shown below. This is because the clustered column chart would use Positive as the first and Negative as the second of the two columns in each cluster. You can fix this by changing the chart type to stacked column, or by double clicking either series, and on the Options tab, change Overlap to 100, so the columns completely overlap each other.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile