To calculate a percentage variance (difference) between two values in Excel, you need to use a simple formula. In this formula, you can deduct the old values from the new values 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 values along with new values. Now here, you need to calculate the percentage variance.
Excel Formula to Calculate Percentage Variance
- First, enter a starting parenthesis and then refer to the cell where you have a new value.
- After that, enter a minus operator and then refer to the old value.
- Next, enter a closing parenthesis and enter and divide the 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 + %”.
Understand the Formula
To understand this formula, let’s break it down into parts.
And this is how it looks when you use real value in the formula.
In the first part, when you delete the new value from the old value, you get the difference that you have between both.
In the second part, you divide that difference with the old value which returns a percentage value which is the variance between both values.
Now let’s come back to our actual example.
When you drag the formula down to the rest of the values, you see it calculates the percentage variance for all the values as well. And, we also have a few values with negative percentages where we have a new value smaller than the old value.
Note: Make sure to have the cell format as a percentage for the result cell. Use the keyboard shortcut Ctrl + Shift + %.