Excel 2020: Sort East, Central, and West Using a Custom List
March 30, 2020 - by Bill Jelen
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. (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....
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.
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.
Title Photo: Luiz Centenaro at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.