Excel Percentage Difference Formula: How to Calculate

For the percentage difference between two nonnegative values in Excel, use =ABS(B2-A2)/AVERAGE(A2,B2), then format the result as a percentage. This symmetric calculation compares the absolute gap with the average of the two values; if you mean the percentage increase or decrease from an earlier value, use a different formula.

The percentage difference formula

Suppose cell A2 contains the first value and B2 contains the second. Enter the formula below in C2 to calculate their percentage difference:

=ABS(B2-A2)/AVERAGE(A2,B2)

ABS makes the result nonnegative, so swapping the two inputs does not change the answer. AVERAGE supplies the reference amount: the midpoint of the two values. Excel calculates a decimal ratio; for example, 0.2 represents 20%. Applying percentage formatting makes that result easier to read.

This definition is useful when neither value is inherently the starting point. It is often used to compare two measurements, prices, estimates, or results when the question is how far apart they are relative to their typical size. The formula assumes the values are nonnegative and that their average is not zero. If the values represent a before-and-after change, a baseline-based percentage change is usually more appropriate.

Percentage difference versus percentage change

These terms sound alike, but they answer different questions. Percentage difference treats the two values equally and reports a nonnegative gap relative to their average. Percentage change treats one value as the starting point and measures movement from that baseline, so its answer has a direction: positive for an increase and negative for a decrease.

For a change from an old value in A2 to a new value in B2, enter this in C2:

=(B2-A2)/A2

For example, if A2 is 80 and B2 is 100, the percentage difference is 20 divided by an average of 90, or about 22.22%. The percentage change is 20 divided by the original 80, or 25%. Both are correct for their respective questions, but they are not interchangeable.

Reversing the order shows why the distinction matters. The percentage difference between 80 and 100 remains about 22.22%, because the gap and average stay the same. The percentage change from 100 down to 80 is negative 20%, because the original value is now 100. When writing a report, label the measure clearly and state which value is the baseline if you are reporting a change.

Set up the worksheet

A simple layout keeps the calculation understandable and makes it easy to check. Put the two values side by side, add a descriptive header for the result, and use a formula that refers to the cells rather than typing the numbers into the formula itself.

Enter the first value in A2 and the second value in B2.
Label C1 Percentage difference, or use a more specific heading such as Difference between estimates.
With the values in A2 and B2 and the result intended for C2, enter =ABS(B2-A2)/AVERAGE(A2,B2) in C2.
Check that both input cells contain numbers, not text that only looks like a number.
For additional pairs in rows 3, 4, and onward, copy the formula down so each row compares its own A and B values.

Excel adjusts ordinary cell references as a formula is copied down: the C3 formula will refer to A3 and B3. You generally do not need dollar signs for this row-by-row calculation. If the two values are stored in different columns in your own sheet, change the references to match their locations.

Worked examples and interpretation

With values of 80 and 100, the absolute difference is 20 and the average is 90. Dividing 20 by 90 gives 0.2222 repeating, which displays as 22.22% when rounded to two decimal places. It does not mean the values differ by 22.22 units; it means the gap is 22.22% of their average.

Consider values of 40 and 60. Their gap is 20 and their average is 50, so the percentage difference is 40%. The percentage change from 40 to 60, by contrast, is 50%, because the baseline is 40. For a close pair such as 98 and 100, the gap is 2 and the average is 99, giving roughly 2.02%. Examples like these show why the chosen denominator matters, especially when comparing results across groups of different sizes.

A percentage difference of 0% means the two inputs are equal. A result of 100% means the gap equals the average of the inputs; for nonnegative values, that occurs when one value is zero and the other is positive. A result can exceed 100% when the two nonnegative values are very far apart. That is not automatically an error: it follows from measuring the difference against the average rather than against the smaller value.

Format the result as a percentage

The formula returns a ratio, not a whole-number percentage. In Excel for Microsoft 365 on Windows, select the result cell or range and choose Home > Number > Percent Style. You can also use the percentage-style button in the Number group. Excel displays 0.2222 as approximately 22%, depending on the number format.

To show more precision, use Increase Decimal in the Number group until the display has the number of decimal places you want. Two decimal places are useful when small differences matter; whole percentages may be clearer in a high-level summary. Formatting changes how the value appears, not the underlying calculation, so sorting and further calculations still use the ratio stored in the cell.

