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. But let’s first review what Variance Percentage is & how it works. Following we will learn how to calculate variance percentage in excel with 3 Easy Methods.


Download Practice Workbook

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


What Is Variance Percentage?

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 estimates & expenses, this term needs to be mentioned. In Excel, you can find this variance percentage for a huge range of data only in minutes.


3 Easy Methods to Calculate Variance Percentage in Excel

Let’s think about a business company that wants to know the percentage variance between Actual Sales & Estimated Sales for 12 months in the year 2021. In column E, we’re going to calculate this percentage variance.

3 Easy Methods to Calculate Variance Percentage in Excel


Now, let us try the following 3 methods to calculate the percentage variance of the sales amounts.

1. Apply Simplified Formula to Determine Variance Percentage

As we described above, we will first apply the simplified formula to find the variance percentage. Let’s see the process below.

  • First, select cell E5 & type this formula.
=(D5-C5)/C5

Apply Simplified Formula to Determine Variance Percentage

  • Then, press Enter & you’ll get the variance for January.

Apply Simplified Formula to Determine Variance Percentage

  • Now, under the Home ribbon, choose Percentage format from the drop-down in the Number group of commands.

  • Finally, the value in cell E5 will convert into a percentage & show as a Percentage Variance.
  • Next, point your mouse cursor onto the bottom right corner of cell E5, you’ll see a Plus (+) sign called Fill Handle.
  • Now, drag this Fill Handle to cell E16 & release the button.

  • That’s it, you’ve just successfully determined all percentage variances for 12 months.

How to Calculate Variance Percentage in Excel

Read More: How to Do Variance Analysis in Excel (With Quick Steps)


2. Get Variance Percentage in Excel Using Alternative Formula

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

  • First, select cell E5 and type this formula.
=D5/C5-1

Get Variance Percentage in Excel Using Alternative Formula

  • Then, press Enter.
  • Along with it, don’t forget to convert the value into Percentage format.

  • Next, use Fill Handle once again to fill down to cell E16 like before.
  • Finally, you’ll get the desired result as shown in the picture below.

Read More: How to Calculate Percentage Variance between Two Numbers in Excel


Similar Readings


3. Insert Excel IFERROR Function to Calculate Variance Percentage

Let’s think about a scenario where you have to divide the difference between Actual & Estimated Sales by Zero (0). You’ll see an error like cell E11 in the following figure.

Insert Excel IFERROR Function to Calculate Variance Percentage

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

  • First, type this formula in cell E5.
=IFERROR(D5/C5-1,0)

  • Now, press Enter to get the same value as the percentage variance for January as before.
  • Next, fill down cell E5 to cell E16 by using Fill Handle.
  • Finally, you’ll find cell E11 without having any error message as you’ve already fixed the formula considering this occurrence.

In this formula, the IFERROR function is used to avoid the error #DIV/0 in the dataset. It is a beneficial function to trap and manage errors. Lastly, we typed 0 in the formula to get an exact match.

Read More: How to Find Population Variance in Excel (2 Easy Ways)


How to Calculate Variance Percentage for Negative Numbers in Excel

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 to enclose the divisor. This function turns a negative value into a positive one. Let’s check the process below.

  • First, select cell E5 and type this formula.
=(D5-C5)/ABS(C5)

How to Calculate Variance Percentage for Negative Numbers in Excel

  • Then, press Enter and you will see that it is showing the correct value despite having negative numbers.

  • Lastly, use the Fill Handle tool to get results in the cell range E5:E16.

Here, the ABS function is used to return the absolute value of percentage variance between the cells C5 and D5.
Note: The ABS function will show misleading results if one of the values in your dataset is positive and another one is negative.

Conclusion

So, these are all basic & common techniques on how to calculate 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 in ExcelDemy. If you have any questions, suggestions or feedback regarding this article then you’re welcome to comment here.


Related Articles

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