Percentage Difference Formula in Google Sheets

In Google Sheets, percentage difference is the absolute difference between two values divided by their average. If A2 contains the first value and B2 the second, enter =ABS(B2-A2)/AVERAGE(A2,B2) in another cell, then format that cell as a percentage. For example, 80 and 100 have a percentage difference of 22.22%.

What percentage difference measures

Percentage difference compares two values without treating either one as the starting point. It answers a question such as, “How far apart are these two measurements relative to their typical size?” The result is nonnegative because the formula uses the absolute difference. Switching the two input values therefore does not change the answer.

The denominator is the average of the two values. Dividing by that average makes the comparison symmetrical: neither value is designated as the baseline. This is useful when comparing two measurements, estimates, prices from different sources, or results from two methods and neither is naturally the original or the new value.

For ordinary nonnegative values, the calculation has three parts: subtract one value from the other, take the absolute value of the result, and divide by the average of the two values. Google Sheets stores the result as a decimal; percentage formatting displays that decimal as a percentage. Multiplying by 100 is another way to express the arithmetic, but do not multiply by 100 and also apply percentage formatting, or the displayed result will be 100 times too large.

Enter the formula step by step

Put the two numbers you want to compare in adjacent cells. For example, place the first value in A2 and the second in B2, then put the result formula in C2. With that layout, enter =ABS(B2-A2)/AVERAGE(A2,B2) in C2. The order of the two cell references does not affect the answer.

Select the cell where you want the result, such as C2.
Type the formula and press Enter. Google Sheets will calculate a decimal value.
With C2 selected, choose Format > Number > Percent.
If needed, use the decimal-place controls on the toolbar to show more or fewer digits.

For example, if A2 is 80 and B2 is 100, the absolute difference is 20 and the average is 90. Dividing 20 by 90 gives 0.2222…; percentage formatting displays approximately 22.22%. The result is not 20%, because percentage difference measures the gap relative to the average, not relative to either value alone.

Worked examples and interpretation

Consider two product quotes of 42 and 48. Their difference is 6, and their average is 45, so the percentage difference is 6 divided by 45, or about 13.33%. This tells you how large the gap is compared with the midpoint of the two quotes. It does not say which quote is preferable or whether the difference matters; those depend on the context.

Another example is comparing measurements of 250 and 260. The gap is 10 and the average is 255, giving about 3.92%. If the two measurements were entered in reverse order, the answer would still be 3.92%. That symmetry is the main reason to use percentage difference when there is no meaningful “before” and “after.”

A percentage is a relative comparison, so it can help compare gaps across scales. A difference of 10 units is substantial between 20 and 30, but small between 1,000 and 1,010. Percentage difference reflects that distinction: the first pair is about 40% apart, while the second is about 1% apart. Always keep the original units and the practical size of the gap in view as well; a percentage alone can hide whether the underlying values are measurements, dollars, or counts.

Percentage difference versus percent change

These terms are often mixed up, but the formulas answer different questions. Percentage difference treats both values equally and divides by their average. Percent change compares a new value with a designated starting value and divides by that starting value. Percent change has a direction: it can show an increase or a decrease.

For instance, moving from 80 to 100 is a 25% increase because the increase of 20 is measured against the starting value of 80. The percentage difference between 80 and 100 is about 22.22%, because the same gap is measured against their average of 90. If the movement is described in reverse, from 100 to 80, percent change is a 20% decrease, while percentage difference remains 22.22%.

Choose based on the question, not just the available formula:

Use percentage difference to compare two values when neither is the baseline.
Use percent change to describe how much a value rose or fell from a known earlier value.
Use a percentage-point difference when comparing two percentages directly. For example, moving from 20% to 25% is a difference of 5 percentage points; the relative increase is 25%.

For percent change in Google Sheets, with the original value in A2 and the new value in B2, enter =(B2-A2)/A2 and format the result as a percentage. This formula is directional and requires a nonzero starting value. It is not a substitute for the symmetrical percentage-difference calculation.

Format and display the result correctly

Google Sheets stores percentages as decimal numbers: 0.25 represents 25%. Applying Format > Number > Percent changes how the value is displayed, not the underlying result. A cell formatted as a percentage can still be used in further calculations as the decimal it represents.

Choose a sensible number of decimal places for the task. Two decimal places are often useful for comparing measurements, while a whole percentage may be easier to read in a summary. Rounding the display does not normally change the stored value, so later calculations can still use the more precise result. If a report requires a rounded value for another calculation, use a rounding formula deliberately rather than assuming the displayed digits are the stored digits.

