fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

8 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?

  5. Chihiro says:

    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.

  6. Rajesh Chawda says:

    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 ?

  7. Njemile says:

    Is there a way to hide the sort order column so it does not appear in the charts created from the pivot table?

    Thanks

  8. Alain Van Holder says:

    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.

Leave a Reply