Sorting in Excel PivotTables

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:

1. Select the row label you want to move
2. Hover your mouse over the outer edge of the cell until your mouse pointer changes to the 4-headed arrow (this can be tricky)
3. 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 😉

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.

Custom Lists

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:

Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.