Sort Pivot Tables the way you want [Quick tip]

Posted on May 31st, 2012 in Excel Howtos , Pivot Tables & Charts - 15 comments

Ever looked at a Pivot table & wondered how you can sort it differently?

“If only I could show this report of monthly sales such that our best months are on top!”

Well, there is a way to do it without sacrificing 2 goats or pleasing the office Excel god. Just use custom sorting options in Pivot tables.

Custom Sorting of Pivot Tables

Assuming you have a pivot table like this:

Sorting Pivot Tables in any order - how to

… and you want to sort it such that best months are on top.

Just use below process.

  1. Click on the month name column
  2. Go to “More sort options”
  3. Choose Descending
  4. From the drop-down, pick “Sum of sales” as the criteria
  5. Click OK and you are done!
  6. Save the goats for some other occasion.

See this quick demo to understand how its done.

Custom Sorting Pivot Tables in Excel - Demo

BONUS TIP

If you want the data in your own order, you can move the values in pivot report around. Just drag & drop to re-arrange the report!

Do you sort your pivots?

I use sorted pivot reports for various reasons. Often, I use sorted data as intermediate step in a dashboard or big model.

What about you? Do you sort your pivot reports. What techniques do you use? Please share using comments.

New to Pivot Reports, check out this:

If you want to learn about pivot tables & pivot reporting, you have hit a mine. We got lots of material on it. Start with these,

PS: No goats or any other animals were hurt while writing this post.

Your email address is safe with us. Our policies

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

15 Responses to “Sort Pivot Tables the way you want [Quick tip]”

  1. Rich says:

    Bummer though that this option is not available in Office 2011 for the Mac. You can sort of do this through the Data->Sort (shift+cmd+r) but it doesn’t work well with nested sorts of multiple row labels.

  2. benny269 says:

    Or alternatively just right-click any entry in the values list you wish to sort by (Net Sales in this example) and select ‘Sort’ > “Largest to Smallest”.

    Much easier.

  3. Tom says:

    God bless you, Chandoo.  (And you too, benny269.)

  4. Ramnath Divakaran says:

    Actually, I use multiple column (field) sorting for the ease of making graphs. In brief, I need to make Product wise, Type wise and Category wise graph. Category will be around 50-60 in number and I use Clustered Bar chart. The reality is if you use Pivot table for multiple columns, you may not be getting the Type wise, Category wise in Asc/Dsc order. So, I use to sort the columns (fields) from the “Choose Fields to add to Report:” inside in to either “Asc/Dsc order” for all Product, Category & Types. This way you can control the sort not only for one column, but for multiple columns in your Pivot table. Now, you can copy the Pivot table data as such and go for a chart which is ready made. You will get the result as Product & Category Wise “each Types – sorted in Descending order”. It will look nice as well.

    I am not sure as to how to share a screenshot. Let me know if this benefit you guys.

  5. LeonK says:

    Many thanks for this apparently simple tip.  I didn’t pay it attention until today and immediately realised how, with Pivot Chart, another overlooked function by me, I could use this every day.
     
    This single tip has lead me to the point where I can now prototype anything I’m asked almost immediately, with supporting table and graph.  I can then work on more permanent models if required, once I know the level of requirement.  Previously, I was creating complex, dynamic and interactive models, usually in the evening at home, even though they had limited use.  Still, I did get ample opportunity to put into practice techniques learned from this site.  
     
    let me tell you what this tip means to me – I appear awesome at work and I now have time to start the VBA course, eventually – Thank you Chandoo.

  6. Jeff Weir says:

    And you can also import custom sort orders into Excel, then tell your pivot table to use them.  see http://office.microsoft.com/en-us/excel-help/create-or-delete-a-custom-list-for-sorting-and-filling-data-HA010222142.aspx or give Google a spin.
     
    So if the boss ALWAYS wants his division, and the divisions of his minions to appear at the top of the pivot, you can do it.

  7. Shan says:

    Sorry this may not be relevant but how did you create a gif of your excel actions? 

    I create a lot of excel guides for my colleagues and having visual instructions like that would help me and my colleagues a lot.

    Any info would be greatly appreciated

    Thanks.

     

  8. Manny says:

    Chandoo,

    Nice work! Hey, how do you set up the .gif file to show your excel moves?

    Thanks!!!

  9. Sameer says:

    Hi,
    My data is sorted as per the month eg: 3 rows for Jan, 5 rows for Feb, 7 rows for Mar and so on. But when I am making a Pivot chart for such table. My Chart bars are not coming sorted by month like Jan-Feb-Mar. Its showing like Mar first than Feb-Apr-May-June…….Jan-Feb.
    i’ve tried all the above techniques. Even my Pivot is not sorted by amount or alphabetic. Please help.

  10. kbell says:

    Thank you for all your help, but it appears that I will have to go back to Excel on the PC to create some needed charts.

    I have a pivot table that has State and County on the left and total on the right. With Excel for Windows, I can sort on the total and put it into a chart that makes sense.

    Along the bottom axis, I have counties grouped by states and the bars are descending left to right showing the counties with the most contribution, again grouped by states.

    This just does not seem possible with Excel for Mac.

    Using the examples given above, I can get data from a single state to sort, but it doesn’t follow across all states. AND, I cannot get the counties listed along the bottom axis.

    If anyone can help with this, please shout out.

    Thanks,

    kbell

  11. sue says:

    Why does MS always keep the simplest of things well hidden? Thanks Chandoo

Leave a Reply