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

Posted on November 29th, 2016 in Pivot Tables & Charts - 4 comments

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:

raw-data-pivot-table-row-label-custom-sort

And you want this.

pivot-table-row-label-order-incorrect

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.

rearrange-pivot-row-labels-v1

The smarter, but slightly longer method:

pivot-table-row-label-custom-sort

  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.

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

4 Responses to “Sorting to your Pivot table row labels in custom order [quick tip]”

  1. Crisu says:

    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.

  2. Sunny Kow says:

    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

  3. Sunny Kow says:

    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.

  4. Lisa says:

    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?

Leave a Reply