How to calculate percent difference in Excel

The percent difference formula

The sheet compares two suppliers' quotes for the same items. Neither quote is the starting point, so you want a single number for how far apart they are. Percent difference takes the gap, ignores its sign with ABS, and divides by the average of the two values.

Format the result as Percent (Home > Number > %) and add a decimal place if you need one. Writing the average out as (B2+C2)/2 gives the same result.

Percent difference vs percent change: which one you need

Percent change measures from a base: =(C2-B2)/B2 says Supplier B is 8.3% more expensive than Supplier A. Turn it around, =(B2-C2)/C2, and Supplier A is 7.7% cheaper than Supplier B. Both are correct; they answer different questions, because the base changes.

Percent difference has no base, so swapping the two values gives the same 8%. Use it for two values on equal footing: two quotes, two lab readings, two stores or two people measuring the same thing. Use percent change when one value is clearly first or is the reference: last year vs this year, budget vs actual, list price vs sale price.

The gap between the two grows with the size of the difference. For the paint, the quotes are 61 and 89: B is 45.9% more than A, A is 31.5% less than B, and the percent difference is 37.3%. If your manager asks "what's the percent difference?" but means "how much more did we pay?", they want percent change.

Flag pairs that are too far apart

A common use is checking that two numbers agree within a tolerance, such as two counts of the same stock or two quotes before you approve one. Put the tolerance in a cell, here 5% in F1, and compare against it with a $-locked reference so you can fill the formula down.

Set the tolerance yourself based on what matters for your data; a 5% gap on fasteners and on a large equipment order are very different amounts of money. For large values, consider checking the absolute gap, C2-B2, as well.

Zeros: 200% and #DIV/0!

If one value is 0 and the other is not, percent difference is always 200%, however big the other value is. Shipping is 0 from Supplier A and 45 from Supplier B, and the result is 2, which shows as 200%. That is the correct math, but it carries no information about size, so report the absolute gap for those rows instead.

If both values are 0, the average is 0 and the formula returns #DIV/0!. Two zeros are identical, so 0% is the honest answer. Test for it explicitly rather than wrapping the formula in IFERROR, which would also hide text or other mistakes in the cells.

Negative values: the result stops making sense

When the two values have opposite signs, their average can be tiny, zero or negative. The rebate row compares a 50 credit (-50) with a 30 charge. The average is -10, so the formula returns -8, which would show as -800%. A negative percent difference is meaningless, and a slightly different pair could divide by zero.

For data that can be negative, such as profit, temperature or net cash, do not use percent difference. Report the absolute difference instead (here 80), or use percent change from a positive base if one value is the reference. You will find a variant online that divides by the average of the absolute values, AVERAGE(ABS(B8),ABS(C8)); it avoids the negative result, but it is not a standard definition, so label it clearly if you use it.

The whole column at once

In Excel 365 and 2021 you can write the formula once for all rows. It spills down: 0.08, 0.031, 0.373, 2 (the zero row), #DIV/0! (both zeros) and 0 (identical quotes). In older versions, write the row 2 formula and fill it down; the references are relative, so each row compares its own pair.