Custom sorting in Excel

Sorting is a common task, but not all data conforms to the familiar ascending and descending rules. For example, months don’t sort in a meaningful way when sorted alphabetically. In this case, Excel offers a custom sort.

Before we look at a custom sort for months, let’s review the problem months presents to normal sorting practices. Below, you can see the problem. When applying an ascending sort, the list sorts alphabetically instead of sorting by month order.

If you want an alphabetic sort, this works great. I’m betting that most of the time, this won’t be the results you want. You could use an expression that returns a value equal to the order of each month and sort by its results,  but it’s unnecessary as there’s a built-in sort just for months. To apply this custom sort, do the following (in Excel 2003):

1. Select the month names. In this case, that’s A2:A13.
2. Choose Sort from the Data menu.
3. The resulting dialog box anticipates the custom sort. The Sort By control displays Month with an Ascending sort. If you click OK,  Excel will sort the selected months in alphabetic order.
4. Click the Options button at the bottom of the dialog box.
5. In the resulting dialog box, the First Key Sort Order control displays Month. Click the dropdown arrow to display four custom sort options.
   
6. Choose the last option, January, February, March, and so on. By default, a custom sort isn’t case-sensitive, but there’s an option to make it so, if you need it.
7. Click OK twice and Excel sorts the months in the familiar way you expect.

Excel 2007 and 2010 offer the same flexible custom sort, but getting there’s a bit different:

1. Click the Sort option in the Sort & Filter group. (Don’t click the A to Z or Z to A sort icons, the ones with the arrows.)
2. In the resulting Sort dialog box, click the Order control’s dropdown list and choose the appropriate custom sort.
   
3. Click OK.

When using a custom sort, the list doesn’t have to contain all of the sort elements to work. A list of just a few months will still sort by month order when applying the custom sort.