ABS Function in Excel: Examples and Practical Uses

Excel’s ABS function returns the absolute value of a number: its distance from zero, without a negative sign. To try it directly in any empty cell, with no worksheet input cells required, enter =ABS(-12); the result is 12.

What the ABS Function Does

ABS is short for absolute value. On a number line, absolute value describes how far a number is from zero, regardless of which side of zero it is on. That means ABS leaves a positive number unchanged, turns a negative number into its positive counterpart, and returns zero for zero.

For example, 8 and -8 have the same absolute value because each is eight units from zero. This makes ABS useful when you care about the size of a difference or adjustment but not its direction. It does not change the original value in its source cell; it returns a result in the cell containing the formula.

A positive input stays positive: ABS of 6 is 6.
A negative input loses its minus sign: ABS of -6 is 6.
Zero remains zero.

How to Enter ABS in a Worksheet

ABS takes one input: the number whose absolute value you want. That input can be a typed number, a cell reference, or a calculation that produces a number. In Microsoft Excel for Microsoft 365 on Windows, select the result cell, type the formula beginning with an equals sign, and press Enter. The function works the same way in Excel for Mac and Excel for the web; the examples below do not depend on a platform-specific menu or command.

For a direct test, with no worksheet input cells required, =ABS(-15) returns 15. For a cell-reference example, with a negative value of -23 in A2 and the formula entered in B2, =ABS(A2) returns 23. If A2 contains 23 instead, the same formula returns 23; ABS does not force every result to change.

You can also use ABS around a calculation. With a value in A2 and another in B2, =ABS(A2-B2) returns the nonnegative distance between them. The order of the subtraction does not affect that distance: reversing the two cell references produces the same absolute result.

Practical ABS Examples

ABS is most useful when a spreadsheet needs a magnitude rather than a signed result. The right formula depends on what the numbers represent and whether direction still matters elsewhere in the analysis.

Compare a budget with actual spending: With the budget in A2 and actual spending in B2, =ABS(B2-A2) returns the size of the difference. If A2 is 500 and B2 is 460, the result is 40. It does not tell you whether spending was under or over budget; the signed subtraction does that.
Measure a delivery delay: With the scheduled time in A2 and the actual time in B2, =ABS(B2-A2) returns the time difference as a nonnegative value, provided both cells contain Excel time values. Format the result as a time or duration appropriate to the data. The sign-free result alone does not say whether the delivery was early or late.
Check whether a measurement is within a tolerance: With the expected measurement in A2, the observed measurement in B2, and the permitted difference in C2, =IF(ABS(B2-A2)<=C2,"Within tolerance","Review") displays a label based on the difference. If A2 is 10, B2 is 10.3, and C2 is 0.5, the difference is 0.3 and the formula returns “Within tolerance.”
Add the sizes of several adjustments: With signed adjustments in B2:B6, =SUMPRODUCT(ABS(B2:B6)) adds their absolute values. For -4, 3, -2, 5, and -1, the result is 15. The sum of the signed values would instead be 1, so these totals answer different questions.
Express a difference as a percentage: With a positive reference value in A2 and a comparison value in B2, =ABS(B2-A2)/A2 returns the absolute difference divided by the reference. If A2 is 80 and B2 is 84, the result is 0.05; apply Percentage formatting to display 5%. This calculation reports the size of the change relative to A2, not whether the comparison went up or down.

Absolute Difference Versus Signed Difference

A frequent source of confusion is treating an absolute difference as if it describes direction. Suppose a target is 100 and a result is 92. Subtracting the target from the result gives -8, which indicates the result is eight below target. Taking ABS of that difference gives 8, which reports the size but removes the below-target information.

Choose the signed calculation when you need to distinguish increases from decreases, such as a profit or loss, a surplus or shortfall, or a temperature rise versus a drop. Choose ABS when a positive magnitude is the point, such as measuring distance from a target or checking whether an error exceeds a limit. In reports, it can be helpful to show both: one column for the signed variance and another for its absolute size.

