Software optimize

How do I create a custom sort in Excel?

Learn how to create a custom sort for unique sorting needs that alphabetic and numeric sorts just can't handle.
In Custom sorting in Excel, I showed you a custom sort order for months. Excel has four of these built-in custom sorts. Unfortunately, they won't handle more unique sorting needs, but you can create new ones when necessary. For example, you might identify temperatures by Cold, Warm, and Hot. An alphabetic sort would result in Cold, Hot, Warm, or Warm, Hot, Cold and there's no built-in custom sort for this particular list.

To create a custom sort for the three elements, Cold, Hot, and Warm, in that order, do the following in Excel Excel 2010:

  1. Click the File tab and select Options (under Help).
  2. Select Advanced in the left pane.
  3. In the General section, click Edit Custom Lists.
  4. In the List Entries control, enter each element in the sort order. Press Enter after each entry. (If the list is long or already exists as natural data, you can specify the cell reference and click Import).
  5. Click Add and Excel will transfer the new custom sort to the Custom Lists control (on the left).
  6. Click OK twice.

If you're using Excel 2007, do the following:

  1. Click the Office button and click Excel Options.
  2. Click Popular in the left pane (the default).
  3. In the Top Options For Working With Excel section, click the Edit Custom Lists button.
  4. Continue with step 4 above.

To apply the custom sort, select the list you want to sort, click the Data tab, and then click Sort in the Sort & Filter group. Click the Order dropdown, choose Custom Lists, and select the Cold, Warm, Hot list.

In Excel 2003, you can create the same custom sort as follows:

  1. Choose Options from the Tools menu.
  2. Click the Custom Lists tab.
  3. Continue with step 4 for Excel 2010.

To apply the custom sort in Excel 2003, select the list, and choose Sort from the Data menu. Click the Options button, choose the custom sort from the First Key Sort Order dropdown, and click OK twice.

TechRepublic's Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!

Custom lists are stored in the Windows Registry so they're available for all workbooks, not just the one you're working in when you create the list. However, that means the list won't be on other computers. If you're working with the same workbook, the list itself is available. Open the Custom Lists dialog box, select the list in the Custom Lists control (on the left of the last figure above) and click Add. Doing so will add the custom sort to the local system's Registry.

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.

3 comments
simchuck
simchuck

Is there any way to specify a wildcard in the custom sort order? I am trying to sort based on a column which contains a coded item number (e.g., Apple-1, Apple-2, Orange-1, Grape-1, ...), where the item number includes a category followed by a numeric value. I want to specify the order of the categories (not alphabetic), and sort so that all categories are grouped and then with consecutive item numbering. For example, sorting the following list with categories ordered by Apple, Orange, Grape... Grape-3 Apple-2 Orange-1 Apple-1 Apple-4 Grape-2 Orange-3 Orange-2 ... when sorted would result in... Apple-1 Apple-2 Apple-4 Orange-1 Orange-2 Orange-3 Grape-2 Grape-3 One further complication -- my list also contains some blanks (""), which I would like to sort to the bottom of the list. I know I can do this by splitting up the key field into multiple columns and using multiple corresponding keys, but I am specifically looking for an option to use some kind of custom list with a wildcard, e.g., "Apple*,Orange*,Grape*,"""""

spiceskull
spiceskull

OMG! That is just so spooky. Just today I was sorting some lists in Excel, and thought "wouldn't it be cool to sort by the actual days of the week, rather than alphabetically." I had a nosey around, and found just this very function, and "Geeked" around with colleagues who would be interested, I now get home and find this very guide to that function ... thanks for that.

Darryl~
Darryl~

I tried with Apple*,Orange*,Grape*.... Apple??,Orange??,Grape??... and even Apple-?,Orange-?,Grape-?.... None seemed to work for me.