The Excel percentage difference formula shows how far two values are apart relative to their average.
Learn when this measure fits, how to enter it in a worksheet, and what to do with zero or negative values.
What does percentage difference measure?
Percentage difference compares two values without treating either one as the starting value.
It divides the absolute gap between them by their average, then expresses the result as a percentage:
=ABS(B2-A2)/AVERAGE(A2,B2)
If A2 contains 80 and B2 contains 100, the absolute difference is 20 and the average is 90.
The calculation is 20 divided by 90, or about 22.22%.
Format the result cell as Percentage to display that value.
This measure is useful when comparing two equally important quantities, such as two laboratory readings, two quoted prices, or measurements taken with different methods.
It treats the values symmetrically: swapping A2 and B2 produces the same percentage difference.
How do you calculate percentage difference in Excel?
- Enter the first value in A2 and the second in B2.
- In C2, enter
=ABS(B2-A2)/AVERAGE(A2,B2). - Select C2 and choose the Percentage number format.
Adjust the decimal places as needed.
For example, if A2 is 48 and B2 is 60, the formula returns 0.2222… before formatting, which displays as 22.22%.
The absolute value function, ABS, makes the result nonnegative regardless of which value is larger.
You can also write the same calculation explicitly as =ABS(B2-A2)/((A2+B2)/2).
The AVERAGE version is often easier to read.
Both formulas require a nonzero average and numeric inputs.
Percentage difference versus percentage change
These terms describe different calculations.
Use percentage change when one value is a baseline and the other is the result—for example, comparing this month’s sales with last month’s.
The standard formula is:
=(NewValue-OldValue)/OldValue
If the old value is in A2 and the new value is in B2, enter =(B2-A2)/A2.
With 80 in A2 and 100 in B2, the result is a 25% increase.
Reversing the values gives a 20% decrease relative to 100.
That direction and baseline matter, so percentage change is not symmetric.
Use percentage difference when you want a neutral comparison between two values and neither is the baseline.
Use percentage change when you want to report an increase or decrease from a defined starting amount.
Choosing the right formula prevents a mathematically correct result from answering the wrong question.
How do you show the direction of the difference?
The absolute difference formula removes direction.
To show which value is higher while still scaling by the average, use =(B2-A2)/AVERAGE(A2,B2).
A positive result means B2 is greater than A2; a negative result means B2 is smaller.
Format the cell as Percentage.
For values 80 and 100, entered in that order, the signed result is about 22.22%.
If you swap them, it is about -22.22%.
This signed measure can be useful in a comparison table, but label the order clearly so readers know which value is being compared against the other.
What happens when a value is zero or negative?
If both values are zero, their average is zero and the formula divides by zero, producing #DIV/0!.
If the two values are equal and nonzero, the result is 0%.
A zero paired with a positive number works: comparing 0 and 50 gives 200%, because the gap of 50 is divided by the average of 25.
For a blank or zero-average comparison, you can return a message instead of an error:
=IF(AVERAGE(A2,B2)=0,"N/A",ABS(B2-A2)/AVERAGE(A2,B2))
Format the result as Percentage; the text result will remain “N/A.” If your policy is to display zero instead, replace "N/A" with 0.
Pick a response that communicates what an undefined comparison means in your report.
Negative values need extra care.
When the values have opposite signs, their average can be zero, so the formula may be undefined.
When both are negative, the average is negative and the usual formula can return a negative result despite using ABS in the numerator.
Percentage difference is generally intended for nonnegative quantities.
For signed measurements, define a domain-appropriate denominator or report the absolute gap alongside the values.
How can you calculate the formula for a whole column?
Keep the two compared values in columns A and B, and put the formula in C2.
If the data is an Excel Table, add a calculated-column formula such as =ABS([@Value2]-[@Value1])/AVERAGE([@Value1],[@Value2]).
Excel fills the formula down the table and adjusts it as rows are added.
In a regular range, copy C2 down using the fill handle.
Each relative reference changes by row, so C3 compares A3 with B3.
If one input must always refer to a fixed cell, use an absolute reference such as $A$2; dollar signs keep that row and column fixed when copying.
Which Excel versions support this formula?
ABS, AVERAGE, and IF are longstanding worksheet functions supported in modern desktop Excel, Excel for the web, and Google Sheets.
The formula does not require a dynamic-array feature or a recent Excel release.
In locales that use semicolons as argument separators, replace commas with semicolons; some locales also display decimal commas.
Why might the displayed result look wrong?
- The cell shows a decimal: Apply Percentage formatting.
A stored value of 0.2222 displays as 22.22% when formatted as a percentage. - The result is 2,222%: Check that you did not multiply by 100 and also apply Percentage formatting.
The number format already scales the display. - The result shows
#DIV/0!: Check whether the average of the inputs is zero and choose an explicit fallback for that case. - The result is negative: Check whether you used the signed formula or whether one or both inputs are negative.
UseABSaround the difference for a nonnegative gap, and confirm that the average is an appropriate denominator. - The formula appears as text: Change the cell format to General, then re-enter the formula.
Also check that it begins with an equals sign and that “Show Formulas” is turned off.
When sharing results, include the two input values and state whether the figures represent percentage difference or percentage change.
That context makes the denominator and the meaning of the percentage clear to readers.