Custom Sorting in Excel

With the default sorting option you can sort the data (text, numbers, dates etc..) in ascending/ descending or alphabetical order but what if you want to have your own sorting criteria?

In that case Custom Sorting comes to rescue, this little trick can help you save countless hours of manual work

Let’s check it out

 

Assume that you have this data

A rating number and remark

 

The problem is to sort the data in this way

Note that this is not a default sorting option

 

The Solution is to create a Custom Sorting List..

Step 1 ) Write down the sorting order anywhere in the spreadsheet

Step 2 ) Open Custom Lists from Excel Options

1. Go to Excel Options (Excel 2013 and 2010 –  ALT F T,  Excel 2007 ALT F I)
2. Click on Advanced
3. Scroll down to General click on Edit Custom Lists

 

Step 3) Import the list from the spreadsheet in the Custom List Box

 

Now Let’s Sort the data..

Now that we have a custom sorting list in place, let’s get on with our usual sorting technique

1. Right click on Remarks Column and choose Custom Sort under Sort
2. Then sort the Remarks Column by the custom list that we have added
3. Bonus : Add another level of sorting on Rating in Descending Order

This will first sort the data by the custom list and then arrange the ratings in descending order for each remark

 

Custom Sorting Applications..

Just as the way we have sorted the data as per the remarks you can possibly sort the data by any of your custom criteria. Another good example of this could be to sort the dates by Indian Financial Year (Apr to Mar)

Custom Sorting works beautifully with the Filter Option and even in Pivot Tables

 

Let me know if you have any questions or if you have tried custom sorting before? Put down your thoughts in the comments below