Excel Formula for Percent Difference: Examples & Tips

Formulas & functions · Updated November 19, 2026

For two values in A2 and B2, calculate percent difference with =ABS(B2-A2)/AVERAGE(ABS(A2),ABS(B2)), then format the result as a percentage. This gives a symmetrical comparison; if you mean the increase or decrease from an earlier value, use percent change instead.

What percent difference measures

Percent difference expresses the gap between two values relative to their average size. The absolute value in the numerator means the answer is never negative: it tells you how far apart the values are, not which one is larger. Using the average magnitudes in the denominator also makes the comparison work consistently when one or both values are negative.

For two positive values, the calculation is the absolute difference divided by their average, multiplied by 100 when written as a percentage. Excel does not need the multiplication by 100 in the formula if you apply percentage formatting; its stored decimal result is converted for display. For example, a stored result of 0.2 displays as 20% when formatted as a percentage.

This measure is useful when comparing two quantities without choosing one as the starting point, such as readings from two instruments or prices from two sellers. It is not automatically the right measure for every comparison: the denominator determines what the percentage means, and a percentage can be misleading when the values have no meaningful ratio scale.

Enter the formula in Excel

Put the first numeric value in A2 and the second in B2. In C2, enter =ABS(B2-A2)/AVERAGE(ABS(A2),ABS(B2)). The formula subtracts the values, takes the absolute difference, and divides by the average of their absolute sizes. For positive values, this is the familiar difference divided by the arithmetic average.

To show the answer as a percentage, select C2 and choose Home > Number > Percent Style. You can also use the increase or decrease decimal buttons in the Number group to control how many decimal places are visible. The formula itself returns a decimal, so do not multiply it by 100 and also apply percentage formatting, or the displayed number will be 100 times too large.

For example, if A2 contains 80 and B2 contains 100, the absolute difference is 20 and the average is 90. Dividing 20 by 90 gives about 0.2222, which Excel displays as 22.22%. If the two values are reversed, the result stays 22.22%, which is what makes percent difference a symmetric measure.

Worked examples and interpretation

Consider two product quotes of $45 and $55. Their difference is $10 and their average is $50, so the percent difference is 20%. This says the quotes are 20% apart relative to their average. It does not say the second quote is 20% higher than the first; measured as an increase from $45, the increase is about 22.22%.

For readings of 12 and 15, the difference is 3 and the average is 13.5. The resulting percent difference is about 22.22%. If two measurements are 50 and 150, their difference is 100 and their average is 100, so the percent difference is 100%. These examples show why the denominator matters: the same absolute gap can represent a very different relative gap at a different scale.

Interpret the result as a magnitude, not a direction. A 22.22% result does not identify which reading is larger; compare the original cells to establish that. If you need the direction as well as size, report the values or use a signed percent-change calculation from a clearly stated baseline.

Percent difference vs. percent change

Percent difference compares two values symmetrically by dividing their gap by their average. Percent change compares a new value with a chosen old or baseline value, so changing the baseline can change the answer. These measures answer different questions and should not be swapped just because both are percentages.

With the old value in A2 and the new value in B2, use =(B2-A2)/A2 to calculate signed percent change. For 80 in A2 and 100 in B2, the result is 25%, meaning the new value is 25% higher than the old value. Reversing the comparison gives a decrease of 20%, not 25%, because the baseline is now 100.

Choose percent difference when neither value is the natural reference, such as comparing two independent measurements. Choose percent change for before-and-after questions, growth, or a change against a stated starting point. When reporting either result, identify the measure and baseline so readers know how to interpret it.

Zeros, negative values, and missing data

If both values are zero, percent difference is undefined: the average denominator is zero, so Excel returns a division-by-zero error. That is a mathematical limitation rather than a spreadsheet fault. Decide how the comparison should be reported in your context, such as as not applicable, instead of presenting a fabricated percentage.

