Make A Custom Sort List In Excel

Sun 24th July 2011

Excel will generally sort data alphabetically. If you want to sort by another criterion, then the simple process described in this article will show you how to make a custom sort list.

One of the less well known Hollywood film companies has decided to lift a novel from the printed page to the silver screen. The book, The Cereal Killer, by Fran Blake, is a whodunit in which people are bumped off after they find a small silver dagger in their corn flakes. But who will play the leading role of the murderess?

The casting department have an Excel spreadsheet containing details of hundreds of actresses, from which they hope to unearth a gem. The producers have stated that they want to stick as closely as possible to the original story, in which the killer is described as a 'raven haired beauty', and so preference will be given to actresses with dark hair.

The spreadsheet does have a column heading Hair Colour, but as the actresses are listed alphabetically by surname, this column is currently a mix of random hair colours, like a barber's dustpan. It would be easy enough to sort the data by hair colour, but Excel would automatically do this alphabetically: blonde, brunette, dark and redhead.

The casting department would like the spreadsheet printed off with the dark haired actresses at the top of the list, followed by the brunettes, redheads and finally, the least suitable blondes, who gentlemen do not prefer on this occasion. Luckily, there is an Excel expert among their number and he solves the problem by creating a custom sort order.

The Excel expert clicks on Tools, then Options, and in the dialog box that opens he selects the Custom Lists tab. He clicks on the NEW LIST option in the Custom Lists pane, and then in the List Entries pane, he types the contents of the column he wants to sort, in the order he would like them to appear on the printout (In this particular spreadsheet, hair colour is stored in column F). He enters in the following, making sure he types the hair colours exactly as they appear in the spreadsheet, and he presses Enter after each one.

Dark
Brunette
Redhead
Blonde

He clicks the Add button and the new list appears in the Custom Lists pane. He clicks OK to confirm and close the dialog box.

Back in the spreadsheet, he selects the entire sheet by clicking the small rectangle by the top right corner of the cell A1 (Ctrl + A would do this as well). He clicks on Data and selects Sort from the list, which opens the Sort dialog box. On clicking the down arrow to the right of the Sort By box, he selects Column F from the list and then he clicks the Options button. He clicks another down arrow at the right of the First key sort order box, and this opens up the Custom Lists pane, where he selects the hair colours list he just entered. He clicks OK and this returns him to the Sort dialog box. He clicks OK again and in the blink of an eye his data is sorted exactly as the casting department requested.

In Excel 2007, the process is slightly different. To enter your data in the order you want it to appear, click the Office button and then Excel Options to open that dialog box. Be sure to select Popular on the left and then click on Edit Custom Lists to open a Custom Lists dialog box. The process is now the same as above: select the NEW LIST option in the Custom Lists pane, and then in the List Entries pane, type the contents of the column you want to sort, in the order you would like them to appear. Click Add and then OK.

And so another problem is solved by a simple process in Microsoft Excel. There are so many tricks up its capacious sleeve that it really is worth making the effort to discover just what they are. Francis Bacon once said that knowledge is power: attaining knowledge of Excel certainly puts an extremely powerful application at your disposal.

Author is a freelance copywriter. For more information on excel training companies london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1822-make-custom-sort-list-in-excel.html