The weekend excel tip

Sorting absolute values

I use this a lot, especially when i have to do account reconciliations

Whenever you sort a list of numbers that includes both positive and negative amounts, assuming that you sort in descending order, the largest positive numbers go to the top and the largest negative numbers go to the bottom. On a short list, this may not be a problem, but on a long list your largest positive and negative numbers will be far apart.
 

 
You may find that sometimes you need your data sorted but you want to ignore the sign. For example, if you are working with variances, you may want to see all large variances grouped together, whether they are positive or negative. 

You can accomplish this by using the ABS (Absolute) function. 

1) In a blank cell, in the column immediately to the right of your data, enter the formula =ABS(D5);
2) Replace D5 with the reference to your first amount cell;
3) Copy that formula down the column to the bottom of your list;
4) Now sort your data in descending order based on this column. 
 
 

 
You will now have your data sorted based on absolute values showing your most significant items (positive or negative) toward the top of your list. You can now clear or delete the column of ABS formulas. 

Click here to read all our earlier excel tips

In case you have a great excel tip or question, pls send the same to us