Pivot tables are lovely. But sometimes they are hard to work with. Let’s say you are analyzing some HR data and want to see number of weeks worked in each hour classification. You have data like this:
And you want this.
Except, there is a teeny tiny problem.
The sort order on the classification is all messed up.
Here is a quick fix to get custom sort order on your pivot table row labels.
OK, I lied, There are actually two ways to do this.
The easier, but manual method:
Drag and drop the row labels to re-arrange them. Pivot table will remember this order even when you refresh. Of course there is a downside. In case you add some new values in the row label area, they will be at the bottom.
The smarter, but slightly longer method:
- First define the sort order in a list. Let’s say this list is in $I$3:$I$12
- Now, add an extra column to the original data.
- Using MATCH formula, find the order of each row label (in our case, classification) in the sort order list. Assuming classification is in D3, use =MATCH(D3, $I$3:$I$12, 0)
- Create a pivot table with data set including sort order column.
- Add sort order column along with classification to the pivot table row labels area.
- Add the usual stuff to values area.
- Set up pivot table in tabular layout.
- Remove sub totals
- Finally hide the column containing sort order.
- Your new pivot report is ready.
Good news for people with Excel 2013 or above:
- Once you have the sort orders table, just link to your original data set thru data model.
- You can then use sort order column in the pivot report directly. No need to write MATCH() formula.
- Refer to our relationship advice for Excel 2013 or above users.
Download Example Workbook
If all of this instruction is sort of tricky for you to follow, click here to download the example workbook.
How do you deal with pivot table sorting problems?
If my data is in Power Pivot, I rely on the excellent sort by feature. It is a god send. But when my data is in Excel (or I can’t use Power Pivot), I rely on the approach outlined in this post.
What about you? How do you deal with messed up sort order problems in your pivot tables? Please share your tips in the comments.
8 Responses to “Sorting to your Pivot table row labels in custom order [quick tip]”
Third way is to use a custom list (as shown here https://support.office.com/en-us/article/Sort-data-using-a-custom-list-def8ff2b-681a-4fc3-9bd2-a06455c379e1).
You just have to create list outside the pivot (don't have to use it anywhere, only add custom list and leave the Sort window) and in pivot it will appear in More sort options... -> More options -> First key order. Not perfect but usable.
Another way is to key in the row label to the new position.
Example : Enter 1-5 onto the 10-15 position and press enter.
1-5 will then move to that position.
Sunny
You can also change the classification to:
0 -Did not work
01-05
05-10
10-15
15-20
20-25
25-30
30-35
35-38
38 and above
This will sort properly.
Leading zeros (as Sunny says) are great to fix name so that normal sorting works.
If you have alpha data that you cannot rename (like a status code) but need in a logical sequence (e.g. Create. Review, Develop, Test, Deploy) then a custom list (like Crisu mentions) is good, as is a separate sort order column.
Does no one mention that the classifications in the example are conflicting for the border cases?
I mean, 1-5 then 5-10 --- where is exactly 5 supposed to fall?
On Excel 2016, custom sort list isn't usable in Pivots. I typically create custom bucket function in PowerQuery and add sort list as well to sort PivotTable.
This was awesome tip for custom sorting the row labels of a pivot table. Similarly, is there any shortcut / trick to do a custom sort to column Headers of a Pivot Table ?
Is there a way to hide the sort order column so it does not appear in the charts created from the pivot table?
Thanks
I'd like to add something about hiding columns or rows in a datasheet: you better use the Group option.
In big databases hidden rows or columns aren't detected by users and therefor can lead to misinterpretation. When you use the Group option, it is always visible that a row or column has been hidden.