My second Excel love is PivotTables (VLOOKUP is still my number 1).
PivotTables make short work of summarising data and if you change your mind like the wind you can easily satisfy those whims with little more than a drag and a drop, unlike formulas which often require a lot more cajoling.
Once you have your data all Pivoted you might like to experiment with sorting, so here we have a few options ranging from Plain Vanilla, DIY and Bespoke.
Sort A to Z - Plain Vanilla
You can sort by the row labels, column labels or the values area. A simple right-click on any of these areas reveals the sort options:
Select a cell in the row or column label area > right click > Sort > A to Z or Z to A:
Select a cell in the Values area > right click > Sort > Smallest to Largest or Largest to Smallest:
Manual Sort Order - DIY
If you just want to manually sort the items, maybe you don’t have many labels to rearrange, then you can left click and drag them into place:
- Select the row label you want to move
- Hover your mouse over the outer edge of the cell until your mouse pointer changes to the 4-headed arrow (this can be tricky)
- Left click and drag it into place:
Here is an animation of manual sorting:
The other way to manually sort is to type over an existing label with the one you want and watch the remaining labels rearrange themselves:
Note: You must type in a label that exists in the PivotTable source data, you can’t go making stuff up 😉
Enter your email address below to download the sample workbook.
Custom Sort Order - Bespoke
Lastly we can set up a custom sort order with the help of a Custom List. It requires a bit of setup so maybe only do this if you’re going to be performing this sort regularly, or you plan on using the Custom List elsewhere (more on that later).
Create a Custom List
To setup our Custom List:
- Excel 2007: Windows button > Excel Options > Popular > Edit Custom Lists
- Excel 2010/2013: File tab > Options > Advanced > General > Edit Custom Lists
The image below shows the Excel 2010/2013 Options window where you'll find the 'Edit Custom Lists' button:
This will open the Custom List dialog box where you can 1. type in your list or, 2. import them from a range of cells then click ‘Add’ to create the list, then 'OK' when you're done:
Custom Sort PivotTable
Now you can use your new Custom List as the basis for your PivotTable sorting.
To do this select the row/column labels you want to sort > right click > Sort > More Sort Options…:
Then in the Sort dialog box click on ‘More Options…’:
Uncheck the ‘Sort automatically every time the report is updated’ and select your Custom List from the ‘First key sort order’ list:
Then click OK. This will take you back to the ‘Sort’ dialog box (below) where you need to choose your field from the ‘Ascending (A to Z) by:’ list, in my case I’m sorting by ‘State’:
Now your PivotTable is sorted using the order of your Custom List.
Don’t let your Custom List go to waste. You can use them in other Sorting menus, like the one on the Data tab (or when you right-click and select Sort):
And you can use it to AutoFill cells too.
Simply type in the first item in your list and then drag the fill handle:
the sorting doesn’t always seem to work …
if I have a PT with applied filters the sort doesn’t work – when the filters are removed they work
I have tried data model and without data model and I am stumped as to why
You should still be able to sort with fitlers applied. Can you please share the file and explain the sort you’re trying to do on our Excel Forum and we’ll take a look.
You’re welcome, Manou 🙂
Thanks, I use pivots every day and didn’t know I could sort this way! Awesome!
Thanks, Mary 🙂
Download the Excel Workbook and try yourself. Note: this is a .xlsx file please ensure your browser doesn’t change the file extension on download.
I down load the file but its showing ************* Please help to down load in Excel 2007
It sounds like the file hasn’t downloaded with the correct .xlsx file extension, as I can download and open it fine. If you check the file name does it have .xlsx on the end?
If not, please download it again and at the ‘Save as’ screen (or equivalent for your browser) change the file extension to .xlsx.
Typing a label over the other one – awesome trick!
Cheers, Pmsocho. I have MrExcel to thank for that trick!
Thanks for a good tip! I, too, find pivot tables to be an excellent tool, but I didn’t realize sorting could be this easy.
Thanks, GJ. Something for everyone with PivotTable sorting 🙂
Very nice recap of PivotTable sorting. Thanks!
Cheers, Michael 🙂
That is interesting and work for sorting purposes. Thanks for sharing.
Cheers, Jef 🙂
You’re welcome, Arif 🙂