Absolute Value in Excel: How the ABS Function Works

The absolute value of a number is its distance from zero, without a plus or minus sign. In Excel you get the absolute value with the ABS function: =ABS(A2) turns -25 into 25 and leaves 25 as 25. This guide shows what ABS does, then the jobs where it really helps: measuring differences, totalling variances, fixing percent change from a negative start and finding the closest match.

What does the ABS function do?#

ABS takes one number and returns it without its sign. The syntax is:

=ABS(number)

The number can be typed in, a cell reference or a formula. Here is what ABS returns for different inputs:

ABS works in every version of Excel, on Windows, Mac and the web. Note that ABS only changes the result of the formula. The original cell still holds its negative number.

How to find the difference between two numbers with ABS#

The most common use of ABS is a difference that doesn't care which number is bigger. A plain subtraction gives a negative result when the second number is larger. ABS gives the size of the gap either way.

All the examples below use this monthly budget in A1:C7:

In D2, type =C2-B2 and fill down: you get 0, 62, -37, 11, 65 and -50. The signs tell you the direction (over or under budget). In E2, type =ABS(C2-B2) and fill down: you get 0, 62, 37, 11, 65 and 50. That is how far off each line was, whatever the direction.

Keep both columns when you report variances. The signed column tells the story, and the absolute column tells you which lines need the most attention. The budget math practice lesson drills this kind of variance work.

How to sum absolute values in Excel#

To add up absolute values, put ABS inside SUMPRODUCT. SUMPRODUCT works with whole ranges, so you don't need a helper column:

=SUMPRODUCT(ABS(C2:C7-B2:B7))

The result is 225 (0 + 62 + 37 + 11 + 65 + 50). Compare it with the plain total of the differences:

The net figure of 51 hides a lot, because overspends and underspends cancel out. The absolute total shows how accurate the budget really was.

Why SUMPRODUCT and not SUM? In Microsoft 365 and Excel 2021, =SUM(ABS(C2:C7-B2:B7)) also returns 225. In older versions, SUM with a range calculation inside needs Ctrl+Shift+Enter, or it may return #VALUE! or a wrong result. SUMPRODUCT handles arrays in every version. The SUMPRODUCT function guide explains how it works.

To sum the absolute values of a single column, such as a list of positive and negative amounts in A2:A20, use =SUMPRODUCT(ABS(A2:A20)). The formula =SUMIF(A2:A20,">0")-SUMIF(A2:A20,"<0") gives the same answer without arrays.

How to fix percent change from a negative number#

The usual percent change formula is =(new-old)/old. It breaks when the old value is negative, because dividing by a negative flips the sign.

Say a small business had a profit of -200 (a loss) last year and 100 this year. That is a clear improvement:

So with cells, write:

=(C2-B2)/ABS(B2)

When the old value is positive, ABS changes nothing, so the same formula is safe for every row. Percent change from a negative start is still hard to read, so many reports show "n/a" instead for those rows. The guide to the percent change formula covers this, and the percent change lesson lets you practise it.

Other everyday uses of ABS#

Check whether two numbers match within a tolerance#

Rounding can make two totals differ by a cent. ABS lets you accept small gaps:

=IF(ABS(B2-C2)<=0.01,"Match","Check")

This says Match when the gap is a cent or less in either direction. More ways to test cells are in the guide to comparing two cells.

Find the closest value to a target#

ABS turns "closest" into "smallest gap". In Microsoft 365 or Excel 2021, this returns the item whose actual spend is closest to 150:

=XLOOKUP(MIN(ABS(C2:C7-150)),ABS(C2:C7-150),A2:A7)

The gaps are 1,050, 362, 7, 21, 115 and 100, so the result is Transport (143). The article on finding the closest match covers versions without XLOOKUP.

Take the square root of a negative number#

=SQRT(-16) returns #NUM!, because a negative number has no real square root. If you only need the size, use =SQRT(ABS(-16)), which returns 4. See square roots and powers in Excel for more.

ABS vs turning negatives into zero#

ABS and MAX(0, …) both get rid of negative results, but in different ways:

Use ABS when the size matters and the sign doesn't, such as a variance or a distance. Use MAX(0, …) when a negative result should not exist, such as a bonus or a stock level that can't go below zero. The guide to turning negative numbers into zero covers that case.

How to remove minus signs without a formula#

Sometimes you want the cells themselves to change. There are two safe ways:

1. Helper column, then paste values. Put =ABS(A2) in an empty column, fill it down, copy it, and paste it over the original as values (Windows: Ctrl+Alt+V, then V; Mac: ⌃⌘V, then choose Values). Delete the helper column.
2. Change only the display. A custom number format of 0;0 shows -50 as 50, but the cell still holds -50, so totals don't change. Use this only when the sign really doesn't matter to anyone reading.

Avoid Find and Replace on the minus sign: it also changes formulas, dates and text that contain hyphens.

Practise it#

Try the exercise on this page: it is the total absolute variance from the budget above, and the answer should be 225. Then practise budget calculations in a live spreadsheet, where you type each formula and it checks the result instantly, and move on to the percent change lesson to use ABS where it matters most.