Feel Tips - 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 the last article “How to Custom Sort in Excel” we discussed special sorting solutions which don’t follow alphabetic and numeric order. There are four of these special sorting solutions built-in Excel custom sorts. For most of time, they are not enough. Fortunately, Excel enables you to create a custom sort if it’s necessary. For example, if you want to identify temperatures by Cold, Warm and Hot, but alphabetic sort would make it by Cold, Hot and Warm. However, you can create a custom sort to make it sort as your wish.

Now, let’s create a custom sort in this case of temperature. In Excel 2010, just follow the steps below: 

1. Go to the File tab and select Options (under Help).

2. In the left pane, select Advanced.

3. In the General section, click Edit Custom Lists.

4. After we are in List Entries control, enter each element in the sort order as you wish. 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 button Add, as the image shows below, Excel will transfer the new custom sort order to the Custom Lists control (on the left).   

6. After that Click OK twice.

In Excel 2007, there is a bit different:

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

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.

If you are using Excel 2003, you can create custom sort by following the steps below:

1. Go to Options from the Tools menu.
2. Click the Custom Lists tab.
3. Continue with step 4 above in 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.

When you create a custom sort list, it’s not just for the one you are working but for all workbooks. Custom lists are stored in the Windows Registry which means it can’t be used on other computers. However, if you’re working with the same workbook, the list itself is available. If you want to add the created new custom sort to a local system’s registry, you can open the Custom Lists dialog box, select the list in the Custom Lists control and click button Add.