In a report that I was working on, I ran into a problem with the way Excel was sorting the pivot table. While Excel usually sorts the 3-letter library codes without a problem, this time it pulled the listings for two libraries out of order at the very top of the list. I couldn’t figure out why Excel suddenly forgot the alphabet for those two locations until I realized that their 3-letter codes were the abbreviations for a day of the week and a month of the year. Excel was trying to be “helpful” by putting those two codes at the top.
There wasn’t a convenient “don’t do that” button to force it to go back to a straight alphabetical sort and I was wondering if I’d have to manually edit the spreadsheet to put the rows for those libraries back where they belonged when Greg told me about custom lists. They’re a way you can set up a new sorting order for Excel. You can then choose your custom list and Excel will use that as the sort criteria.
To create a custom sort list:
- Open Excel and type the values you want to sort by in the order you want them, from top to bottom, in a single column in a spreadsheet.
- Select the cells in the column that contain your new sort criteria. Don’t select the entire column, just the cells that have entries.
- In the Menu bar, click on File and then choose Options from the list.
- This will open the Excel Options window. Click on Advanced from the list on the left of the window.
- Scroll all the way down to the bottom of the window and, just above the colored bar for “Lotus compatibility” there should be an Edit Custom Lists button. Click on it.
- A Custom Lists window should now be showing. The range you had selected earlier should be showing in the “Import list from cells” box towards the bottom of the window. Click on Import.
- The list should now show in the Custom lists with the full list of the contents in the List entries box. Click on OK.
- The next time you need to sort based off of this list, under Order where you’d normally choose ascending or descending, choose Custom List and then just click on the list you created.