ABS

Returns the absolute value of a number, removing any negative sign.

Math & Trigonometry
|
Excel All versions
|
Google Sheets Supported

Spreadsheet editor

Syntax

=ABS(number) Returns: Number

Arguments

Argument Required Description
number Yes The number you want the absolute value of.

About

ABS converts any number to its positive form. Pass it -25 and you get 25. Pass it 25 and you still get 25. The function strips the sign, leaving only the number itself.

This comes up a lot in variance analysis: =ABS(actual - budget) tells you how far off you are, whether you're over or under budget. It's also useful for deviation calculations, distance measurements, and any case where you only care about the gap, not the direction.

Pair ABS with SQRT for distance formulas, or combine it with SUMPRODUCT to sum absolute deviations across a range. If you need to know whether a value is positive or negative (rather than removing the sign), try SIGN instead.

Examples

Negative, zero, and positive inputs

Edit the temperature readings in column B and watch what ABS does with each case. Negative deviations become positive, positive stays positive, and zero stays zero. This is the core behavior that makes ABS useful whenever you only care about the gap, not the direction.

Spreadsheet editor

ABS removes direction info

Notice how Alice and Bob show different gaps, but column D can't tell you which one overperformed. ABS strips the sign, so you lose direction. Try changing the Sales values and see how the Status column adds back the context that ABS removes.

Spreadsheet editor

Summing all variances with SUMPRODUCT

Try editing the Actual values in column C. Cell D8 uses SUMPRODUCT with ABS to total every variance at once. Positives and negatives do not cancel each other out, so you get the true combined error across all months.

Spreadsheet editor

Watch out for

⚠

Non-numeric input returns an error

If the cell contains text or something ABS can't read as a number, you'll get a #VALUE! error.

→ Check the input first with ISNUMBER, or wrap the formula in IFERROR: =IF(ISNUMBER(A1), ABS(A1), 0)

⚠

ABS only takes a single value

=ABS(A1:A10) won't process the whole range in older Excel versions. You'll only get the result for the first cell.

→ In Excel 365, ABS spills over a range automatically. In older versions, use Ctrl+Shift+Enter to enter it as an array formula, or use SUMPRODUCT(ABS(A1:A10)) to work with a range.

⚠

Using ABS when you need SIGN

If you need to know whether a number is positive or negative (not just its size), ABS removes exactly the information you want.

→ Use SIGN instead. It returns 1 for positive, -1 for negative, and 0 for zero.

Tips & notes

In Excel 365, ABS spills results automatically when given a range. In older versions, wrap it in an array formula (Ctrl+Shift+Enter) or use it inside SUMPRODUCT for range-level calculations.

Common questions

Does ABS work on decimal numbers?

Yes. ABS works on any real number, including decimals. =ABS(-3.75) returns 3.75.

How do I get the absolute value of an entire column?

In Excel 365, =ABS(A1:A10) spills results automatically. In older versions, enter it as an array formula with Ctrl+Shift+Enter, or use SUMPRODUCT to process a range.

Can I use ABS to find the largest difference in a dataset?

Yes. Combine it with MAX: =MAX(ABS(A1:A10)) returns the largest absolute value in the range. In older Excel this requires Ctrl+Shift+Enter.