Do not multiply the formula by 100 if you plan to apply percentage formatting. Doing both would turn a ratio of 0.2222 into 22.22 and then display it as 2,222%. If you need the result as a numeric value such as 22.22 for a system that expects percentage points rather than a percentage-formatted ratio, multiplying by 100 can be appropriate, but label that column so the scale is unambiguous.

Zero values and negative numbers

If both inputs are zero, their average is zero and the main formula tries to divide by zero, producing Excel’s #DIV/0! error. There is no meaningful percentage difference under this formula because the reference average is zero. If the worksheet should flag that case explicitly, and A2 and B2 contain nonnegative values, use this guarded version in C2:

=IF(AVERAGE(A2,B2)=0,NA(),ABS(B2-A2)/AVERAGE(A2,B2))

It returns #N/A when the average is zero, rather than a misleading percentage. The error can make missing or undefined comparisons visible in charts and reviews. If you prefer a blank result, a formula can return an empty text value instead, but a blank may be mistaken for a missing calculation. Choose the behavior that suits the workbook and document it for other users.

A pair containing one zero and one positive value does not cause division by zero: the average is positive, and the percentage difference is 200% when the values are 0 and 100. That may surprise someone expecting 100%, but it is the result of dividing the 100-unit gap by the average of 50. For a percentage change from zero, the baseline formula instead divides by zero, so a percentage increase from zero is undefined by that calculation.

Use caution with negative numbers. The average of signed values can be zero or negative, which can make the usual percentage-difference result undefined or unintuitive. For signed measurements, first decide what should count as the reference scale. Some analyses use the average of the absolute magnitudes; others use a defined baseline and a directional percentage change. The right convention depends on the subject, so do not silently apply the nonnegative-value formula to signed data.

Compare many pairs and summarize results

For a table with many comparisons, place each pair on its own row and fill the formula down. If A2:A100 contains first measurements and B2:B100 contains second measurements, put the calculation in C2 and copy it through C100. Review a few rows by hand to confirm that the references have shifted correctly and that every row compares like with like.

If you need an overall percentage difference for two groups, decide what overall means before averaging the row-level percentages. The simple average of individual percentages gives every row equal weight, even if the original values vary greatly. A ratio calculated from group-level totals weights the observations differently and may be more suitable when totals are meaningful. For example, comparing total units sold across two periods is not the same analysis as averaging the percentage difference for each product.

Keep the source values and the resulting percentage in separate columns. That makes it possible to audit the arithmetic, change the display precision, or recalculate using a different definition later. For a chart, use a clear axis title and percentage number format. A chart can otherwise make a ratio look like a raw quantity, particularly when results are mixed with counts or currency values.

Common mistakes to avoid

Using the wrong denominator: Dividing by the first value calculates a change from that value, not the symmetric percentage difference.
Dropping ABS: Without ABS, reversing the inputs changes the sign and the result no longer represents a nonnegative difference.
Multiplying by 100 and formatting as a percentage: That applies the conversion twice and inflates the displayed value.
Ignoring the units: Both cells should measure comparable things in compatible units. Comparing 2 meters with 100 centimeters without converting units produces a meaningless difference.
Comparing different time periods or populations: A mathematically correct formula cannot fix mismatched definitions, such as comparing a weekly total with a monthly average.
Rounding the inputs first: Keep the original precision in the cells and round the displayed result unless the analysis specifically requires rounded input data.

Also check whether a percentage is the best way to communicate the result. When values are near zero, small absolute changes can look very large as percentages. Reporting the absolute gap alongside the percentage can give readers useful context.

Frequently Asked Questions

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

How do I calculate percentage change instead?
With the old value in A2 and the new value in B2, use =(B2-A2)/A2. Format the result as a percentage. A positive result indicates an increase and a negative result indicates a decrease; the old value cannot be zero for this calculation.

Should percentage difference be positive or negative?
The symmetric percentage difference is normally nonnegative because it describes the size of a gap, not its direction. Use percentage change when you need to show whether a value rose or fell from a stated baseline.

Why does Excel show #DIV/0!?
The two inputs may both be zero, making their average zero. The formula cannot divide by that denominator. Decide whether the comparison should be marked not applicable, left blank, or handled under another definition rather than treating it as a normal percentage.

Why is my result 2,222% instead of 22.22%?
You may have multiplied the ratio by 100 and then applied percentage formatting. Remove the extra multiplication, or keep the multiplied number in a column explicitly intended to store percentage points without percentage formatting.

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.