If the cell displays a plain decimal, apply percentage formatting. If it shows something like 2,222.22%, check that the formula does not multiply by 100 as well as using percent formatting. Conversely, a formula that already multiplies the ratio by 100 should be displayed as a regular number with a percent sign added only if that is the intended presentation.

Calculate percentage differences down a column

For a table of paired values, put each pair on the same row. For example, column A can hold the first set of measurements and column B the second, with row 2 as the first pair. With that layout, enter =ABS(B2-A2)/AVERAGE(A2,B2) in C2, format C2 as a percentage, and copy the formula down for the remaining rows. Google Sheets adjusts the row references automatically when you fill down.

Check that each row really contains a matching pair. Comparing a measurement from one date with a different date, or one product with another product, can produce a valid calculation but an unhelpful conclusion. Add clear column headings such as “Method A” and “Method B,” and label the output “Percentage difference” so readers do not mistake it for percent change.

For rows that might have missing or nonnumeric inputs, a guarded formula can leave the result blank until both cells contain numbers. With the first and second values in A2 and B2, respectively, enter =IF(COUNT(A2:B2)<2,"",IFERROR(ABS(B2-A2)/AVERAGE(ABS(A2),ABS(B2)),"")). This version also uses the average of the absolute magnitudes in the denominator, which avoids a zero average when the inputs have opposite signs. It returns a blank if either input is missing or if both values are zero, rather than showing a division error.

Zero, negative, and unusual values

If both values are zero, their average is zero, so the basic formula attempts to divide by zero. There is no meaningful percentage gap relative to a zero average; decide whether your report should show a blank, a label such as “not defined,” or a separate category. The guarded formula above returns a blank for that case.

If one value is zero and the other is a positive number, the basic formula returns 200%. For example, comparing 0 and 10 gives an absolute difference of 10 and an average of 5. That result can look surprising, but it follows from using the mean as the denominator. If your question is instead “How much did a value change from zero?”, percent change is undefined because the starting value is zero.

Negative values need care. Using the ordinary average of signed values can make the denominator zero or produce an unintuitive result when values have opposite signs. In those cases, the guarded formula uses the average of the absolute magnitudes. That convention can be useful for signed measurements, but it does not make every comparison meaningful: if the values represent quantities where sign indicates fundamentally different states, explain the interpretation or use a measure designed for that data.

Common Google Sheets problems

Unexpectedly large output: Do not multiply the ratio by 100 if you also format the cell as a percentage.
#DIV/0!: The average in the denominator is zero, commonly because both inputs are zero. Decide how to represent that case rather than treating it as a normal percentage.
Formula parse error: Check the parentheses and cell references. Depending on spreadsheet locale settings, Google Sheets may expect semicolons instead of commas between function arguments; use the separator required by the sheet.
Wrong comparison: Verify that the two cells are the intended pair and use the same units. Comparing centimeters with meters without converting them first gives a misleading result.
Confusing result labels: A symmetrical percentage difference is not an increase or decrease. Label it distinctly from percent change.

Google Sheets and Excel use the same basic arithmetic for this calculation, and the formulas shown here work in both when the spreadsheet locale uses commas as argument separators. The menu path in Google Sheets is Format > Number > Percent; in Excel, percentage formatting is available from the Home tab’s Number group. The main practical difference is the application interface and, in some locales, the formula separator.

Frequently Asked Questions

What is the percentage difference formula in Google Sheets?
With the two values in A2 and B2, use =ABS(B2-A2)/AVERAGE(A2,B2), then format the result cell as a percentage. This standard version is intended for nonnegative values whose average is not zero.

Do I multiply the formula by 100?
Not if you format the result cell as a percentage. Google Sheets displays the decimal 0.2 as 20% with percent formatting. Multiplying by 100 as well would make the displayed figure 100 times larger than intended.

Can percentage difference be negative?
Not with the standard formula, because ABS makes the difference nonnegative. If you need a signed increase or decrease from a starting value, calculate percent change instead and keep the order of the values meaningful.

Why is my result 200% when one value is zero?
When one value is zero and the other is nonzero, the average is half the nonzero value, while the absolute difference is the full value. Their ratio is therefore 2, which displays as 200%. That is a consequence of the percentage-difference definition, not necessarily a spreadsheet error.

What if both values are zero?
The denominator is zero, so the standard formula cannot return a defined percentage. You can leave the result blank, show a text label, or handle zero pairs separately according to how the results will be used.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.