Do you know how to use custom sort in Excel?
I bet you know how to sort data in Excel. It is pretty easy. Most of the time an ascending sorting is what we need – letters and numbers listed in the ascending order – a to z, 1 to 100 etc. And just in case you need to sort in the reverse order, you have the Z to A sort, also called the Sort in Descending order. Between the two, most people are quite happy, thanks to Microsoft‘s intuitive sorting options.
However, there arises a time when you don’t want either the sort in Ascending order or the Descending order in Excel.
Examples where a Standard Sorting won’t work:
For example, if the departments in your organization are Finance, Marketing, Sales & Engineering. And you want the Sales department to be listed first, followed by Marketing, Engineering, and Finance being the last.
Now how would you sort the departments in this order? Ascending or descending sort is not going to work.
Do not despair however. Here is where the power of Microsoft Excel Custom sort shines.
Another scenario is the Sorting of Months – say you want to sort April, May & June, in this order. Or maybe you want to sort regions by East, West, North & South. This EWNS order also needs a custom sorting in Excel.
Or if you have a completely random order – which defies any kind of sorting. Say you want to list Oranges, then Apples, then Grapes, and finally Bananas. You can go nuts without custom sorting criteria in Excel.
Using Custom Sort in Excel
First, let’s create the custom list in Excel.
Go to Tools, Options, Custom Lists.
You can key in your list and click Add. Or you can import your list from another area of the spreadsheet, where you list the options in the sorted order.
Once you have imported the list in the correct order, you can go to Data, Sort, and then click on Options at the bottom of this popup window.
Choose your custom sorted list from the list of First Key Sort Order.
Voila! Your list is now sorted in your very own custom order.
Alternatives to Custom Sort in Excel
Of course, if you don’t want to use Custom Sort, there are other alternatives. I have often used a Lookup Table
Fruit Sorting
Oranges 1
Apples 2
Grapes 3
Bananas 4
I then use the inbuilt Lookup function of Excel called VLOOKUP function and pick the correct value, and then do an Ascending sort. This is a quick cheat trick.
But it would be tough if you did not know how to use the Lookup functions of Excel in the first place. More on this lookup function in another post.
Let me know if this neat trick help you. And if you want to learn more, join me in a Excel Training workshop in Singapore.
Till then…
Cheers,
Vinai Prakash
Founder & Master Trainer at Intellisoft Systems, in Singapore