How to use the ABS Function in Excel

ABS Function

A lesser know function in Excel is ABS, which is short for absolute. This function simply converts any negative number into a positive one.

To use it we simply type in the function and refernce a cell which contains a number. If the number is positive then this number is displayed. If it is negative then the number gets converted into a positive number. If the result is not a number then we get an error. For example the number 7 remains as the number 7. Minus 7 gets made into a positive number 7 and entering text produces an error.

We can also use a formula in the ABS function. In this example we are simply adding one to a number entered into the highligted cell before performing the ABS function. Therefore if we entered minus 3, the formula would add one to this giving us minus two, before performing the ABS function giving a positive number two.


Lets now look at some scenarios in which it might be useful to use ABS.

When comparing the difference between two numbers, for example measuring a distance between two points, it may make more sense if the returned result is positive. To demonstrate, lets create a small list of measurements. The first column is the starting measurment, the second is the end measurment and the third is the difference between the two. we work this out by simply taking the second number away from the first. The combined length of all the measurments is displayed at the bottom of the list. You can see that, whilst this is mathamatically correct, because one of the pairs of numbers we used was entered with the smaller number first, for this specific row this results in a negative difference and subsequently makes the total length shorther than it should be.

To rectify this we can use the ABS function which I will put in a new column at the end of the list. Now when we create a total, we see that it is showing the correct total value by transforming the negative distance into a positive one.

A less common use for ABS would be to use it to check for a positive or negative number. We can currently do this using a simple formula which checks if a number is less than 0 but using ABS to do this instead has one benefit.

In this example the first formula, which does not use ABS, checks the highlighted cell. If the value in this cell is less than 0 then it reports "negative", otherwise it will report "positive". Below this we have a formula which uses ABS. We check the entered number against the ABS of the number. If they are the same, the number is positive, otherwise we report "Negative". This works because ABS only changes negative numbers so all positive numbers are the same when we use ABS on them.

Entering a positive or negative number produces the same result in both cases as expected. However entering anything other than a number (for example some text) results in an error for the ABS function but with the non-ABS function, it still reports as positive. The benefit of having ABS reporting with an error is that it provides a simple error check at this point for someone entering an incorrect value. With the non-ABS function you may not realise that an incorrect value has been used and so continue without fixing the problem.


Take a look at our video on how to use the ABS Function in Excel