For today’s Stupid Microsoft Office Trick, we will be teaching Excel how to sort musical instruments into score order. This has lots of uses for musicians and music educators:
- Inventories of instruments, sheet music, CD’s, you name it
- Rosters of students, orchestra members, sub lists, and so forth
For example, suppose I have a list of sheet music for various woodwind instruments:
If I sort alphabetically by column C, I’ll get bassoon pieces first, then clarinet, flute, oboe, and saxophone. But as a musician I rarely have reason to sort things that way. I would rather have the flute pieces on the top, followed by oboe, clarinet, bassoon, and saxophone—a typical score ordering.
I’ll show you three easy steps to make this happen. I’m using Excel 2007 and Windows Vista, but I believe this feature exists in earlier versions of Excel as well. You are on your own for the exact details, unless someone cares to share in the comments section.
Here we go:
-
Specify your order.
In a blank worksheet, type your preferred score order list in a column.
I’ve done a really basic list here—5 basic woodwind families—but more elaborate lists are possible. A few tips on this are at the end of this article.
-
Import as a Custom List.
Click the column letter above your list (column A in my example) to select that column. Click the round Office button in the upper-left-hand corner, then click the “Excel Options” button, then click the “Edit Custom Lists…” button, then click the “Import” button, then click “OK” twice. Nice work.
-
Sort like a musician.
Select the data you want to sort. In my example, I’m selecting the list of woodwind sheet music. Click on the “Data” tab, then click the “Sort” button.
A dialog box pops up. In the “Column” field, choose the column with the instrument names (column C for me). The “Sort On” field should have “Values” selected. In the “Order” field, choose “Custom List…”
Choose your new Custom List, and click “OK.” If you want to add additional levels of sorting, such as sorting by composer or title, now is the time to do it; otherwise just click “OK” again.
Voilà
:
Perfectly sorted.
Some tips on creating your score order list:
- If you are creating a more comprehensive and specific list, you may wish to consider including some general names as well. For example, if you’re including “soprano saxophone,” “alto saxophone,” “tenor saxophone,” and “baritone saxophone,” you might also want to include just “saxophone,” to give yourself a little extra flexibility for how you use your custom sort order.
- You may also want to include abbreviations (“pc,” “fl,” “ob,”…).
- If the data you’re sorting includes instrument names that aren’t part of your custom sort order, they will end up at the bottom of the list in alphabetical order.