Google Sheets Absolute Percentage: Formulas and Examples

To return the absolute value of a percentage in Google Sheets, use =ABS(A2); to calculate the size of a percentage change between an old value and a new one, use =ABS((B2-A2)/A2). Format the result as a percentage with Format > Number > Percent. The right formula depends on whether you mean removing a negative sign or measuring a change without showing its direction.

What “absolute percentage” can mean

Google Sheets does not have a special function called ABSOLUTEPERCENT. The phrase usually refers to one of several related calculations, and choosing the right one matters because the formulas answer different questions.

Absolute value of a percentage: Turn a negative rate such as -12% into a positive 12%. Use the ABS function on the percentage value.
Absolute percentage change: Measure how large a change is relative to a starting value, without indicating whether the value rose or fell.
Absolute percentage difference: Compare two values symmetrically, without treating either one as the starting point.
Absolute percentage error: Measure the size of a prediction error as a proportion of the actual value.

These measures are not interchangeable. In particular, a percentage change uses a chosen baseline, while a percentage difference commonly uses the average of the two values as its denominator. The rest of this guide explains when each approach is useful.

Get the absolute value of a percentage

A percentage in a spreadsheet is stored as a numeric value. For example, 12% is stored as 0.12, and -12% is stored as -0.12. ABS removes the negative sign while leaving the magnitude unchanged. If A2 contains -12%, and you want its positive magnitude in B2, enter:

With the percentage in A2 and the result in B2, use =ABS(A2). The result is 0.12 internally, or 12% when B2 is formatted as a percentage.

This is useful when rates are signed in the source data but a report needs to show only their size. For instance, a variance column might contain -3.5% and 2.1%; applying ABS to each entry produces magnitudes of 3.5% and 2.1%. That can help compare the size of deviations, but it removes whether each result was above or below its reference. Keep the original signed data if direction matters to the analysis.

To apply the formula to further rows, put it in the first result cell and drag the small square at the cell’s lower-right corner down the column. Google Sheets adjusts the relative reference as the formula is filled, so the next row will refer to its own percentage cell.

Calculate absolute percentage change

Absolute percentage change answers: how large was the change compared with the starting value, ignoring whether it was an increase or decrease? The calculation is the absolute difference divided by the original value. Suppose A2 contains an original value of 80 and B2 contains a new value of 100. In C2, enter:

With the original value in A2, the new value in B2, and the result in C2, use =ABS((B2-A2)/A2). The calculation is ABS((100-80)/80), or 0.25, which displays as 25% when C2 is formatted as a percentage.

If the new value is 60 instead, the calculation is ABS((60-80)/80), which is also 25%. The ordinary signed percentage changes would be +25% and -25%; ABS makes both results positive. That is appropriate when comparing the magnitude of movement, but not when a reader needs to know whether the value rose or fell.

The starting value is the denominator, so the result depends on which value is treated as the baseline. Moving from 80 to 100 is a 25% increase, but moving from 100 back to 80 is a 20% decrease. Those are not equal changes because the base differs. Do not use the absolute version to conceal direction in a context where increases and decreases have different meanings.

A starting value of zero creates a division-by-zero error. There is no finite percentage change from a zero baseline using this formula. Decide how your report should handle that case rather than replacing the error with an arbitrary percentage. For example, you might label it as not applicable or report the raw change separately.

Compare two values with absolute percentage difference

If neither value is a natural starting point, an absolute percentage difference can provide a symmetric comparison. A common definition divides the absolute difference by the average of the two values. This definition is useful for comparing two measurements, quoted prices, or results when the question is how far apart they are rather than how much one changed from the other.

With the two values in A2 and B2 and the result in C2, use =ABS(A2-B2)/AVERAGE(A2,B2). For 80 and 100, the difference is 20 and the average is 90, so the result is 20/90, approximately 0.2222, or 22.22% when displayed as a percentage.

This result differs from the 25% change calculated using 80 as the baseline. Neither answer is inherently wrong: they use different denominators and answer different questions. State which definition your report uses, especially when the figure will be compared with results from another source. Other disciplines may define percentage difference differently, so check the convention required for a particular report or assignment.

If both comparison values are zero, their average is zero and the formula cannot produce a percentage. If values can be negative, the average-based measure may also be hard to interpret or may produce unexpected results when the average is close to zero. In those situations, explain the chosen method and consider reporting the absolute difference in the original units as well.

Percentage change, percentage difference, and percentage points

One common source of confusion is the difference between a percent change and a percentage-point change. A percentage-point change is simple subtraction between two rates. A percent change instead divides that difference by the original rate. Applying ABS to either calculation removes direction, but does not make the two measures equivalent.

For example, a rate that moves from 10% to 12% increases by 2 percentage points. Relative to its original level of 10%, that is a 20% increase. The absolute percentage change is therefore 20%, while the absolute difference between the rates is 2 percentage points. Calling the latter a 2% increase would be misleading.

With the starting rate in A2 and the ending rate in B2, a signed percentage-point difference is =B2-A2; format the result as a percentage if you want it displayed as a rate difference. With those same cells, the absolute percentage-point difference is =ABS(B2-A2). For the relative absolute percentage change, use =ABS((B2-A2)/A2), provided A2 is not zero. Use labels such as percentage points or relative percent change so readers can tell which number they are seeing.

