Google Sheets Absolute Value of a Percentage Formula

To get the absolute value of a percentage in Google Sheets, use =ABS(A2), assuming A2 contains the percentage. The result is the same percentage without a negative sign; format the result cell as a percentage if it appears as a decimal.

What absolute value means for a percentage

Absolute value is a number’s distance from zero, expressed without a positive or negative direction. For a percentage, that means keeping its size while removing a minus sign: the absolute value of −12% is 12%, and the absolute value of 12% is also 12%. A zero percentage remains zero.

In Google Sheets, a percentage is a numeric value with percentage formatting. For example, 12% is stored as 0.12, while −12% is stored as −0.12. The ABS function acts on that underlying number. It returns 0.12 for −0.12, and the cell’s number format determines whether you see 0.12 or 12%.

This is useful when you care about the size of a rate or change but not its direction—for instance, measuring the magnitude of a variance or showing the absolute size of gains and losses. It does not change the source cell, and it does not turn a negative result into a positive change in the underlying data. It produces a separate result that you can use in calculations or reports.

Use ABS on a percentage in Google Sheets

Suppose A2 contains −12%, entered as a percentage or calculated by another formula. To return its absolute value in B2, enter the formula below. The result is 0.12 as a stored number, equivalent to 12%.

With the source percentage in A2 and the result in B2, enter:

=ABS(A2)

If B2 displays 0.12, the calculation is still correct; only the display format needs changing. Select B2, then choose Format > Number > Percent. It will display as a percentage, potentially with decimal places depending on the number format. You can adjust the displayed decimal places with the decimal-place controls in the toolbar.

Put the percentage you want to evaluate in a cell, such as A2.
Select the cell where you want the absolute value to appear, such as B2.
Enter the ABS formula using the source cell reference.
Format the result as a percentage if it is not already displayed that way.

ABS also works when the source is a formula rather than a typed value. You can place the existing calculation inside ABS, or put the existing calculation in one cell and refer to that cell. Keeping the calculation and its absolute value in separate cells can make a sheet easier to audit, especially when readers need to see both the original signed result and its magnitude.

Percentage values, storage, and display

It helps to distinguish a value from its display. Applying percentage formatting does not, by itself, multiply the underlying number by 100. Instead, the format displays the numeric value as a percentage. A value of 0.12 displays as 12%, and a value of −0.12 displays as −12%.

When you enter 12% directly in a cell, Sheets interprets it as the numeric value 0.12 with percentage formatting. ABS returns 0.12, so the result appears as 12% when the result cell uses percentage formatting. If you enter 12 without a percent sign and leave the cell as a plain number, ABS returns 12, not 0.12. That is why the source value and its format should be checked before interpreting a result.

Formatting affects how a number looks, not the mathematical meaning of ABS. If a percentage result displays as 1200%, it is often because the formula returned 12 while the result cell is formatted as a percentage. Check whether the source was entered as 12 rather than 12%, or whether an extra multiplication by 100 was included. In most ordinary percentage calculations, avoid multiplying the result by 100 if you plan to display it using percentage formatting.

Apply ABS to a column of percentages

For a list of signed percentages, enter the formula beside the first value and copy it down. For example, if A2:A5 contains −12%, 4%, −0.5%, and 0%, place the formula in B2 and fill it through B5. The results are 12%, 4%, 0.5%, and 0%, provided the result cells are formatted as percentages.

With the source values in A2:A5 and the corresponding results in B2:B5, enter this in B2, then drag or copy it down:

=ABS(A2)

As the formula is copied, its relative reference changes: the next row uses A3, then A4, and so on. You can copy a formula with the fill handle—the small square at the lower-right corner of the selected cell—or copy and paste it into the remaining result cells. Check the first and last rows to make sure the references align with the intended source values.

For a one-off calculation, using a direct cell reference is usually clearer than retyping a long expression. If the source percentage is calculated in A2, referring to that cell keeps the calculation easy to inspect. If you wrap an expression in ABS directly, make sure the parentheses include the complete expression whose sign you want to ignore.

Absolute percentage versus percentage-point difference

A common related task is finding the size of the difference between two percentages. That is not quite the same as taking the absolute value of one percentage. If one rate is 18% and another is 11%, their difference is 7 percentage points. ABS can remove the direction of that difference so the result is positive whichever rate is larger.

