After Hours

10 steps to creating a custom list for sorting in Excel

When you need to implement a special sort order that Excel can't accommodate, use this trick to set up a custom sort.

Sometimes, business rules or some property that's inherent to your data requires a sort order that Excel can't handle -- at least, not without a little help. For instance, you might want to sort order records by sizes, using the following order: Petite, Small, Medium, Large, and X-Large. If you sort alphabetically, you won't get the results you want. When rules force these types of sorting requirements, you can build a custom list and then sort by it. You might not consider this route on your own, but it's easy!

Note: You can download the demo files used for this technique.

1: Consider your data

Sort tasks don't always come in alphabetical or numeric order. Figure A shows a simple data set sorted by category, alphabetically. Although the sort groups by category, it might not be in a meaningful order for those using the data. They might want to see the categories sorted in a unique way, one that Excel's sort feature can't handle.

Figure A

Business rules require records be sorted as shown, not by alphabetical order.

Column A stores the data entry order and is for your convenience while learning. You can resort by column A anytime you want to reset the records.

2: Determine the sort order

A custom sort is needed. But first, you must determine the order of the values. In this case, the order, shown in Figure B, isn't particularly straightforward. Those using the data require this order, but there's no way a simple sort task can fill that requirement.

Figure B

This unique sort order might produce a meaningful report for those studying the data.

3: Path to custom lists

You could enter both a sort order value and the category, but two values instead of one is inefficient, and fortunately, unnecessary. Instead, you can create a custom list that recognizes the custom order (specified in step 2). In Excel 2010, click the File tab, select Options (under Help), click Advanced in the left pane, and then click Edit Custom Lists in the General section (Figure C). In Excel 2007, click the Office button and click Excel Options. Select Popular in the left pane and then click Edit Custom Lists in the Top Options For Working With Excel section. Excel 2003 users should choose Options from the Tools menu and then click the Custom Lists tab.

Figure C

Click the Edit Custom Lists option to create a custom list.

4: One way to create the list

At this point, you're ready to create the custom list. You can do so manually or select the sorted values in a sheet. In this step, I'll show you how to create the list manually. If you want to select the values, skip to step 5.

In the List Entries control, enter the first value in your custom sort order. In this case, that's Jewelry, as shown in Figure D. Then, press Enter, not Add. To complete the list, enter the additional values as you want them sorted. Using Figure B as a guide, add the remaining values, making sure to press Enter and not Add.

Figure D

Enter the custom list manually.

5: A second way to create the list

If the ordered list exists in a sheet, you can point to it. Click the selection icon in the Import List From Cells option (circled in Figure D). If the list isn't on the current sheet, click the appropriate sheet tab (Category, in this example). Then, select the ordered list, as shown in Figure E. Finally, click the selection tab to return to the dialog.

Figure E

Select the sort values.

6: Add the list

Completing the list depends on how you entered the items. If you created the list manually (step 4), simply click Add. If you selected the list in a sheet (step 5), click Import. Either way, Figure F shows the resulting custom list (created by importing). Click OK twice to return to the sheet.

Figure F

You just created a new custom list.

7: Initiate the custom sort

You're now ready to use the custom list to sort records. To initiate the sort in Excel 2010 and 2007, click anywhere inside the data range and then click the Data tab. Click Sort in the Sort & Filter group. In Excel 2003, choose Sort from the Data menu.

8: Further define the sort in Excel 2010 and 2007

Excel 2010 and 2007 will display the Sort dialog, and this is where you'll select the custom list as your custom sort order:

  1. From the Sort By drop-down, choose Category, the column that contains the sort values.
  2. From the Order drop-down, choose Custom List, as shown in Figure G.

Figure G

Choose Custom List from the Order drop-down to access custom lists.

If you're using Excel 2003, choose Category from the Sort By drop-down and click the Options button.

9: Specify the custom list

At this point, you can choose from the custom lists via the Custom Lists dialog. Select the category list in the Custom Lists control and click OK. Figure H shows the custom list as the new sort order.

Figure H

After identifying the Category custom list, Excel uses it as the Order setting.

If you're using Excel 2003, choose the custom category list from the First Key Sort Order drop-down and click OK.

Before continuing, be sure to indicate whether your data range has a header row. In Excel 2010 and 2007, use the My Data Has Headers option (circled in Figure H). In Excel 2003, click Header Row or No Header Row.

10: Sort

The last step is to click OK to sort the data by the specified field, Category, using the custom list. Figure I shows the records sorted accordingly. For a descending sort, you need another custom list.

Figure I

The data, sorted by a custom list.

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.

6 comments
bluesman_99
bluesman_99

Sorry, But that is way too complex for me. Just create the "sort order" column in the main spreadsheet like in figure B. Sort the list by the "sort order" column, then HIDE the "sort order" column if you prefer NOT to see it. Simple stuff, and really effective. To me, its all about efficiency. A learning curve is just "twisted"!

Mastercraft209
Mastercraft209

In Excel 2010 (not sure about earlier versions), there is a simpler way; you don't have to preset the Custom Sort Order, you can specify it at the time of the sort. When specifying the sort order (jump straight to Step 9), simply select Custom List, then in the Custom List box, select NEW LIST, then enter the items (Jewelry, Beauty, etc.) in the List Entries box. Much easier than going through the Path to Custom Lists in Step 3.

Suresh Mukhi
Suresh Mukhi

If TR had a like button for TR, ( not for FB ), I would click it for this article. :thup:

DaveUnger
DaveUnger

Great tip, Susan! This came at an opportune moment for me, and simplified what initially was starting to look like a very complicated process. Thanks very much. Dave

Useless_Wizard
Useless_Wizard

@Mastercraft209 Hey'a, this is what I thought too but there is a limit you can type or paste in as a sort list directly, which was annoying as I had an ordered column of unique values spanning 120+. Using the import custom sort I was able to use all my ordered values. I'm not sure why there should be a disparity but that's what happened in my Excel 2010.  

ssharkins
ssharkins

I'm glad you found this useful.