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.
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.
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.
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.
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.
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.
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:
- From the Sort By drop-down, choose Category, the column that contains the sort values.
- From the Order drop-down, choose Custom List, as shown in 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.
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.
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.