To calculate percentage variance, subtract the old value from the new one and divide by the old value: =(B2-A2)/A2, where A2 is the old figure and B2 the new one. Then format the cell as a percentage with Ctrl+Shift+%, or you’ll see 0.25 instead of 25%. A positive result is an increase, a negative one a decrease.
The shorter version =B2/A2-1 gives exactly the same answer with less typing. Two cases will break it.
If the old value is zero, you get #DIV/0! — there’s no meaningful percentage change from nothing, so wrap it in =IFERROR((B2-A2)/A2,""). And if the old value is negative, the sign flips and the result is misleading, so use =(B2-A2)/ABS(A2) when your data can go below zero.
For the variance as a positive number regardless of direction, wrap the whole thing in ABS.
You need to use a simple formula to calculate a percentage variance (difference) between two values in Excel. In this formula, you subtract the old value from the new one and then divide that difference by the old value. This way, you get the percentage of the change compared to the old value.
In the following example, you have a list of old and new values. Now, here, you need to calculate the percentage variance.
Excel Formula to Calculate Percentage Variance
- First, enter the equals sign and a starting parenthesis, and then refer to the cell where you have the new value.
- After that, enter a minus operator and then refer to the old value.
- Next, enter a closing parenthesis and then the divide operator (/).
- Now, refer to the old value and hit enter to get the result.
- In the end, you need to change the format of the cell where you have the result value. So, use the keyboard shortcut “Ctrl + Shift + %”.
With the old value in cell A2 and the new value in cell B2, the formula looks like this:
=(B2-A2)/A2
You can also write it as =B2/A2-1, which gives the same result. For example, an old value of 100 and a new value of 120 give 20%.
Understand the Formula
To understand this formula, let’s break it down into parts.
And this is how it looks when you use real values in the formula.
In the first part, when you subtract the old value from the new value, you get the difference between them.
In the second part, you divide that difference by the old value, which returns the variance between both values as a decimal (like 0.2), which shows as a percentage (20%) once you format the cell.
Now, let’s come back to our actual example.
When you drag the formula down to the rest of the values, it also calculates the percentage variance for all the values. We also have a few values with negative percentages where we have a new value smaller than the old value. Make sure to use the percentage format for the result cell. Use the keyboard shortcut Ctrl + Shift + %.
When the Old Value Is Zero
If the old value is 0, the formula divides by zero and returns the #DIV/0! error, as a change from zero can’t be shown as a percentage. Use IFERROR to show a blank instead (or any text you like, such as “n/a”):
=IFERROR((B2-A2)/A2,"")
When the Old Value Is Negative
With a negative old value, the sign of the result flips. For example, a loss that goes from -100 to -50 is an improvement, but the formula returns -50%. To get the right sign, divide by the absolute value of the old value with ABS (it returns 50% for this example):
=(B2-A2)/ABS(A2)
Frequently Asked Questions
What is the difference between percentage variance and percentage difference?
Percentage variance (change) compares the new value with the old one, so the order matters. Percentage difference compares two values without a “before” and “after”, using their average: =ABS(A2-B2)/AVERAGE(A2,B2). For 100 and 120, the variance is 20%, and the difference is about 18.2%.
Why does my result show 0.2 instead of 20%?
The result is correct; the cell just isn’t formatted as a percentage. Press Ctrl + Shift + % or use the % button on the Home tab. Don’t multiply the formula by 100 as well, or you’ll get 2000%.
Can I calculate the variance for a whole column at once?
Yes. Enter the formula in the first row and double-click the fill handle (the small square at the bottom right of the cell) to copy it down to the end of your data.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.