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

May 19, 2020 Excel tips,

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

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.

#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1