Custom Sort

July 28, 2017 - by Bill Jelen

Using Cut and Insert Cut Cells is a slow way to rearrange rows

At my last day job, we had three sales regions: East, Central, and West. The company headquarters was in the East, and so the rule was that all reports were sorted with the East region first, then Central, then West. Well, there is no way to do this with a normal sort.

Sort AZ, and you will have Central at the top.

Sort the data ZA, and you will have West at the top.

I actually went to my manager to ask if he would rename the Central region. “To what?” he asked incredulously. I replied that I didn’t care, as long as it started with F through V. Perhaps “Middle”? John shook his head no and went on with his day.

So, over and over, I would sort the report, then Ctrl+X to cut the East region records and paste them before the Central region. If only I had known this trick.

The first thing to do is to set up a custom list with the regions in the correct order: East, Central, West.

Once the custom list is defined, open the Sort dialog using the Sort icon on the Data tab. Choose to sort by Region. Open the Order dropdown. You don’t want A to Z. You don’t want Z to A. You want Custom List...

Choose the East, Central, West custom list.

Once you’ve chosen that custom list, you can either sort it East, Central, West or West, Central, East.

The result: an easy way to sort a list into a nonstandard sequence.

At that old day job, we also had a product list that refused to sort correctly. PTC-610, PTC-710, PTC-860, PTC-960, PTC-1100 was the desired order. But the PTC-1100 always fell first in a text sort. A custom list would solve this problem as well.

Thanks to @NeedForExcel for suggesting this tip.

Watch Video

Title Photo: Hans / pixabay