With the first percentage in A2 and the second in B2, enter this formula in C2 to return the magnitude of their difference:

=ABS(A2-B2)

For A2 = 18% and B2 = 11%, the subtraction produces 0.07 and ABS leaves it at 0.07. With C2 formatted as a percentage, it displays as 7%. This is a difference of 7 percentage points, not a 7% relative increase. The distinction matters: moving from 11% to 18% is a 7-point rise, while the relative increase compared with 11% is about 63.64%.

To calculate the relative difference from a chosen baseline, divide the difference by that baseline. With the current value in A2 and the baseline in B2, this formula returns the magnitude of the relative change from B2:

=ABS((A2-B2)/B2)

For A2 = 18% and B2 = 11%, the result is approximately 0.63636, or 63.64% when formatted as a percentage. This calculation requires a nonzero baseline in B2; division by zero cannot produce a meaningful relative change. Also, ABS removes direction, so this formula cannot tell you whether the current rate is higher or lower. Use the signed difference instead when direction matters.

Common mistakes and how to fix them

The result looks like a decimal. The value may be right but displayed as a plain number. Format the result cell as a percentage using Format > Number > Percent.
The result is 1200% instead of 12%. Check whether the source value is 12 rather than 12%, or whether the calculation multiplies by 100 and the result is also percentage-formatted.
A negative result is still visible. Check that the formula uses ABS and points to the intended source cell. Changing the cell’s number format alone does not remove a negative sign mathematically.
The source appears to be a percentage but does not calculate as one. It may have been imported or entered as text. Confirm that Sheets recognizes it as a number; if needed, re-enter or convert the value using a method appropriate to the source data.
The answer is positive, but the report needs to show whether the rate rose or fell. ABS intentionally hides direction. Keep the signed value as well, or use the signed difference for a directional comparison.

Be especially careful when summarizing several values. Taking the absolute value of each percentage and then averaging those values answers a different question from averaging the signed percentages first. For example, the signed values −10% and 10% average to 0%, while their absolute values average to 10%. Choose the calculation that matches what the report is meant to communicate.

When to use ABS—and when not to

Use ABS when the size of a value matters and its sign does not. Examples include reporting the magnitude of an error, showing how far an estimate is from a target, or listing the size of gains and losses without treating either direction as more important. In these cases, keep the signed source data if it may be useful later; ABS can create a display or analysis column without erasing the original direction.

Do not use ABS just to make a sheet look more reassuring or to hide a negative result that has meaning. A negative percentage change can represent a decrease, an undershoot, or a loss. If readers need to interpret performance, retain the sign or label an absolute-value column clearly as magnitude. For a comparison, also label whether the number is a percentage-point difference or a relative percentage change.

Google Sheets and Excel both provide an ABS function, and the basic calculation is the same in each. This page describes the Google Sheets interface in a web browser; in Sheets, use Format > Number > Percent to change the display. Menu placement and appearance can differ in Excel, but that does not change the underlying meaning of the ABS calculation.

Frequently Asked Questions

What formula gives the absolute value of a percentage in Google Sheets?

Use the ABS function with a reference to the percentage cell, such as =ABS(A2) when A2 contains the percentage. Format the result cell as a percentage if you want it to display with a percent sign.

Does ABS change the original percentage cell?

No. ABS returns a result in the cell containing the formula. The source remains unchanged unless you overwrite it yourself. Keeping the original value and the absolute-value result in separate columns preserves both direction and magnitude.

Why does my absolute percentage show as 0.12?

Google Sheets stores 12% as 0.12. The calculation can be correct while the result cell is formatted as a plain number. Select the result and choose Format > Number > Percent to display it as a percentage.

How do I get the absolute difference between two percentages?

Subtract one percentage from the other and wrap the subtraction in ABS, for example =ABS(A2-B2) when the two values are in A2 and B2. This gives the positive magnitude of the difference; interpret it in percentage points when the inputs are rates.

Can ABS turn a negative percentage change into a positive one?

Yes, it can return the positive magnitude, but that also removes the information that the change was negative. Keep or display the signed change when readers need to know whether a value increased or decreased.

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.