98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
Make A Custom Sort List In Excel
Sun 24th July 2011
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
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
Testimonialsebm-papst UK Ltd
PA To The MD Janine Harris Lync End-User Richard is a an excellent trainer, he keeps you engaged and listening to what he has to say at all times Harry's
National Account Manager Rose Finney Excel Advanced Simon covered topics at the right speed with a great level of enthusiasm. I have every confidence that if there was something specific I wanted to revisit, he would make it possible Streetscape
Landscape Project Manager Mat Fiddes Excel Introduction Enjoyed the course, was very helpful. would have liked some more revision to ensure the skills learnt would stick |
PUBLICATION GUIDELINES