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?
Formula(s) for Variance Percentage:
(New Value – Old Value) / Old Value * 100%
(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%
⇒ Select Cell D2 & type
⇒ Press Enter & you’ll get the variance for January.
⇒ 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.
⇒ 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.
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%
=C2/B2-1 in Cell D2 & press Enter.
⇒ Don’t forget to convert the value into Percentage format.
⇒ 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.
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
ii) For Method 2:
Similarly, in Cell D2, type
=C2/ABS(B2)-1 in lieu of
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.
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.
=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.
⇒ 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.
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.
- How to Calculate Sales Growth Percentage in Excel
- Subtract a Percentage in Excel (Easy Way)
- Calculate Year over Year Percentage Change in Excel (Advanced Technique)
- How to Calculate Percentage of Sales in Excel
- Calculate Growth Percentage Formula in Excel
- How do you Calculate Percentage Increase or Decrease in Excel
- Calculate salary increase percentage in Excel [Free Template]
- Excel Formula to Add Percentage Markup [with Calculator]
- Excel formula to calculate percentage of grand total (4 Easy Ways)
- Calculate Excel percentage difference between two numbers (using formula)