# ABS function in Excel

> ABS returns a number without its sign, so -250 becomes 250 and 250 stays 250.

Source: https://spreadsheetpractice.com/functions/abs · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** ABS returns a number without its sign, so -250 becomes 250 and 250 stays 250.
- **Syntax:** `=ABS(number)`
- **Most common use:** `=ABS(C2-B2)` returns 65 (Under forecast by 65, shown as a size: 65)
- **Available in:** Every current version
- **Category:** [Math functions](https://spreadsheetpractice.com/functions/category/math)
- **Returns:** The number as a positive value (zero stays zero).
- **Google Sheets:** Works the same in Google Sheets.

## What it does

ABS returns a number without its sign, so -250 becomes 250 and 250 stays 250. Use it when only the size of a difference matters, such as how far a forecast missed, whichever direction it missed in.

## Syntax

`=ABS(number)`

| Argument | Required? | What it is |
|---|---|---|
| `number` | Required | The number or calculation whose size you want. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | `=ABS(C2-B2)` | 65 | Under forecast by 65, shown as a size: 65 |
| D3 | `=ABS(C3-B3)` | 62 | Over forecast also comes out positive: 62 |
| D5 | `=MAX(ABS(C2:C4-B2:B4))` | 65 | The biggest miss across all stores in one formula |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D |
|---|---|---|---|---|
| 1 | Store | Forecast | Actual | Miss |
| 2 | North | 1200 | 1135 | 65 |
| 3 | South | 980 | 1042 | 62 |
| 4 | East | 1500 | 1488 |  |
| 5 |  |  |  | 65 |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| The direction is gone | ABS throws the sign away, so you can't tell over from under. Keep the plain difference in another column, or use SIGN to get the direction. |
| #VALUE! with =SUM(ABS(A2:A10)) | In Excel 2019 and earlier this array formula needs Ctrl+Shift+Enter. =SUMPRODUCT(ABS(A2:A10)) works in every version without it. |
| #VALUE! | The cell holds text, such as a dash typed for zero. Replace it with 0 or wrap the reference in IFERROR. |

## Tips

- Average absolute error: =SUMPRODUCT(ABS(C2:C13-B2:B13))/COUNT(B2:B13).

## Related functions

- [SIGN](https://spreadsheetpractice.com/functions/sign): SIGN tells you which side of zero a number is on: 1 for positive, -1 for negative and 0 for zero.
- [SUMPRODUCT](https://spreadsheetpractice.com/functions/sumproduct): SUMPRODUCT multiplies matching items in two or more ranges and adds the results.
- [MAX](https://spreadsheetpractice.com/functions/max): MAX returns the largest number from a list of values or ranges: the top sale, the latest date, the highest temperature.

## Questions

### How do I make negative numbers positive in Excel?

Use =ABS(A2). To change the values in place, multiply by -1 with Paste Special, but ABS keeps the original data intact.

### How do I sum absolute values in Excel?

Use =SUMPRODUCT(ABS(A2:A20)). It works in every version without Ctrl+Shift+Enter.

### How do I find the difference between two numbers regardless of order?

Use =ABS(A2-B2). It returns the same result whichever number is bigger.

## Practice

A graded ABS exercise ("Forecast error") is on [the ABS page](https://spreadsheetpractice.com/functions/abs). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
