You need to use a simple formula to calculate a percentage variance (difference) between two values in Excel. In this formula, you can deduct the old values from the new ones and then divide that difference by the old ones. 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 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 between them.
In the second part, you divide that difference with the old value, which returns a percentage value, 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, 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 cell format as a percentage for the result cell. Use the keyboard shortcut Ctrl + Shift + %.