The ABS function returns the absolute value of a number: it strips away any minus sign and gives you the distance from zero. Positive numbers stay the same, negative numbers become positive, and zero stays zero.
=ABS(number)What does ABS do?
The ABS function is one of Excel's simplest math tools, but it solves a very common problem: you have numbers with minus signs and you only care about their size, not their direction. ABS stands for absolute value, which in plain terms means how far a number is from zero. So =ABS(-250) returns 250, and =ABS(250) also returns 250.
Why does this matter in an office or study setting? Negative numbers are everywhere: budget variances, temperature readings, bank withdrawals, error margins, and differences between forecast and actual figures. Often you want to compare the size of those differences without the sign getting in the way, or you want to display a shortfall as a positive amount. ABS makes that instant and reliable.
A typical situation: you are reviewing a monthly budget report and the variance column shows some departments over budget (positive) and some under (negative). To rank departments by how far off they are, regardless of direction, you wrap the variance in ABS. The result is a clean list of magnitudes you can sort, average, or flag against a tolerance.
When to use ABS
- Measuring the size of a budget variance without caring whether it is over or under.
- Converting negative temperature readings to positive values for a chart or report.
- Calculating the absolute difference between forecast and actual sales.
- Finding the distance between two numbers, such as a target price and an actual price.
- Displaying bank withdrawals or expenses as positive amounts in a summary.
- Checking whether a value is within a tolerance by comparing
ABS(difference)to a limit.
ABS syntax
=ABS(number)| Argument | Required? | What to enter |
|---|---|---|
number | Required | The number, cell reference, or calculation whose absolute value you want. For example, -42, A2, or B2-C2. |
What it returns: A non-negative number: the absolute value of the input. If the input is positive or zero, it is returned unchanged; if negative, the minus sign is removed.
ABS examples
1 Basic absolute value of a single number
This example shows the core behaviour of ABS on plain numbers. The goal is to see that the sign is removed but the magnitude is kept. The formula =ABS(A2) looks at the value in cell A2, which is -15, and returns 15. Cell A3 contains 8, so =ABS(A3) would return 8 unchanged. The target cell D2 uses the first value to demonstrate the negative-to-positive conversion.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Value | ||||
| 2 | -15 | 15 | |||
| 3 | 8 | ||||
| 4 | 0 | ||||
| 5 | -3.5 | ||||
| 6 | 22 | ||||
| 7 | -100 | ||||
| 8 | 7 | ||||
| 9 |
2 Absolute difference between forecast and actual
Here you compare forecast sales with actual sales and want the size of the gap, not its direction. The formula =ABS(B2-C2) subtracts the actual figure in C2 from the forecast in B2, then ABS removes any minus sign. For the first row, 1200 minus 1150 is 50, so the result is 50. If actual had been higher, the subtraction would be negative and ABS would still return a positive gap.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Forecast | Actual | |||
| 2 | Widget | 1200 | 1150 | 50 | ||
| 3 | Gadget | 800 | 860 | |||
| 4 | Doohickey | 450 | 430 | |||
| 5 | Gizmo | 1500 | 1500 | |||
| 6 | Sprocket | 600 | 540 | |||
| 7 | Cog | 300 | 320 | |||
| 8 | Lever | 950 | 900 | |||
| 9 |
3 Budget variance as a positive magnitude
In budget reviews, a negative variance means under budget and a positive one means over budget. Sometimes you only want to know how far off each department is. The formula =ABS(C2-B2) subtracts the budget in B2 from the actual spend in C2, then ABS converts any negative result to positive. For Marketing, 5200 minus 5000 is 200, so the answer is 200. For Operations, 4800 minus 5000 is -200, and ABS returns 200 as well.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Department | Budget | Actual | |||
| 2 | Marketing | 5000 | 5200 | 200 | ||
| 3 | Operations | 5000 | 4800 | |||
| 4 | Sales | 7500 | 7900 | |||
| 5 | HR | 3000 | 2950 | |||
| 6 | IT | 6200 | 6100 | |||
| 7 | Finance | 4100 | 4300 | |||
| 8 | Admin | 2200 | 2200 | |||
| 9 |
4 Temperature readings without minus signs
Weather and lab data often include negative temperatures, which can complicate charts or simple comparisons. The formula =ABS(B2) takes the reading in B2 and returns its magnitude. For the first day, -5 becomes 5. This does not mean the temperature was 5 degrees; it means the reading was 5 degrees away from zero. Use this when you need the size of the deviation rather than the signed value.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Day | Temp C | |||
| 2 | Mon | -5 | 5 | ||
| 3 | Tue | -2 | |||
| 4 | Wed | 3 | |||
| 5 | Thu | 7 | |||
| 6 | Fri | -1 | |||
| 7 | Sat | 0 | |||
| 8 | Sun | 4 | |||
| 9 |
5 Checking a price difference against a tolerance
This example combines ABS with a comparison to flag items whose actual price is too far from the target price. The formula =ABS(C2-B2) calculates the absolute gap between target and actual. For the first item, 100 minus 105 is -5, and ABS returns 5. You could then wrap this in an IF to show Pass or Review, but the ABS part alone gives the size of the difference you need.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Target Price | Actual Price | |||
| 2 | Item A | 100 | 105 | 5 | ||
| 3 | Item B | 250 | 240 | |||
| 4 | Item C | 75 | 75 | |||
| 5 | Item D | 320 | 335 | |||
| 6 | Item E | 180 | 170 | |||
| 7 | Item F | 95 | 99 | |||
| 8 | Item G | 410 | 400 | |||
| 9 |
Common ABS errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The argument is text that cannot be interpreted as a number, such as =ABS("abc"). | Make sure the cell contains a number or a formula that returns a number. Remove stray spaces or non-numeric characters. |
| #NAME? | The function name is misspelled, for example =ABSS(A2) or =ABS( without a closing parenthesis. | Type =ABS( and let Excel suggest the function, then complete the formula with a closing parenthesis. |
| #REF! | The cell reference points to a deleted cell or an invalid range. | Check the referenced cells still exist, or use the Undo command to restore deleted cells. |
| #N/A | The referenced cell contains #N/A from another formula, and ABS cannot evaluate an error value. | Resolve the source error first, or wrap the source in IFERROR before passing it to ABS. |
Tips for using ABS
- ABS works on the result of any calculation, so
=ABS(B2-C2)is often more useful than applying ABS to a single cell. - Combine ABS with IF to create tolerance checks:
=IF(ABS(B2-C2)<=5,"OK","Review"). - ABS always returns a non-negative number, so it is safe to use in averages and totals where signs would cancel out.
- If you need the absolute value of an entire range, enter
=ABS(A2)and copy it down rather than retyping the formula. - Remember that ABS removes the sign but does not round; use ROUND with ABS if you need a fixed number of decimal places.
- For dates, subtract the earlier date from the later one to avoid negative day counts, or use ABS on the difference.
Frequently asked questions
What does the ABS function do in Excel?
ABS returns the absolute value of a number, which means it removes any minus sign and gives you the distance from zero. Positive numbers and zero are returned unchanged.
Can ABS handle cell references and formulas?
Yes. You can pass a cell reference such as A2, a calculation such as B2-C2, or a literal number such as -10. ABS evaluates the argument first, then returns the non-negative result.
Does ABS work with dates?
ABS works on the underlying serial number of a date, so =ABS(DATE(2024,1,1)-DATE(2024,1,10)) returns 9. For normal date differences, it is usually simpler to subtract the earlier date from the later one.
Why does ABS return a decimal when I expected a whole number?
ABS does not round. If the input has decimals, the output keeps them. Wrap the formula in ROUND, for example =ROUND(ABS(A2),0), if you need a whole number.
Is ABS the same as removing the minus sign with find and replace?
The result looks similar, but ABS is a live formula. If the source value changes, the ABS result updates automatically, whereas a manual edit does not.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. ABS reference