how to find variance in excel

Variance Charts in Excel is beneficial to compare two sets of data series. In generally variance charts, compare the actuals and targets are drawn. Having an actual value greater than the targeted value is not always favourable. In case of cost, having actual value lesser than the target is recommended. But when it comes to sales, having actual figures greater than the target is favourable. It depends on the nature of data and what the variance is about.

Variance analysis can help a firm to solve many issues from a long-run perspective. It also helps to set targets for the future. A Variance Chart helps to track the firm's productivity based on the actual vs target concept.

Where can it be used?

Variance chart is used mainly for the comparison of data. The following are a few instances where Variance chart is often used.

Comparison between Actual vs. Budget.
Analysing firm's productivity based on the annual target.
Comparison between Previous year profit vs. Current year profit.
In of case stock market, one can compare the return based on the current and previous year.

(NOTE: There is no builtin option to create a Variance chart, but can be created with the help of column chart.)

Steps to create variance charts in Excel

Here is an example that shows the Sales report with Actual Sales and Target Sales. To create a variance chart you don't necessarily need the target values. The actual values alone can be used to show the variance against actual

From the given data extract the Variances.
To find: Variance = Actual Sales – Target Sales, Variance% = Variances/Target Sales.

Step 1: Enter the data in the spreadsheet and select the entire data. We have selected Product Column and Actual Sales Columns as shown below in the image.

Step 2: To insert a chart, Go toInsert menu >>ClickInsert column or Bar chart and selectClustered bar chart.

Step 3: After the Bar chart is ready, stay on the chart, right-click and select the format data series. Alternatively, press Ctrl Key+1 key to get Format data series panel.

Step 4: In the format data series panel, fix the Gap Width to 100 %. This helps to increase the Bar's thickness.

Step 5: Now select the variance column from your data and insert another bar chart. The chart will look as shown in the below image. Since our variance consists of negative numbers as well the chart will have bars would be in both the directions.

Step 6:To show the difference between the positive and negative value.
Make the following changes: Be on the Chart Right -Click select the format data seriesFill=> Check Invert if negative Option (you can give two colour choices for negative and positive). Here we have used green for positive values and red for negative values.

Step 7: Once the difference is highlighted, we can use either the Shift key or Ctrl key and group both the charts.

Pro Trip

Use Alt Keys for align both the charts uniformly.

Step 8: Thus, the variance between the actual and target is displayed, into a single chart knows as Variance chart.

Points to note while creating Variance charts in Excel

To create a variance chart you require the actual and target values.
The actual values alone can not be used to show the variance.

Advantages of Variance charts in Excel

It makes it easy for firms and organisations to keep track of their business goals.
Variance chart makes it easier to understand deviations from targets.
Helps in Risk management.
Easy to identify the changes required in the business plan.
It gives a clear understanding of what can be done in times of fluctuations.

Limitation of Variance charts in Excel

Variance charts could sometimes be time-consuming as it is not a built-in option.
It doesn't give out a clear understanding immediately.

You can view videos on charts available on our Youtube channel.
To have a Glance at How to use Columns charts In Excel- Click Here.
To know how to choose the right charts for your data-Click Here