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.
by Charley Kyd
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.
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
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
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.
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
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
(To see how we created an Excel chart with tan and olive-green
colors, read Display Any Color in
Creating the Chart
Although this is a fairly standard Excel chart, it has several
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
Enter the formulas shown for these two cells:
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
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.
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.
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
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.