Jun 06, 2014

Sorting is a common task in Microsoft Excel, but it does have a few specific, sophisticated options that not too many people know. One of these is the ability to sort in an order using a custom list. When sorting text you can sort alphabetically (A-Z), reverse alphabetically (Z-A) or by a custom list. Custom lists are useful when you want to sort by an order that isn’t alphabetical or reverse alphabetical, such as days of the week, months of the year, top 10 clients, or other specific choices.

You are probably already aware that Excel knows the days of the week and the months of the year. If you type a day or month and then autofill it, Excel completes the sequence. So Excel must know these sequences and must store them somewhere. Excel stores them as custom lists. If you want to sort by a custom list, firstly select your data and click on the ‘Sort & Filter’ button in the ‘Editing Panel.’ Click on the ‘Custom Sort’ button and in the dialogue box, choose the ‘Custom List…’ option from the ‘Order’ drop-down. Once you hit OK, the below dialogue box should appear.

This is where the custom lists are stored and where you can add your own custom lists. Note that custom lists are stored in your application of Excel, NOT in the file. So, if you go to a new computer you may have to recreate your custom lists. To create your own custom list, click the ‘Add’ button and then type in the list entries in the order you want them to be in. Then click ‘Add’ again to lock in the new list. You can delete custom lists you have created yourself, but you can’t delete the preset custom lists.

Now, you can sort the column by the custom list. Excel will treat the items in the list as the most important things in that order when sorting, with any items not in the list automatically going in the alphabetical/reverse alphabetical order at the bottom of the data. This way, you could create a list of the top 10 clients who would always appear in the order you would like at the top of the data, and any other clients would appear in normal alphabetical order below the top 10. If you want to change, add, or delete a custom list without going into the ‘Sort’ menu, you can do so in two simple ways depending on if you’re using Excel 2010 or Excel 2007.

1. Excel 2010: In the ‘File’ tab, select ‘Options,’ and in the dialogue box, click ‘Advanced’ and select ‘Edit Custom List.’ You’ll have to scroll to the end to find it.
2. Excel 2007: Click on the Office button and under ‘Excel Options,’ select ‘Edit Custom Lists.’

With both methods, you’ll end up with this screen:

This menu is very similar to the sorting version of custom lists, but the difference here is that you can also import a custom list from a range of cells in an Excel sheet. To do so, click in the text box next to the ‘Import’ button and then highlight the area of cells on the sheet that you want to use for your custom list.

Once you click on the ‘Import’ button, this creates a new custom list without you having to type each entry. As a bonus, you can even autofill with any of your custom lists to quickly repeat that sequence. Hope this helps you with your sorting!