How To Insert a Variance Graph

You certainly know how to calculate a variance in Excel. But do you know how to insert variance graphs?

Variance charts in Excel can be used when you wish to compare two sets of data, for example, if you wish to compare the sales forecasts to the actual sales over a period.

You will need to have the data listed within a table before generating the chart. The table must have labels clearly identifying the actuals vs. predicted figures and the variance, which is the difference between those two figures.

For the purposes of this guide, sample data has been created for you to use. In the sample data, the scenario is of a manager of a car dealership wishing to chart predicted car sales versus actual car sales over a year.

Steps to create a variance chart

The steps below will take you through the process using some sample data. A quick reference guide is available at the end of this document.

Open the Car sales Excel spreadsheet.

sar sales spreadsheet

Click anywhere within the data.

On the menu, select Insert, then click Recommended Charts.

recommended charts ribbon button

The Insert Chart window opens, on the left, under Recommended Charts click on any of the types of charts listed; a preview of the chart appears on the right. Be sure to select a chart that plots the variance figures (you will see the variance label on the chart). Not all the recommended charts will do this. You will see a line labelled ‘Variance’.

insert line variance chart

Click the All Charts tab to see additional chart options. As before, select only charts that plot the variance figures.

See also  Chart with a single x-axis but two different ranges

insert column variance chart

In the All Charts view, hover your mouse over a chart in the preview area to see a larger view of the chart.

insert variance column clustered chart

Once you decid on a chart, click OK in the bottom right corner of the window. Excel inserts the chart into the spreadsheet.

Excel opens a Chart Design tab. Use this to edit the chart as you wish.

design variance chart This is what a Variation Plot looks like:

Excel variance chart

Quick reference guide

  1. Open the Excel spreadsheet containing the data you wish to chart.
  2. Click anywhere within the data.
  3. On the menu select Insert then click Recommended Charts.
  4. The Insert Chart window opens, on the left under Recommended Charts click any of the types of charts listed; a preview of the chart appears on the right.  Be sure to select a chart that plots the variance figures, not all the recommended charts will do this.  You will see a line labelled ‘Variance’.
  5. Click the All Charts tab to see additional chart options. As before, select only charts that plot the variance figures.
  6. In the All Charts view, hover your mouse over a chart in the preview area to see larger view of the chart.
  7. Once you decided on a chart, click OK in the bottom right of the window, Excel inserts the chart into the spreadsheet.
  8. Excel opens a Chart Design tab, use this to edit the chart as you wish.