Format the result as a percentage

ABS and division calculate numeric values; formatting controls how those values appear. In Google Sheets in a web browser, select the result cell or range and choose Format > Number > Percent. You can also use the percentage button in the toolbar. A calculated value of 0.25 then displays as 25%, while the underlying value remains 0.25.

Choose the number of decimal places according to the precision your audience needs. The toolbar’s increase and decrease decimal place controls can adjust what is shown. Changing displayed decimal places does not necessarily change the stored calculation. For a calculation that must be rounded, use ROUND deliberately rather than assuming the displayed value has been rounded.

With a decimal result in C2 and a desired whole-percentage result in D2, use =ROUND(C2*100,0)/100. If C2 contains 0.256, this returns 0.26, which displays as 26%. In many reports, ordinary percentage formatting is preferable because it preserves the more precise underlying value for later calculations. Rounding at each intermediate step can cause small differences when figures are combined.

Be careful when entering data. Typing 25% gives Sheets a value of 0.25, but typing 25 and then applying percentage formatting displays 2500%. If a value is already stored as a whole-number percentage, such as 25 to mean 25%, divide it by 100 before treating it as a normal percentage value. Check the formula bar or test a known entry if imported data appears unexpectedly large.

Absolute value is not an absolute cell reference

The word absolute also appears in spreadsheet discussions about cell references, but that is a separate concept. ABS is a function that returns a number’s nonnegative magnitude. Dollar signs in a reference, such as $A$2, lock a cell reference when a formula is copied; they do not calculate an absolute percentage.

For example, if a fixed target percentage is in A1 and values to calculate are in B2:B10, a formula copied down may need to keep pointing to A1. With the target in A1 and the input percentage in B2, use =ABS(B2-$A$1) to calculate an absolute difference from that fixed target; the dollar signs keep the reference to A1 in place as the formula is filled down. This is a numeric difference, not a relative percentage change. To express the difference relative to the target, the formula and denominator must be chosen explicitly.

Google Sheets and Excel both use ABS and dollar-sign cell references, and the basic formulas here work in both. The menu paths are application-specific: this article uses Google Sheets in a browser, where percentage formatting is under Format > Number. Excel has its own ribbon-based formatting controls, so do not assume the exact menu route is identical.

Common errors and how to avoid them

A percentage appears 100 times too large: Check whether the source contains 25 or 25%. A raw value of 25 formatted as a percentage displays as 2500%.
The result shows a minus sign: Check that ABS surrounds the complete expression whose direction you want to ignore. Applying it only to one part of a subtraction may not produce the intended result.
The formula returns an error: A percentage-change calculation divides by the baseline. A baseline of zero causes division by zero; blank or text entries can also disrupt calculations.
The number is not displayed with a percent sign: Apply percentage formatting to the result cell. Formatting changes display, not the underlying calculation.
The result is positive but the report seems incomplete: ABS has discarded direction. Keep a signed change in another column or use the signed formula when increases and decreases must remain distinct.

When formulas are entered in a locale with different spreadsheet conventions, Google Sheets may expect semicolons rather than commas between function arguments. If a formula using commas is rejected, check the spreadsheet’s locale and use the separator expected by that setting. The calculation itself remains the same.

Choose the formula that fits the question

Before filling a column with formulas, name the result you need. That small step prevents the common mistake of reporting a percentage difference when the reader expects a percentage change.

To turn a signed percentage into a positive magnitude, use =ABS(A2).
To measure a change relative to an original value, use =ABS((new-old)/old), with the original value as the denominator.
To compare two values symmetrically using their average as the baseline, use =ABS(A2-B2)/AVERAGE(A2,B2).
To compare two rates in percentage points, subtract one rate from the other; use ABS only if direction should be omitted.

Give the output a clear column heading, such as Absolute change from baseline or Absolute difference between rates, and include the denominator convention in a note when the meaning may not be obvious. A positive result alone cannot tell a reader whether the original data rose, fell, or simply differed.

Frequently Asked Questions

How do I make a negative percentage positive in Google Sheets?
Use ABS on the cell containing the percentage. With the value in A2, enter =ABS(A2) in the result cell, then choose Format > Number > Percent if the result is not already displayed as a percentage.

How do I calculate absolute percentage change in Google Sheets?
Subtract the original value from the new value, divide by the original value, and wrap the calculation in ABS. If the original is in A2 and the new value is in B2, use =ABS((B2-A2)/A2). The original value must not be zero.

Why does my Google Sheets percentage show 2500% instead of 25%?
The underlying value is likely 25 rather than 0.25. Percentage formatting interprets 1 as 100%, so divide a whole-number percentage by 100 before applying that format.

Is absolute percentage difference the same as percentage change?
No. Percentage change uses a selected starting value as its denominator. A common absolute percentage difference formula uses the average of the two values instead. Because the denominator differs, the results can differ even for the same pair of numbers.

Can I calculate percentage change when the original value is zero?
Not with the standard percentage-change formula, because it divides by the original value. Report the raw difference or choose a clearly explained alternative that fits the data; do not present an invented percentage as though the usual calculation were defined.

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.