Excel ABS Function
last modified October 5, 2026
The ABS function is a mathematical function in Excel that returns
the absolute value of a number. Absolute value represents a number's magnitude
without regard to its sign. This tutorial provides a comprehensive guide to
using the ABS function with detailed examples. You'll learn basic
syntax, practical applications, and advanced techniques to master this useful
Excel function.
ABS Function Basics
The ABS function converts negative numbers to positive numbers
while leaving positive numbers unchanged. It's particularly useful when you
need to work with magnitudes or distances regardless of direction.
| Component | Description |
|---|---|
| Function Name | ABS |
| Syntax | =ABS(number) |
| Arguments | number - The value you want the absolute value of |
| Return Value | Non-negative number |
This table breaks down the essential components of the ABS
function. It shows the function name, basic syntax format, argument
requirements, and return value characteristics.
| Argument | Meaning |
|---|---|
| number | The real number whose absolute value you want. It can be a literal, a
cell reference, or an expression that returns a number, such as
A1-B1. |
ABS takes exactly one argument, which makes it the simplest of the
math functions. Supplying more than one argument produces a "too many
arguments" error.
Basic ABS Example
This example demonstrates the simplest use of the ABS function with a single number.
=ABS(-10)
This formula converts the negative number -10 to its absolute value 10. The result will always be non-negative, regardless of the input's original sign.
ABS with Cell References
A more practical use of ABS involves referencing cells containing
values. Here's an example with cell references.
| A | B |
|---|---|
| -15 | =ABS(A1) |
The table shows a simple spreadsheet with a negative value in cell A1 and an
ABS formula in cell B1 that converts it to a positive value.
=ABS(A1)
This formula returns the absolute value of whatever number is in cell A1. If A1 contains -15, the result will be 15. If A1 contains 15, the result remains 15.
ABS with Calculations
ABS can be used with mathematical operations to ensure results are non-negative. This example shows ABS applied to a calculation.
| A | B | C |
|---|---|---|
| 100 | 150 | =ABS(A1-B1) |
This table demonstrates using ABS to ensure the difference between
two values is always expressed as a positive number, regardless of which value
is larger.
=ABS(A1-B1)
This formula calculates the absolute difference between values in A1 and B1. The result will be 50 whether A1 is larger or smaller than B1. This is useful for distance calculations.
ABS with SUM Function
ABS can be combined with other functions like SUM to ensure totals are non-negative. This example demonstrates this combination.
| A | B |
|---|---|
| -5 | |
| 10 | |
| -3 | |
| =ABS(SUM(A1:A3)) |
The table shows how ABS can wrap around a SUM
function to ensure the total is non-negative, regardless of the individual
values' signs.
=ABS(SUM(A1:A3))
This formula first sums the range A1:A3 (-5+10-3=2) then applies ABS to the result. While not necessary in this case (since the sum is already positive), it guarantees a non-negative result for any combination of values.
ABS with IF Function
ABS can be combined with IF to create conditional absolute value calculations. This example shows this powerful combination.
| A | B | C |
|---|---|---|
| -25 | Yes | =IF(B1="Yes", ABS(A1), A1) |
The table demonstrates how to conditionally apply the ABS function based on another cell's value. In this case, ABS is only applied if B1 contains "Yes".
=IF(B1="Yes", ABS(A1), A1)
This formula checks if B1 equals "Yes". If true, it returns the absolute value of A1 (25). If false, it returns A1 unchanged (-25). This allows flexible application of ABS based on conditions.
ABS with Text and Logical Values
The number argument must be numeric. Non-numeric text produces a
#VALUE! error, but Excel quietly coerces values that it can read as
numbers, such as text that looks like a number and the logical values
TRUE and FALSE.
| Input | Result |
|---|---|
| -15 (number) | 15 |
| "15" (text that looks numeric) | 15 |
| "abc" (text) | #VALUE! |
| TRUE | 1 |
| FALSE | 0 |
=ABS("15") ' 15, numeric text is coerced
=ABS("abc") ' #VALUE!, text cannot be converted
=ABS(TRUE) ' 1
Coercion applies only when the value can be converted. To guard against
unexpected errors, test the cell first with ISNUMBER:
=IF(ISNUMBER(A1), ABS(A1), "n/a").
ABS with Zero
ABS returns zero when applied to zero, maintaining mathematical consistency. This example demonstrates this behavior.
| A | B |
|---|---|
| 0 | =ABS(A1) |
The table shows that applying ABS to zero simply returns zero,
which is mathematically correct since zero has no sign to remove.
=ABS(A1)
This formula returns the absolute value of zero, which is zero. This demonstrates that ABS doesn't change zero values, as they're already non-negative.
ABS in Array Formulas
ABS can process an entire range at once, which is useful when you
want to total magnitudes rather than signed values.
=SUM(ABS(A1:A3))
If A1:A3 holds -1, 2, and -3, this returns 6. Do not confuse it with
=ABS(SUM(A1:A3)), which first adds the signed values (-2) and then
returns their absolute value (2). Use ABS(SUM(...)) for a net total
and SUM(ABS(...)) for a total magnitude.
Passing a range this way makes the formula an array formula. In Microsoft 365
and Excel 2021 it is confirmed with a plain Enter. In Excel 2019 and earlier you
must confirm it with Ctrl+Shift+Enter, which wraps the formula in
curly braces. SUMPRODUCT handles arrays natively, so it works in
every version without special entry.
=SUMPRODUCT(ABS(A1:A3))
You can also write the values as an array constant. The array {-1,2,-3,4}
becomes {1,2,3,4}, so =SUM(ABS({-1,2,-3,4})) returns 10. The
constant form is likewise an array formula and follows the same entry rules.
ABS for Tolerance Checks
Because ABS removes direction, it is the natural tool for deciding
whether two values are close enough to each other.
| A | B | C |
|---|---|---|
| Target | Measured | Check |
| 100 | 100.4 | =ABS(B2-A2)<=0.5 |
The formula measures the absolute difference between the measured and target values (0.4) and compares it with a tolerance of 0.5. It returns TRUE because the difference is within tolerance; a value outside the tolerance returns FALSE.
ABS Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Cell contains non-numeric text | #VALUE! error | Check with ISNUMBER before applying ABS |
| More than one argument | Too many arguments error | ABS accepts a single argument |
| Referenced cell is empty | 0 | Empty cells are treated as zero |
| Underlying value is already an error | The same error is passed through | ABS cannot mask #N/A, #DIV/0!, or similar errors |
The ABS function is essential for working with magnitudes,
differences, and distances in Excel. From simple sign removal to complex
conditional calculations, ABS provides reliable mathematical
operations. Remember that ABS needs a numeric value: text that
looks like a number is converted, but other text returns an error. Mastering
ABS will enhance your ability to work with signed numbers in
spreadsheets.
Author
List all Excel Formulas.