Sort by value ignoring the sign

An absolutely simple function to the rescue

I recently received an unusual request about sorting. They wanted to sort in ascending order but they wanted to ignore the sign of the values. So -44 would be next to 44.

There is no built-in functionality to perform this type of sort in Excel but you can use a function to convert the numbers so that you can sort like this.

The easiest function that achieves the result is the ABS function.

The ABS function is short for ABSOLUTE which is a mathematical concept which measures the distance from zero. In practice ABS removes all negatives leaving the positive version of the number. You can then use the results to achieve the requested sort sequence.

As an example I have a list below of positive and negative numbers in  column A.

Next to it I have inserted an ABS column which has the ABS function in it.

The formula for cell B2 is

`=ABS(A2)`

It is a very simple formula and it has been copied down the rest of the column.

We can now sort by column B to achieve the sort sequence that was requested – see image below.

Related Posts

Please note: I reserve the right to delete comments that are offensive or off-topic.

This site uses Akismet to reduce spam. Learn how your comment data is processed.