Excel ABS Function (Absolute Value) – Examples & Practice
Practice the Excel ABS function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Return the absolute value of cell B3 so negatives become positive.
Formula Syntax
=ABS(number)
- number: The real number whose absolute value you want.
What it does
ABS returns the absolute value of a number, removing its sign. It is helpful for distances, tolerances, and magnitude-only analytics.
Excel ABS Function Examples
Remove a negative sign
=ABS(B3)
Turns -12 into 12.
Distance between two values
=ABS(A2-B2)
Useful for unsigned differences.
Unsigned budget gap
=ABS(B2-C2)
Shows how far actual is from budget without a sign that implies over vs under.
numeric-practice-sheet.xlsx
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Input 1 | 120 |
| 3 | Input 2 | -45 |
| 4 | Input 3 | 560.25 |
| 5 | Input 4 | 225.8 |
| 6 | Input 5 | 90 |
| 7 | Output |
Input Formula
Need Help?
Tips
- Pair ABS with differences for tolerance bands.
- Keep raw signed values in a helper column when auditing.
- Do not confuse ABS with removing formatting minus signs in text.
ABS Function Use Cases
- Compute distance or variance without direction
- Clean deviations for tolerance checks
- Prepare magnitudes before ranking
- Simplify accounting reconciliations
- Feed risk models that care about size, not sign
Common mistakes - ABS function not working
- Expecting ABS to fix text values
- Using ABS when signed direction matters
- Forgetting ABS around A-B differences in tolerance tests
- Mixing ABS with percentages without clear intent
- Assuming ABS rounds numbers (use ROUND instead)
FAQ
Does ABS change positive numbers?
No. ABS leaves non-negative numbers unchanged.
Can ABS remove the minus from a text number?
ABS expects a numeric value. Clean text numbers first or use VALUE.
Does ABS work on errors?
If the input is an error, ABS returns an error.
Is ABS the same as removing sign in math?
Yes. It returns the magnitude of a number.
Can I use ABS on a range?
Use an array formula or apply ABS per cell; SUMPRODUCT patterns can help aggregate magnitudes.
Comparison
| Function | Effect |
|---|---|
| ABS | Magnitude |
| SIGN | Returns -1, 0, or 1 |
| INT | Rounds down to integer |
Example
=ABS(-5) returns 5.
Advanced examples
Sum of absolute deviations
Combine ABS with SUM or SUMPRODUCT to total unsigned gaps:
=SUMPRODUCT(ABS(A2:A6-B2:B6))
ABS inside tolerance checks
=IF(ABS(A2-B2)<=D1, "Within tolerance", "Review")
Compares the gap to a threshold in D1—common for QC and reconciliation workflows.
Related functions
ROUND and INT reshape numbers in different ways: ROUND follows digits and mode rules, while INT always drops the fractional part toward negative infinity—compare them before you pick one.
Use SUM after ABS when you need total distance, total error magnitude, or any “size without direction” subtotal across a range.