It is pretty easy to sort data in Excel.
The sorting option is available on the Home Tab of Excel 2007 or Excel 2010. See image below from Excel 2010.
Just select the data, and click on the Sort & Filter Button on the Home Tab.
You can sort A to Z (Ascending order) of the selected column, which is the default Sorting option in Excel 2003, 2007 & Excel 2010 too.
But you could also sort in the Descending Order first (Sort Z to A), just by selecting this option from the Sort button on the Home Tab.
Between these two Ascending & Descending options, you can get almost all kinds of data sorted.
However, you will come across situations, when neither of these options will give you the desired result. This happens when you want to sort in a particular fashion – which is neither ascending nor descending.
What do you do then?
Here’s where Custom Sort comes into play. This hidden gem has been available in Excel since ages (a.k.a. since Excel 95, 98… days). However, most people are stumped when it comes to using it.
Say for example, that Department wise Spending are provided in an Excel file below.
You want to sort on departments, and you want them in this order listed below.
- Human Resources
- Information Technology
Clearly, this can’t be done using Ascending Sort or Descending Sort.
Custom Sort comes to the rescue.
Go to Sort & Filter button on the Home Tab of Excel 2010. Click on Custom Sort from the drop down menu. A new popup window will open.
If your data has headings, you can select the check box “My data has headers“. If you do not have column heading, then do not choose the “My Data has headers” option.
In the Sort by Column dropdown, choose Department as the option to sort on.
In the Order dropdown, choose “Custom List…“.
Click on NEW LIST, and press Add.
The cursor will move to the Empty Box in the popup. Key in the list of departments here – one per line, in the SORT order you need. Then Click OK.
Click OK on the Custom List Popup, and close all pop ups.
You will now notice that the list of Departments has been sorted, as per your specified order – which was neither ascending nor descending.
As you can see, using the Custom Sort is pretty easy. In fact, Microsoft Excel has been using this trick to sort Months, Weekdays, Quarters etc. since ages too. It’s time you learnt about this nifty technique too.
Do try it out, and let me know about your experience with Custom Sort. I’ll be glad to receive your comments and suggestions.
- ExcelChamp Video Tutorials on YouTube
- 2 Axis Charts in Excel
- How To Find Duplicates in Excel Quickly
Founder & Editor, ExcelChamp.Net
Are you facing any problem in using Excel? Any Question?
You have come to the right place. Tell us your needs. We’ll be glad to help you!