MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Sort East, Central, and West Using a Custom List


March 29, 2019 - by Bill Jelen

Excel Sort East, Central, and West Using a Custom List. Photo Credit: Luiz Centenaro at Unsplash.com

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.

Data is sorted by Region. Central is at the top, followed by East, then West.

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

Sort the same data descending, and you get West, East, Central.

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. (See Excel 2019: The Fill Handle Does Know 1, 2, 3...

Once the custom list is defined, open the Sort dialog by 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....

This screenshot shows detail of the Sort dialog. It says Sort by Region, Sort on Values. In the Order dropd-down menu, you can choose A to Z, Z to A or Custom List. Choose Custom List.

Choose the East, Central, West custom list.

This shows the Custom Lists dialog, The Bagel Flavors from ealier in the book are there, and a new list with East, Central, West in that sequence is selected.

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

Back in the Sort dialog, the Order drop-down now offers two new choices. You have the original A to Z, Z to A, then East, Central, West or the backwards West, Central, East, and then the option to choose a different Custom List.

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

Back to the report with data for three regions. It is now sorted with East first, then Central, then West, following the order of items in the Custom List.

Product lines often won't sort correctly: PTC-610, PTC-710, PTC-860, PTC-960, PTC-1100 is the desired order. But PTC-1100 always sorts first in a text sort. A custom list would solve this problem as well.

Thanks to @NeedForExcel for suggesting this tip.

Title Photo: Luiz Centenaro at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.