Absolute Value in Excel: How to Use the ABS Function

To get the absolute value of a number in Excel, use the ABS function: =ABS(A2). ABS returns the number without its sign, so -5 becomes 5 and 5 stays 5.

Here’s the quick version:

1. Click the cell where you want the result.
2. Type =ABS(, click the cell with the number (for example A2) and type ).
3. Press Enter and drag the fill handle down to copy the formula to the other rows.

Below, I’ll also show you how to sum and average absolute values, how to sort by absolute value and how to turn negative numbers into positive ones for good 🤔

And do not forget to download our free sample workbook here to practice the examples below.

How to get absolute value in Excel with the ABS function

In the simplest of words, the absolute value of a number is the number without its sign.

A number may be positive (like 5) or negative (like -5). The absolute value of both numbers is 5.

The ABS function has just one argument:

=ABS(number)

The number can be a value you type, a cell reference or another formula that returns a number.

The image below has a list of numbers.

We can see the list has positive and negative values.

Let’s turn them all into absolute (positive) values 😯

1. In cell B2, write the ABS function as follows.

=ABS(A2)

The referenced cell (A2) contains the value that we want to convert into an absolute value.

2. Press Enter. Excel turns the value of cell A2 (-5) into its absolute value: a positive 5.

To find the absolute values of the other numbers too, drag the fill handle (the small square at the bottom-right corner of cell B2) down to B5. Or double-click it.

And here come the absolute values.

How to sum absolute values in Excel

The ABS function becomes really useful when you combine it with another function, like SUM.

Let’s continue with the same table as above.

We want the sum of these values, but with all of them counted as positive numbers.

1. Write the ABS function as follows.

=ABS(A2:A5)

This tells Excel to convert the values in cells A2:A5 into absolute values.

Pro Tip!

If you give ABS a range like A2:A5, it returns an array with one absolute value per cell. On its own, that array spills into the cells below. Inside SUM, it gets added up into one number 👍

Here’s the ABS part typed in cell C7 👇

2. Nest the above function in the SUM function as follows.

=SUM(ABS(A2:A5))

The formula says, ‘Turn the values of cells A2 to A5 into absolute values and then sum them up.’

3. Press Enter. Here’s the result: 5 + 6 + 9 + 8 = 28.

In Excel for Microsoft 365 and Excel 2021 or later, a normal Enter is all you need. In older versions like Excel 2016 or 2019, confirm the formula with Ctrl + Shift + Enter, or use SUMPRODUCT instead, which works without it in every version:

=SUMPRODUCT(ABS(A2:A5))

Average of absolute values: the same trick works with AVERAGE. =AVERAGE(ABS(A2:A5)) returns 7 for our list.

That is only one combination. You can use ABS inside lots of other calculations too, like MAX (the biggest number regardless of sign) or a difference between two cells: =ABS(A2-B2) gives the distance between two numbers, no matter which one is bigger.

How to sort by absolute value in Excel

Here’s a real-life use for ABS: sorting numbers by size, no matter if they are positive or negative.

The data below shows some transactions made from a bank account.

Some of them are positive (cash inflows), and others are negative (cash outflows).

We want to sort these positive and negative numbers in descending order to find the biggest transactions.

1. If you apply the simple SORT function:

=SORT(B2:B5,,-1)

The first argument is the range to sort. The second argument (sort index) is left out. The third argument is -1 for descending order.

2. The results would be as follows.

Excel sorts the data from the biggest positive number to the most negative number, like on a number line.

That puts the big $8,000 outflow at the bottom, below the smaller $2,000 outflow.

That was not what we wanted.

3. To have the transactions rightly sorted, nest the ABS function in the above formula.

=SORT(ABS(B2:B5),,-1)

The listing now changes.

This time, the amounts are sorted by size. Note that the result shows the absolute values, so the minus signs are gone.

Want to keep the signs (and the rest of the table)? Use SORTBY and let ABS decide the order:

=SORTBY(A2:B5,ABS(B2:B5),-1)

This returns the whole table, sorted from the biggest to the smallest transaction by size, with -8000 still shown as -8000.

SORT and SORTBY are available in Excel for Microsoft 365 and Excel 2021 or later. In older versions, add a helper column with =ABS(B2), sort the table by that column with Data > Sort, and delete the helper column afterwards. More on that in my guide to sorting data in Excel.

How to make negative numbers positive in Excel

The ABS formula gives you the absolute values in a new column. If you want to replace the original numbers with their absolute values:

1. Use =ABS(A2) in a helper column and fill it down, like above.
2. Select the results and press Ctrl + C.
3. Select the original numbers and go to Home > Paste > Values.
4. Delete the helper column.

Now the original cells hold plain positive numbers, with no formulas.

Only want negative numbers to look positive, without changing the value? Then you need a custom number format instead. See how to format numbers in Excel.

That’s it! What’s next?

That’s all about the ABS function of Excel. Easy, isn’t it?

ABS is simple on its own. It really shines when you combine it with other functions like SUM, SORTBY, IF or SUMIF.

Want to learn how to use these functions?

Click here to enroll for my 30-minute free email course and learn them (+ pivot tables) in no time.

Other resources

The ABS function can come to your rescue in many situations.

Some of the times, you are not really looking out for absolute values but a different visual representation of the same value.

If that’s the case, check out how to format numbers in Excel.

Frequently asked questions

What is the formula for absolute value in Excel?

=ABS(number), for example =ABS(A2) or =ABS(-7). It returns the number without its sign, so the result is always 0 or positive.

Is there a shortcut for absolute value in Excel?

No, there is no keyboard shortcut for ABS. You type the function. The F4 shortcut you may have heard of is for something else: absolute cell references like $A$1. If that’s what you’re after, see my guide to the F4 shortcut for absolute references.

What’s the difference between absolute value and an absolute reference?

The absolute value is a number without its sign (ABS(-5) = 5). An absolute reference ($A$1) is a cell reference that doesn’t change when you copy a formula. They just share the word “absolute”.

How do I sum absolute values in Excel?

Use =SUM(ABS(A2:A5)). In versions before Excel 2021, press Ctrl + Shift + Enter, or use =SUMPRODUCT(ABS(A2:A5)).

How do I sort by absolute value but keep the minus signs?

Use =SORTBY(A2:B5,ABS(B2:B5),-1). It sorts the table by the size of the numbers in column B, but returns the original values. See sort by absolute value above.

Does ABS work in Excel for Mac and Excel for the web?

Yes. ABS works the same way in Excel for Windows, Mac and the web.