• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Sorting in Excel PivotTables

You are here: Home / Excel PivotTables / Sorting in Excel PivotTables
Sorting in Excel PivotTables
June 5, 2014 by Mynda Treacy

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:

sort pivot table a to z

Select a cell in the Values area > right click > Sort > Smallest to Largest or Largest to Smallest:

sort pivot table a to z

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:

manually sort pivot table

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:

type in sort order for a pivot table

Note: You must type in a label that exists in the PivotTable source data, you can’t go making stuff up πŸ˜‰

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and try yourself. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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:

Create new Custom List

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:

Create new Custom List

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…:

sort with Custom List

Then in the Sort dialog box click on β€˜More Options…’:

sort with Custom List

Uncheck the β€˜Sort automatically every time the report is updated’ and select your Custom List from the β€˜First key sort order’ list:

first key sort order Custom 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’:

sort ascending order

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):

sort with Custom List

And you can use it to AutoFill cells too.

Simply type in the first item in your list and then drag the fill handle:

fill handle Custom List

Sorting in Excel PivotTables

More Pivot Table Posts

Excel Pivot Tables Year on Year Change

How to calculate Year on Year variances in Pivot Tables
Excel PivotTables Unique Count 3 Ways

Excel PivotTables Unique Count 3 Ways

Excel PivotTables Unique Count, or distinct count as it's also know, is easy, but there are different approaches depending on your version of Excel.
Excel Factor 6 Auto Refresh PivotTables

Excel Factor 6 Auto Refresh PivotTables

Excel Factor Entry 1 - Reverse PivotTable

Excel Factor Entry 1 – Reverse PivotTable

Create a Pivot Table Direct From Access

Create a Pivot Table Direct From Access

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you how to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.




Category: Excel PivotTablesTag: pivot table
Previous Post:Writing Excel Formulas EfficientlyWriting Excel Formulas Efficiently
Next Post:Excel Paste Special Operations

Reader Interactions

Comments

  1. Peter

    November 4, 2016 at 11:41 pm

    the sorting doesn’t always seem to work …
    if I have a PT with applied filters the sort doesn’t work – when the filters are removed they work
    I have tried data model and without data model and I am stumped as to why

    Reply
    • Mynda Treacy

      November 5, 2016 at 5:32 am

      Hi Peter,

      You should still be able to sort with fitlers applied. Can you please share the file and explain the sort you’re trying to do on our Excel Forum and we’ll take a look.

      Kind regards,

      Mynda

      Reply
  2. manou

    July 2, 2014 at 9:32 am

    thanks,very nice
    Cheers

    Reply
    • Mynda Treacy

      July 2, 2014 at 9:35 am

      You’re welcome, Manou πŸ™‚

      Reply
  3. mary

    June 8, 2014 at 2:48 am

    Thanks, I use pivots every day and didn’t know I could sort this way! Awesome!

    Reply
    • Mynda Treacy

      June 8, 2014 at 9:14 am

      Thanks, Mary πŸ™‚

      Reply
  4. adam junkeer

    June 6, 2014 at 10:57 pm

    Download the Excel Workbook and try yourself. Note: this is a .xlsx file please ensure your browser doesn’t change the file extension on download.

    I down load the file but its showing ************* Please help to down load in Excel 2007

    Reply
    • Mynda Treacy

      June 7, 2014 at 11:44 am

      Hi Adam,

      It sounds like the file hasn’t downloaded with the correct .xlsx file extension, as I can download and open it fine. If you check the file name does it have .xlsx on the end?

      If not, please download it again and at the ‘Save as’ screen (or equivalent for your browser) change the file extension to .xlsx.

      Kind regards,

      Mynda

      Reply
  5. pmsocho

    June 6, 2014 at 4:57 am

    Typing a label over the other one – awesome trick!

    Reply
    • Mynda Treacy

      June 6, 2014 at 6:49 am

      Cheers, Pmsocho. I have MrExcel to thank for that trick!

      Reply
  6. GJ Case

    June 6, 2014 at 12:46 am

    Mtnda:

    Thanks for a good tip! I, too, find pivot tables to be an excellent tool, but I didn’t realize sorting could be this easy.

    Reply
    • Mynda Treacy

      June 6, 2014 at 6:48 am

      Thanks, GJ. Something for everyone with PivotTable sorting πŸ™‚

      Reply
  7. Michael Rempel

    June 5, 2014 at 11:09 pm

    Very nice recap of PivotTable sorting. Thanks!

    Reply
    • Mynda Treacy

      June 6, 2014 at 6:48 am

      Cheers, Michael πŸ™‚

      Reply
  8. Jef

    June 5, 2014 at 10:04 pm

    That is interesting and work for sorting purposes. Thanks for sharing.

    Reply
    • Mynda Treacy

      June 5, 2014 at 10:23 pm

      Cheers, Jef πŸ™‚

      Reply
  9. arif hossain

    June 5, 2014 at 3:57 pm

    Thanks

    Reply
    • Mynda Treacy

      June 5, 2014 at 4:37 pm

      You’re welcome, Arif πŸ™‚

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.