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
- Go to Excel Options (Excel 2013 and 2010 – ALT F T, Excel 2007 ALT F I)
- Click on Advanced
- 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
- Right click on Remarks Column and choose Custom Sort under Sort
- Then sort the Remarks Column by the custom list that we have added
- 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