FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
March 10, 2016
 
Greetings from The Excel Addict
Hi fellow Excel Addict,

If you missed Tuesday's 'Excel in Seconds' newsletter, in it I explained 'What the "Cannot Shift Objects Off Sheet" Message Means' and how to avoid it. You can read all about it here.

Today I am going to show you how to "Sort Numbers Regardless of Their Signs". I hope you find this tip helpful.

Please feel free to share my newsletter with anyone you think could use a little Excel help.


Take care and keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)





If you missed my last newsletter, you can click here to view it online.

 

 
TheExcelAddict.com Quote of the Day

"There are no traffic jams on the extra mile."
-- Zig Ziglar --

 
If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

Sort Numbers Regardless of Their Signs

In my former career working in Finance, I found the following technique helpful, so I'm hoping that you may also.

Whenever you sort
, in descending order, a list of numbers that includes both positive and negative amounts, 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 largest negative numbers will be far apart and difficult to compare.

Sorted Variances In Table High To Low_in_Microsoft_Excel_2007_2010_2013_2016_365
For example, if you are working with variances in a report, you may want to see all large variances grouped together, whether they are positive or negative. Also, if you work with bank or other reconciliations where you need to match up offsetting debits and credits, this technique may be able to help. Or, maybe you have other situations where you need to sort numbers but ignore their signs.

Using Excel's ABS() function to sort numbers by absolute value (i.e.
the number without its sign) can help you accomplish these tasks.

The syntax of the Absolute function is =ABS(number). Number is the number or a reference to a number for which you want the absolute value.

DOWNLOAD PRACTICE FILE HERE

1) In a blank cell, in the column immediately to the right of your data (or you can insert a new column), enter the formula =ABS(E5) where E5 is the first cell in your variance column;

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.

Variances In Table Sorted High To Low By Absolute Value_in_Microsoft_Excel_2007_2010_2013_2016_365
You will now have your data sorted based on their 'absolute values' showing the largest values (positive or negative) at the top of your list.

For a bank reconciliation, this means offsetting debits and credits will be grouped together making it easier to match them up.

Sort And Match Offsetting Debits And Credits_in_Microsoft_Excel_2007_2010_2013_2016_365



Thanks for supporting this newsletter and website

FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630