Absolute Value in Excel: ABS Function with Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

The ABS function returns the absolute value of a number, which is that number without its sign. If you need the absolute value in Excel, you write =ABS(number) and Excel converts -7 to 7 while leaving 7 as 7 [1]. The same function exists in Google Sheets with identical syntax, so anything below works in both tools.

Quick Answer

=ABS(number) returns the unsigned magnitude of a value, so =ABS(-3.5) gives 3.5 and =ABS(3.5) also gives 3.5 [1].
The number argument is required and must be a real number. It can be a literal, a cell reference, or a formula that produces a number [1].
ABS never changes the sign of a positive number or zero. It only removes a minus sign.
The result is always zero or positive, which makes ABS useful for measuring deviation, distance, and error size.
In Google Sheets the function is also ABS and takes the same single argument.

Syntax

Argument	Required?	Meaning
number	Required	The real number for which you want the absolute value [1]

The full form is:

$$=ABS(number)$$

You can pass a hard-coded value such as =ABS(-12), a cell reference such as =ABS(A2), or an expression such as =ABS(B2-C2). Excel evaluates the inner expression first, then strips the sign from the result.

How It Works

Absolute value measures distance from zero on the number line, and distance is never negative. That is why ABS(-1) and ABS(1) both return 1 [2]. The function discards direction and keeps only magnitude.

This matters in analysis because many quantities have a natural direction. A temperature can be above or below a target. A forecast error can overshoot or undershoot. A bank transaction can be a deposit or a withdrawal. When you care about how far off something is, not which way it went, ABS is the right tool.

ABS is also the standard way to make a subtraction order-independent. =ABS(B2-C2) and =ABS(C2-B2) return the same value, so you do not have to know which cell holds the larger number.

One practical detail: ABS returns a number, not text. If the argument is text that looks like a number, Excel may coerce it or may return a #VALUE! error depending on context. Feeding ABS a genuine numeric value avoids the question entirely.

Worked Example

The table below tracks temperature readings against a target. Column A holds the raw reading, column B converts it to an absolute deviation, and column C labels deviations above 2 as High.

	A	B	C
1	Temperature Reading	Absolute Deviation	Flag
2	-3.5	`=ABS(A2)` -> displays 3.5	`=IF(B2>2,"High","Normal")` -> displays High
3	2.1	`=ABS(A3)` -> displays 2.1	`=IF(B3>2,"High","Normal")` -> displays High
4	-0.8	`=ABS(A4)` -> displays 0.8	`=IF(B4>2,"High","Normal")` -> displays Normal
5	5	`=ABS(A5)` -> displays 5	`=IF(B5>2,"High","Normal")` -> displays High
6	-1.2	`=ABS(A6)` -> displays 1.2	`=IF(B6>2,"High","Normal")` -> displays Normal

Walking through column B:

B2 returns the absolute value of the reading in A2, converting -3.5 to 3.5.
B3 returns the absolute value of the reading in A3, keeping 2.1 as 2.1.
B4 returns the absolute value of the reading in A4, converting -0.8 to 0.8.
B5 returns the absolute value of the reading in A5, keeping 5.0 as 5.0.
B6 returns the absolute value of the reading in A6, converting -1.2 to 1.2.

Notice that the sign of the original reading disappears in column B. A reading of -3.5 and a reading of 3.5 would both produce a deviation of 3.5, which is exactly what you want when the question is "how far from target" instead of "above or below target."

Column C then applies a threshold. Because B2, B3 and B5 all exceed 2, they are flagged High. B4 and B6 fall at or below 2 and return Normal. The comparison in column C uses the greater-than operator, which you can review in this guide to greater than or equal to in Excel if you want to adjust the threshold logic.

More Examples

Difference between two values, regardless of order. If A2 is 10 and B2 is 4, =ABS(A2-B2) returns 6. Swap the cells and you still get 6. This is the fastest way to compute a gap without an IF check.

Distance from a target. With a target in D1 and an actual value in A2, =ABS(A2-$D$1) gives the deviation. The dollar signs lock the target reference so the formula copies down cleanly. If you are new to that notation, see absolute reference in Excel.