Also distinguish between the absolute value of a total and the total of absolute values. With signed entries in B2:B6, =ABS(SUM(B2:B6)) returns the size of their net total, while =SUMPRODUCT(ABS(B2:B6)) adds each entry’s size before totaling. For values -4 and 3, the first result is 1 and the second is 7. The second calculation does not let positive and negative entries cancel each other out.

Using ABS with Ranges and Other Functions

ABS can be nested inside other functions to support checks and summaries. The tolerance example uses IF to turn a numeric comparison into a readable label. You can also combine ABS with SUMPRODUCT to total magnitudes across a range, as shown above. In each case, ABS handles the sign while the other function controls what happens to the result.

In Microsoft 365, a range can also be passed to ABS as an array. With numeric values in A2:A5 and the formula entered in B2, =ABS(A2:A5) returns the absolute values as a spilled list in B2:B5, assuming those cells are empty. If the spill area is blocked by existing content, Excel displays a spill error until the obstruction is cleared. In older Excel versions without dynamic arrays, use a single-cell formula such as =ABS(A2) in B2 and fill it down the column instead.

When copying a formula down, a relative reference such as A2 normally changes to A3, A4, and so on. If every row must compare against one fixed value, make that reference absolute with dollar signs. For example, with a constant target in D1 and a measurement in A2, enter =ABS(A2-$D$1) in B2 and fill down. The reference to A2 adjusts by row, while $D$1 stays fixed.

Common Errors and Things to Check

ABS is simple, but the inputs and the meaning of the result still matter. If a formula does not behave as expected, check these points:

The result is positive but the direction is missing: That is expected. ABS removes the sign. Use a signed subtraction in a separate cell if you need to identify whether a value is above or below a target.
The result is an error: ABS cannot produce a numeric absolute value from an error input; errors such as #VALUE! in a referenced calculation generally pass through. Trace the referenced cells to find the original error rather than trying to hide it with ABS.
A formula returns zero unexpectedly: Check whether the referenced cell is blank or contains a formula returning zero. Also confirm that the formula points to the intended row and column.
A percentage looks like a decimal: A result of 0.05 represents 5 percent when formatted as a percentage. Use Home > Number to select Percentage, or use the percentage-format button on the Home tab.
A range formula will not spill: In Microsoft 365, make sure the cells where the array results should appear are clear. If you use an older version, calculate one row at a time and fill the formula down.

ABS does not repair bad source data or determine whether a difference is acceptable. For instance, taking ABS of a date serial number would produce a number, but that usually is not a meaningful date comparison. Make sure the input represents the kind of numeric quantity you intend to measure.

When ABS Is—and Is Not—the Right Choice

Use ABS when the direction of a number is irrelevant to the question being asked: distance from zero, size of a variance, error magnitude, or total movement regardless of sign. It is especially handy in quality checks, reconciliations, and comparisons where a tolerance is expressed as a positive amount.

Do not use it as a general way to make results “look positive.” A negative balance, loss, or shortfall may be important information, and ABS can conceal it if applied without thought. Before replacing a signed result with an absolute one, ask whether readers need to know which side of the target the value falls on. In many practical worksheets, retaining both the signed and absolute values gives a clearer, more complete view.

Frequently Asked Questions

Does ABS change the original cell value?
No. ABS returns a result in the cell containing its formula and leaves the source cell unchanged. To replace source values with results, you would need to copy and paste values over them, so keep a backup if the original signs matter.

Can the ABS function return a negative number?
No. For a valid numeric input, ABS returns a value greater than or equal to zero. That is why it is useful for magnitudes, but unsuitable when the sign communicates a meaningful surplus, deficit, gain, or loss.

How do I calculate the absolute difference between two cells?
With the values in A2 and B2, enter =ABS(A2-B2) in the result cell. It returns their distance as a nonnegative number, regardless of which value is larger.

What is the difference between ABS and the sign of a number?
ABS gives the magnitude and removes the sign; it does not report whether the original number was positive or negative. If direction matters, keep or calculate the signed value as well.

Can ABS work on a whole column or range?
In Microsoft 365, a range such as A2:A5 can be passed to ABS and the results can spill into neighboring cells if the spill area is empty. In older Excel versions, enter ABS for the first row and fill the formula down.

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.