ABS Excel Examples: Formulas and Uses

In Excel, use =ABS(number) to return a number’s distance from zero without its negative sign: for example, =ABS(-25) returns 25. ABS is useful for making negative values positive and measuring the size of a difference, while leaving the original cell unchanged.

The ABS formula at a glance

ABS is a built-in Excel function available in Microsoft 365 for Windows. Its syntax has one required argument: a number, cell reference, or calculation that produces a number. You can type the formula directly into a cell or select Formulas > Math & Trig > ABS and enter the argument in the function dialog.

No cell layout is needed for this standalone calculation; enter the formula in any empty cell. The result is 25.

=ABS(-25)

For a cell reference, put a value such as -18.5 in A2, then enter the following in B2. The result in B2 is 18.5, while A2 remains -18.5.

=ABS(A2)

The function returns a number, not text that merely looks positive. That makes its result suitable for further arithmetic, such as adding values or comparing them with a threshold.

How ABS handles positive, negative, and zero values

Absolute value describes how far a number is from zero on the number line. The distance is never negative, so ABS returns a positive result for a negative input and leaves a positive input unchanged. Zero remains zero. In practical terms, ABS removes the negative sign when there is one; it does not reverse the digits or round the value.

A negative input such as -12 becomes 12.
A positive input such as 12 stays 12.
Zero stays 0.
A decimal such as -3.75 becomes 3.75; ABS does not change its decimal precision.

This behavior is useful when the sign is irrelevant and only the size matters. It is not appropriate when the direction of a result matters. For example, a balance of -40 and a balance of 40 have the same absolute value, but they mean different things in an account. Keep or inspect the original signed values if a negative result indicates a loss, deficit, shortfall, or direction of change.

Examples with negative numbers in a worksheet

Suppose column A contains measurements or adjustments, some positive and some negative. To show their magnitudes in column B, put a value such as -6.2 in A2 and enter this formula in B2. B2 returns 6.2. Copy the formula down to apply it to other rows; the relative reference changes automatically to A3, A4, and so on.

With the value -6.2 in A2, enter this formula in B2. It returns 6.2.

=ABS(A2)

Using a separate result column preserves the source data. That matters when the original sign is meaningful or when someone may need to audit the data later. If you replace the source values with their absolute values, the original direction is lost unless you have a backup or another copy of the data.

ABS does not round. If A2 contains -6.237, the result is 6.237. To show fewer decimal places, use the number-format controls on the Home tab; changing the displayed decimal places does not change the stored result. If you need a rounded calculation, combine ABS with a rounding function, but decide how many decimal places the task requires before doing so.

Find the size of a difference

A frequent use of ABS is to compare two numbers without caring which one is larger. Subtracting one value from another gives a signed difference: the result is positive in one direction and negative in the other. Applying ABS turns that signed result into the size of the gap.

For example, put 125 in B2 and 118 in C2. Enter the formula below in D2. It returns 7. If the two values are swapped, the signed subtraction changes direction, but ABS still returns 7.

=ABS(B2-C2)

This can help compare quoted amounts, planned and actual quantities, or two readings where only the size of the discrepancy matters. It does not identify which value is higher. If direction matters as well, retain the subtraction result in another cell or inspect the original values.

Be precise about what you subtract. A difference between a current value and a prior value can be written in either order; ABS deliberately hides that direction. For a percentage difference or percentage error, the denominator also matters, so the formula should reflect the definition you intend to use.

Add the magnitudes of a range

If a range contains both positive and negative numbers and you want the total of their magnitudes, applying ABS to each value before summing is different from taking the absolute value of the final sum. For example, the values -12, 7, -4, and 9 add to zero as signed numbers, but their magnitudes total 32.

With -12, 7, -4, and 9 in B2:B5, enter the following formula in C2. It returns 32.

=SUMPRODUCT(ABS(B2:B5))

SUMPRODUCT evaluates the absolute values in the range and adds them. This pattern is useful when positive and negative adjustments should both count toward an overall amount of movement. By contrast, =ABS(SUM(B2:B5)) would return 0 for these values, because it first adds the signed numbers and only then takes the absolute value. Choose the order that matches the question: total movement or net result.

