Software

Custom sorting in Excel

Excel's ascending and descending sort options are powerful tools, but they won't always sort values in a familiar and meaningful way. When this is the case, you might need a custom sort.

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
DaveUnger
DaveUnger

Another great tip (as usual). Thanks Susan.

maj37
maj37

A very good tip. Now if I can just remember it when I need it. maj

sissy sue
sissy sue

I welcome these tips. They are quite helpful.

ssharkins
ssharkins

I appreciate that very much -- always good to hear!