MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

March 30, 2020 - by Bill Jelen

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

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 2020: 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

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.

Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.