Sorting to your Pivot table row labels in custom order [quick tip]

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:

1. First define the sort order in a list. Let’s say this list is in $I$3:$I$12
2. Now, add an extra column to the original data.
3. 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)
4. Create a pivot table with data set including sort order column.
5. Add sort order column along with classification to the pivot table row labels area.
6. Add the usual stuff to values area.
7. Set up pivot table in tabular layout.
8. Remove sub totals
9. Finally hide the column containing sort order.
10. 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.