With the first value in A2 and the second in B2, this guarded version returns #N/A when either input is missing or when both values are zero; otherwise, it calculates percent difference: =IF(COUNT(A2,B2)<2,NA(),IF(AVERAGE(ABS(A2),ABS(B2))=0,NA(),ABS(B2-A2)/AVERAGE(ABS(A2),ABS(B2)))). The COUNT check is useful when blank cells should not be treated as zero. If you would rather show a blank or a custom message, choose an appropriate output for your workbook, keeping in mind that a text result will not behave like a numeric percentage in later calculations.

Negative values can be compared by this formula using the average of their absolute magnitudes. However, percentages for quantities such as Celsius temperature can be conceptually misleading because zero is an arbitrary point on that scale. A percentage comparison is most meaningful when the values have a meaningful zero and ratios between values make sense. For measurements that cross zero, it may be clearer to report the absolute difference in the original units.

Format, rounding, and precision

Percentage formatting changes how Excel displays a number; it does not change the underlying calculation. Select the result cell and use Home > Number > Percent Style, then set the displayed decimal places to suit the precision of your inputs. For a quick estimate, a whole percentage may be enough; instrument readings or financial figures may call for more decimals, depending on how precise the source values are.

A displayed 22.22% may still have more digits stored behind the scenes. That is usually desirable because Excel can use the full-precision result in later calculations. Avoid rounding the formula only to make the cell look tidy; use number formatting for display unless a later business rule specifically requires the value itself to be rounded. Also check whether the input values were already rounded, since extra decimals in the output cannot restore precision that the source data did not contain.

Calculate percent difference down a column

For a list of comparisons, place the first set of values in A2:A20, the second set in B2:B20, and the formula in C2. Select C2 and drag the fill handle, the small square at the lower-right corner of the cell, down through the rows. Excel adjusts the relative references for each row, so the next calculation compares A3 with B3, then A4 with B4, and so on.

Apply percentage formatting to the whole result range, such as C2:C20, rather than formatting each cell individually. Before filling down, check that each row pairs the intended observations; a correct formula cannot fix misaligned records. If the data is in an Excel table, entering the formula in a calculated column generally fills it through the table, which can make the comparison easier to maintain as rows are added.

Common mistakes to avoid

  • Using the wrong denominator: Dividing by the first value calculates percent change from that baseline, not symmetrical percent difference.
  • Adding 100 twice: A formula that already multiplies by 100 should not also be formatted as a percentage. The recommended formula returns a decimal, so percentage formatting is the appropriate display step.
  • Dropping ABS: Without ABS around the difference, reversing the values can produce a negative result, which suggests direction rather than the size of the gap.
  • Ignoring zero or blank cells: Two zeros make the percentage undefined, and a blank input may be mistaken for zero by a formula. Confirm the data and decide how such rows should be handled.
  • Calling every relative comparison percent difference: If the question is how much a value rose or fell from a starting point, percent change is usually the intended measure.

Frequently Asked Questions

What is the simplest Excel formula for percent difference?
With the two values in A2 and B2, use =ABS(B2-A2)/AVERAGE(ABS(A2),ABS(B2)) and format the result as a percentage. It compares the gap with the average size of the two values.

Do I multiply the Excel formula by 100?
No, not if you apply percentage formatting. Excel displays a decimal such as 0.25 as 25%. Multiplying by 100 and then applying percentage formatting would display 2500%.

Why is my percent difference negative?
The recommended formula uses ABS around the subtraction, so its result should not be negative for valid numeric inputs. A negative result often means the formula was written as a signed subtraction or is actually calculating percent change.

Can percent difference be more than 100%?
Yes. For example, comparing zero with a positive value gives a difference equal to the positive value, while the average magnitude is half that value. The result is 200%, provided the two values are not both zero.

Should I use percent difference or percent change?
Use percent difference when the two values have equal standing and you want a symmetric comparison. Use percent change when one value is a defined starting point and you need to describe the increase or decrease from it.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.