Rounding an absolute value. =ROUND(ABS(A2),1) strips the sign and rounds to one decimal place in a single step. The rounding rules are covered in this guide to the Excel ROUND function.

Summing total deviation. =SUMPRODUCT(ABS(A2:A10)) adds the absolute values of a whole range. Plain SUM would let positive and negative values cancel out, which hides the true size of the errors.

Percentage error. =ABS((A2-B2)/B2) gives the unsigned relative error between an actual and a predicted value. Format the result as a percentage for readability.

Conditional flagging. =IF(ABS(A2)>2,"Review","OK") routes only the large deviations for follow-up. This pattern scales well when you combine it with other logic functions from the Excel formulas cheat sheet.

Errors and How to Fix Them

Error	Typical cause	Fix
`#VALUE!`	The argument is text that Excel cannot interpret as a number	Point ABS at a numeric cell, or clean the text first
`#NAME?`	The function name is misspelled, for example `=ABSOLUTE(A2)`	Use `ABS`, which is the actual function name [1]
Unexpected 0	The referenced cell is empty, and empty cells evaluate as zero	Check that the source cell actually contains a value
Wrong magnitude	The formula references the wrong cell or a shifted range	Verify the reference, and use absolute references where the formula is copied

Common Mistakes

Writing =ABSOLUTE(A2). There is no function by that name. The correct name is ABS [1]. Excel returns #NAME? for the misspelling.
Expecting ABS to change positive numbers. ABS only removes a minus sign. A value of 5 stays 5, and a value of 0 stays 0.
Using ABS when you need the signed value. If direction matters, such as tracking whether a balance is in credit or debit, ABS destroys the information you need. Keep the original column intact.
Forgetting that ABS returns a number, not a formatted string. If you need the result displayed with a specific format, wrap it in TEXT or apply a number format to the cell. See the Excel TEXT function for format codes.
Assuming ABS fixes text entries. If a cell contains a number stored as text with a leading apostrophe, ABS may error or coerce it unpredictably. Convert the column to real numbers first.
Nesting ABS unnecessarily. =ABS(ABS(A2)) works but adds nothing. One call is enough.

Limitations

ABS removes sign information permanently for that calculation. If you later need to know whether a value was above or below a target, you must keep the original signed column somewhere in the sheet. Overwriting raw data with absolute values is a common and hard-to-reverse mistake.

ABS also does nothing about scale. A deviation of 500 and a deviation of 5 both come back as positive numbers, so ABS alone cannot tell you which one matters. Pair it with a threshold, a percentage calculation, or a ranking step before you draw conclusions. And because ABS operates on a single number, it cannot summarize a range on its own. You need an array-aware wrapper such as SUMPRODUCT to apply it across many cells at once.

Frequently Asked Questions

What is the difference between ABS and an absolute cell reference?

They are unrelated despite the shared word. ABS is a function that returns the unsigned magnitude of a number. An absolute cell reference uses dollar signs, as in $A$1, to lock a reference when a formula is copied. You can use both in the same formula, for example =ABS(A2-$D$1).

Does ABS work in Google Sheets?

Yes. Google Sheets provides the same ABS function with the same single required argument, so =ABS(-3.5) returns 3.5 in both Excel and Sheets. Formulas you build in one tool generally transfer to the other without changes.

How do I get the absolute value of a whole column?

Wrap ABS in an array-capable function. =SUMPRODUCT(ABS(A2:A10)) sums the absolute values of the range. If you want each result on its own row, put =ABS(A2) in the first row and copy it down.

Can ABS return a negative number?

No. The result is always zero or positive by definition, because absolute value is the distance from zero [2]. If you see a negative result, the formula is not actually returning ABS output, or the cell is displaying a different calculation.

How do I find the absolute difference between two numbers?

Use =ABS(A2-B2). The subtraction happens first, then ABS strips the sign, so the result is the same no matter which cell holds the larger value. This is the standard approach for gap analysis and forecast error measurement.