Check whether two values are within a tolerance

Sometimes a small difference is acceptable, but a larger one should be reviewed. ABS can make a difference test work in either direction, so the comparison does not depend on which value is larger. For example, compare a measured value with a target and check whether the gap is no more than an allowed tolerance.

Put the measured value 48.7 in B2, the target 50 in C2, and the allowed tolerance 1.5 in D2. Enter this formula in E2. It returns TRUE because the difference is 1.3, which is within the 1.5 tolerance.

=ABS(B2-C2)<=D2

The result is a logical value, TRUE or FALSE, which can feed another formula or be used in filtering. A tolerance is not the same as rounding: the formula checks the actual difference against a limit. If the tolerance is a percentage rather than a fixed amount, calculate a percentage difference using an appropriate reference value and check that result against the percentage limit.

Calculate an absolute percentage error

For a simple percentage error, divide the absolute difference between an actual value and a forecast by the actual value. The actual value must not be zero, because division by zero produces an Excel error. The denominator choice is important: other measures, such as percentage change, may use a different starting value.

Put an actual value of 200 in A2 and a forecast of 190 in B2. Enter the formula in C2 and format C2 as a percentage using Home > Number. The underlying result is 0.05, displayed as 5% with the usual percentage format.

=ABS((A2-B2)/A2)

ABS makes the reported error nonnegative regardless of whether the forecast is above or below the actual value. If the actual amount can be zero, decide how that case should be represented rather than allowing a division error to go unnoticed. A percentage error is also not automatically the right measure for every comparison; use a definition suited to the data and purpose.

Common mistakes and limitations

ABS is simple, but several misunderstandings can lead to results that look plausible while answering the wrong question.

Taking ABS after summing: =ABS(SUM(B2:B5)) gives the magnitude of the net total. It does not total the magnitudes of individual entries.
Expecting the source cell to change: a formula such as =ABS(A2) returns a result in the formula cell. It does not edit A2.
Confusing formatting with calculation: number formatting can hide a minus sign visually, but it does not change the stored negative value. ABS changes the calculated result.
Using ABS when direction matters: it removes the sign information that distinguishes a gain from a loss or an increase from a decrease.
Expecting rounding: ABS preserves the numeric precision of its input. Use an explicit rounding calculation if rounding is part of the requirement.

If the referenced cell contains an error, ABS will not repair it; the error generally carries through to the result. A blank cell referenced as the argument is treated like zero in a basic arithmetic context, so ABS of a blank reference returns zero. If the data may contain text or unexpected entries, inspect the source and decide how those cases should be handled instead of assuming ABS will clean them.

ABS compared with related Excel functions

ABS is the right choice when the goal is specifically a nonnegative magnitude. SUM adds values while retaining their signs; combining SUM and ABS in different orders gives the net magnitude or the sum of individual magnitudes. ROUND changes precision, whereas ABS does not. MAX and MIN identify the largest and smallest values in a set, but they do not convert every value to a magnitude.

For comparing two cells, =ABS(B2-C2) is often clearer than a longer conditional formula that checks which value is greater and subtracts in the appropriate order. The shorter formula is also easier to maintain. However, if a report needs both the size and direction of the difference, keep the signed subtraction alongside the ABS result rather than replacing it.

Frequently Asked Questions

What does ABS mean in Excel?
ABS is the absolute-value function. It returns a number’s distance from zero, so negative inputs become positive while positive inputs and zero remain unchanged.

How do I use ABS on a cell?
If the number is in A2, enter =ABS(A2) in another cell. Excel displays the magnitude there and leaves the value in A2 alone.

How do I get the absolute difference between two cells?
Subtract one cell from the other inside ABS, such as =ABS(B2-C2). The result is the size of the difference without its direction.

How do I add absolute values in a range?
For values in B2:B5, use =SUMPRODUCT(ABS(B2:B5)) to add each value’s magnitude. This differs from taking ABS of the range’s signed sum.

Does ABS round numbers or change the original value?
No. ABS returns a calculated result without changing the source cell or rounding its value. Use number formatting to alter how many decimals are displayed, or a rounding function when the underlying calculation must be rounded.

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.