MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Custom Sort


July 28, 2017 - by Bill Jelen

Custom Sort

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.

Sample Data Set
Sample Data Set

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

Sort A-Z
Sort A-Z

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

Sort Z-A
Sort Z-A

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.

Illustration: Michelle Routt
Illustration: Michelle Routt

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...

Custom List Sort Order
Custom List Sort Order

Choose the East, Central, West custom list.

Select Custom List
Select Custom List

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

Sort Order
Sort Order

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

The Result
The Result

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

  • Using Cut and Insert Cut Cells is a slow way to rearrange rows
  • Podcast 1978 introduced Custom List for the Fill Handle
  • Sorting data into a special sequence is another benefit of custom lists
  • Type the list into the correct sequence
  • File, Options, Advanced, 83%, Edit Custom Lists, Import
  • Use the Sort dialog
  • In Sort Order, open the dropdown and choose Custom List
  • Interesting (?) that you can sort the list reverse after choosing
  • Thanks to @NeedForExcel for suggesting this tip

Download File

Download the sample file here: Podcast1992.xlsx

Title Photo: Hans / pixabay


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.