Microsoft Tips and Codes

Once in a while, we have lists that we need to sort in custom ways. The default in a pivot table is alphabetically. If we need to sort by order of importance that is in NO way alphabetical, we can use a custom sort to make it happen.

I am going to use a list we use to provide reports for our reference collection measurements. Here is the list:

RefFloor1, RefTier2, ShortShelf, HIDesk, TestAssess, Encyc, 188/MPS, Atlas, College, Career, Rm161, ANSI

The first two items are the main shelving units in our collection which we want to appear in the first two columns. As we go through the list, items are grouped by where they are in the collection and according to past worksheets.

How do I get these to show up in this order as column labels in my pivot table?

The easiest way to do this is with a custom list.

1. Open the excel file you want to sort and place your cursor in the top cell of the column you want to sort.
2. From the Home ribbon, click the Sort and Filter button and select Custom Sort from the menu.
3. In the Sort pop-up box, click the pull-down arrow in the Order column and select Custom List...
4. With NEW LIST selected in the Custom List box, place your cursor in the List Entries: box and type your field list, one per line
5. Once you have your list completed, click the Add button
6. Select the list from the Custom Lists box
7. Click OK
8. The list will appear in the Sort by row
9. Click OK

Your list will appear in the sort order you just made, AND when you make a pivot table, it will appear in that order as well.

The document attached has these instructions with visuals so you can see how this is done.