Math function
Excel ABS function
ABS returns a number without its sign, so -45 becomes 45 and 45 stays 45. Use it when you care about how big a difference is, not which direction it went, like budget variances or tolerance checks.
=ABS(number)Arguments
number- The number or cell you want the absolute value of.
ABS examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | |
|---|---|---|---|
| 1 | Category | Budget | Actual |
| 2 | Marketing | 5000 | 5620 |
| 3 | Travel | 3200 | 2740 |
| 4 | Software | 1800 | 1795 |
| 5 | Training | 2500 | 2150 |
Result: 460
Travel came in under budget. ABS shows the size of the gap as a positive number.
Result: 620
Marketing went over by the same kind of amount, and ABS gives a positive answer here too.
Result: On target
Flag a line as on target if it is within $10 either way.
Result: 0.14 shows as 14% with Percent format
Divide by the budget to see the miss as a share. Training was off by 14%.
How to use ABS
- Click an empty cell.
- Type =ABS(
- Select a cell or type a calculation like C2-B2.
- Close the bracket and press Enter.
Common ABS mistakes
Summing variances without ABS
A +500 and a -500 cancel to 0. Wrap each difference in ABS if you want the total size of the misses.
Hiding a real direction
ABS throws away the sign. Keep a separate column if readers need to know over versus under.
Passing a range
ABS(B2:B5) spills a column of results in Excel 365 rather than one number. Wrap it in SUM or SUMPRODUCT for a total.
ABS FAQ
How do I make a negative number positive in Excel?
Use =ABS(A1). It turns -120 into 120 and leaves positive numbers unchanged.
How do I sum absolute values in Excel?
Use =SUMPRODUCT(ABS(A2:A10)). In Excel 365 =SUM(ABS(A2:A10)) also works.
How do I check if two numbers are within a tolerance?
Use =ABS(A1-B1)<=0.05, changing 0.05 to your tolerance. It returns TRUE or FALSE.
Practice ABS in these lessons
Guides that use ABS
Related functions
Updated