How to Calculate Variance Percentage in Excel (3 Easy Methods)

In Microsoft Excel, you can use the most common & basic formula directly to calculate Variance Percentage. Here I’m going to show how you can do this calculation by inserting the direct formula with ease but let’s first review what Variance Percentage is & how it works.


What Is Variance Percentage & Where Can You Use It?

Variance Percentage indicates the percentage difference between a new value & an old value subject to the old value. It represents the percentage of change between two values.

Formula(s) for Variance Percentage: 

(New Value – Old Value) / Old Value * 100%

or, 

(New Value / Old Value-1) * 100%

Variance Percentage is widely used in business accounting & economics. It can determine the percentage of profit & loss under a given dataset. To find the differences or changes in the values of temperature, product sales, budgetary estimations & expenses, this term needs to be mentioned. In Excel, you can find this variance percentage for a huge range of data only in minutes.


Download Practice Workbook

You can download our worksheet to practice yourself that we’ve used to prepare this article.


3 Easy Methods to Calculate Variance Percentage in Excel

1. Using Simplified Formula to Determine Variance Percentage

Let’s think about a business company that wants to know the percentage variance between Actual Sales & Estimated Sales for 12 months in a particular year.

In Column D, we’re going to calculate this percentage variance.

Formula Syntax: (New Value – Old Value) / Old Value * 100%

Finding percentage variance by using simple formula

Step 1:

⇒ Select Cell D2 & type =(C2-B2)/B2

⇒ Press Enter & you’ll get the variance for January.

Finding percentage variance by using simple formula

Step 2:

⇒ Under the Home ribbon, choose Percentage format from the drop-down in the Number group of commands.

The value in Cell D2 will convert into percentage & show as Percentage Variance.

Finding percentage variance by using simple formula

Step 3:

⇒ Point your mouse cursor onto the right bottom corner of Cell D2, you’ll see a ‘+’ sign which is called Fill Handle.

⇒ Drag this Fill Handle to Cell D13 & release the button.

Finding percentage variance by using simple formula

You’ve just successfully determined all percentage variance for 12 months.


2. Applying Alternative Formula to Calculate Variance Percentage in Excel

Now we’ll use the same datasheet but apply an alternative formula that is quite easier to type than the previous one.

Formula Syntax: (New Value / Old Value-1) * 100%

Step 1:

⇒ Type =C2/B2-1 in Cell D2 & press Enter.

⇒ Don’t forget to convert the value into Percentage format.

Alternative Formula to Calculate Variance Percentage in Excel

Step 2:

⇒ Use Fill Handle once again to fill down to the Cell D13 like before.

You’ll get the desired result as shown in the picture below.

Notes:

In some older Excel versions, you may find error messages while dividing with negative values. In that case, you have to use the ABS Function(Turns a negative value into a positive one) to enclose the divisor.

The formulas then will be-

i) For Method 1: 

In Cell D2, you have to type =(C2-B2)/ABS(B2) instead of =(C2-B2)/B2

ii) For Method 2:

Similarly, in Cell D2, type =C2/ABS(B2)-1 in lieu of =C2/B2-1


3. Inserting Excel IFERROR Function to Avoid #DIV/0 Error While Calculating Variance Percentage

Let’s think about a scenario where you have to divide the difference between Actual & Estimated Sales by 0(Zero). You’ll be shown an Error like Cell D8 in the following figure.

Excel IFERROR Function to Avoid #DIV/0 Error While Calculating Variance Percentage

You can check what type of error it is by clicking on the Error Checking option attached to Cell D8. It’s showing Divide by Zero Error here. So we have to correct this error now.

Excel IFERROR Function to Avoid #DIV/0 Error While Calculating Variance Percentage

Step 1:

⇒ Type =IFERROR(C2/B2-1,0) in Cell D2

What we’re doing here is telling Excel if any error occurs then the percentage value will be shown as 0 (Zero).

⇒ Now press Enter.

You’ll get the same value as percentage variance for January as before.

Excel IFERROR Function to Avoid #DIV/0 Error While Calculating Variance Percentage

Step 2:

⇒ Fill down Cell D2 to D13 by using Fill Handle.

This time, you’ll find Cell D8 without having any error message as you’ve already fixed the formula considering this occurrence.


Concluding Words

So, these are all basic & common techniques to find out variance percentage in Excel. I have tried my best to make the instructions as easy as possible. I hope, this article has guided you with proper & convenient instructions. You can have a look at other articles related to a wide range of Excel functions on this website. If you got any questions, suggestions or feedback regarding this article then you’re welcome to comment here.


Further Readings

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo