To calculate the percentage variance in excel, let’s take an example for the same.

Example:

percentage variance

As you can observe in the image above, we have taken product number with its sales forecast i.e. the predicted sales and the actual sales of the product.

Previously: How sum all values in column or row using INDEX Formula? 

Variance is basically the difference between the sales forecast and the actual sales, or in other words the difference between the expectancy and the reality.

percentage variance

As every formula begins with an equal to sign, so it is necessary to input the same to bring out the result.

As variance is the difference between the reality and the expectations, so we will select the actual sales first (cell D3).

percentage variance

Then we will subtract the same with the sales forecast (which is cell C3) to get the variance between the same.

percentage variance

Then we will divide the difference with the sales forecast (cell C3). So overall, the formula becomes as follows:

=(D3-C3)/C3

percentage variance

Now all we have to do is press enter. And BAAAMMM!!! The variance between the sales forecast (cell C3) and the actual sales (cell D3) is 0.22222222.

As you can see in the image above, we have dragged down the formula to an extent till it is in need of. It is an easy way to solve things quickly or else jotting down the same formula for every product (that too when you are dealing with thousands of data entries can be a big load).

percentage variance

As we need the variance in percentage, we will go to the number section and select percentage format in the same.

The minute we select the same, our data switches into percentage.

percentage variance

As you can observe in the image above, the variance, which was in general format before is now in percentage format.

So, all you have to do is:

percentage variance

It’s easy, isn’t it?