Home >  Excel Solutions > 

An Excel Tutorial:

Show Useful Variance Charts
In Your Excel Dashboards

Using charts to display spending variances can clarify budgets,
spending, and variances. But using the wrong type of chart can
make charts virtually useless. We show how to create the right
type of chart in Excel.

Many companies have a difficult time creating charts of spending variances. In fact, many companies rely on charts that are nearly useless for that purpose.

Here's an example of a worthless chart that far too many companies use. This one compares actual and budgeted spending for January through October, on a year-to-date basis.

The problem with this chart is that it hides actionable information; it doesn't display it.

This chart's most obvious information, expressed by the upward-sloping lines, is that year-to-date (YTD) budgets and spending ratchet upwards during the year. That is, the chart is designed to emphasize a mathematical certainty.

The only useful information this chart attempts to convey is the amount of the YTD variance, which is the difference between the two lines shown. But because so much vertical space is used to display the YTD totals, the relatively small amount of variance is virtually impossible to estimate.

To understand how effectively this chart hides information, put yourself in the position of a manager reviewing the chart. If you were going to quiz an employee about this spending performance, you would have virtually no facts on which to base a question. And the employee would have no facts on which to base an explanation. 

In contrast, the following chart presents the same underlying data, but in a format that communicates actionable information.

Here, the tan area chart shows the monthly budget for the year. And the black line shows actual monthly spending.

The olive green columns in this chart show the YTD variance. Unlike YTD spending and budgets, variances don't necessarily ratchet upward. In this chart, for example, the YTD variance was favorable (less than zero) during the first four months of the year. And recently, the YTD variance declined in August and September.

Unlike the first chart, this one provides enough information to alert managers to developing problems. It also provides managers with enough detail to begin a reasonable discussion about the causes of the spending variance.

(To see how we created an Excel chart with tan and olive-green colors, read Display Any Color in Excel.)

Creating the Chart

Although this is a fairly standard Excel chart, it has several unusual characteristics.

The Data Layout

This figure shows the worksheet data that supports the chart. When you launch the Chart Wizard, you will select the range A1:D15, as shown in the figure. So that the Chart Wizard can interpret the layout correctly, make sure that cell A1 is empty.

The chart range includes rows 2 and 15. It does so to provide the horizontal space needed to display the full width of the column chart, as shown in the first figure above.

To display the gray rows successfully, you must make entries in three gray cells.

Enter the formulas shown for these two cells:

C2:    =OFFSET(C2,1,0)
C15:  =OFFSET(C15,-1,0) 

These two formulas return the values in the cells immediately below or above the cells in which the formulas are used. By using the OFFSET function, you can insert new rows between the gray rows without changing the formulas.

To illustrate why the OFFSET function is used in these formulas, suppose cell C15 used the formula =C14. If you inserted a row between rows 14 and 15, the formula in cell C16 would not reference the new cell C15. Instead, it still would reference cell C14. Using OFFSET gets around this problem.

The third entry to make is in cell A2, where you enter an apostrophe. This also is called a single quote. This entry causes Excel to display a blank for the first month of the chart. Without that entry, Excel gets confused about how the months match up with their data.

Cell A3 contains the date value 1/1/2004. The remaining cells in this column contain the equivalent dates shown. To format the dates, select the range A2:A15; choose Format, Cells; in the Number tab choose Custom; then enter "m yy" (without the quotes) as the date format; then choose OK.

Enter the following formula in the cell shown below, and then copy it down the column as shown in the figure.

D3:  =IF(B3=0,NA(),B3-C3+D2)

This formula returns NA() for months in which no actual spending has been entered. This causes Excel to display no line for those months.

The Chart Layout

To create the chart, select range A1:D15 in the preceding figure and launch the Chart Wizard. In the Standard Types tab, choose Line. Then select the top-left chart sub-type. Click Next, and then click Next again.

In the Gridlines tab, deselect all gridlines. In the Legend tab, deselect Show Legend. Then click on Finish.

To turn off background shading, right-click on the plot area; choose Format Plot Area; choose an Area of None; then choose OK.

To display the month and year at the bottom of the chart, right click the horizontal axis, choose Format Axis, and then choose the Patterns tab. In the Tick Mark Labels option box, choose the Low option.

While still in the Format Axis dialog, choose the Scale tab. The checkbox "Value (Y) axis crosses between the categories" probably is checked. Make sure it is unchecked. Then choose OK.

To display the budget data as an area chart, right-click the line that displays the budget data and choose Chart Type. In the Chart Type list box, choose Area. Choose the top-left chart sub-type. Then choose OK.

To display the YTD variance as a column chart, right-click the line that shows this data and choose Chart Type. In the Chart Type list box, choose Column. Choose the top-left sub-type. Then choose OK.

The date format used in this chart is particularly suited for small charts. In your version of the chart, the month and year might be on the same line, rather than wrapped as shown above. To create the wrapped effect, merely make the chart smaller. If you want a larger chart, you probably would prefer to use a different date format.