Calculate Percentage Variance (Difference)

HomeExcel FormulasCalculate Percentage Variance (Difference)

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.

list-of-old-and-new-values

Excel Formula to Calculate Percentage Variance

  1. First, enter a starting parenthesis and then refer to the cell where you have a new value.
  2. After that, enter a minus operator and then refer to the old value.
  3. Next, enter a closing parenthesis and enter and divide the operator.
  4. Now, refer to the old value and hit enter to get the result.
  5. 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 + %”.
formula-to-get-percentage-variance

Understand the Formula

To understand this formula, let’s break it down into parts.

percentage-variance-formula

And this is how it looks when you use real value in the formula.

real-value-in-formula

In the first part, when you delete the new value from the old value, you get the difference that you have between both.

delete-new-value-from-old

In the second part, you divide that difference with the old value which returns a percentage value which is the variance between both values.

divide-difference-with-old-value

Now let’s come back to our actual example.

example-with-percentage-variance

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 + %.

🔙 Excel Formulas