fbpx
Search
Close this search box.

Sort Pivot Tables the way you want [Quick tip]

Share

Facebook
Twitter
LinkedIn

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.

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

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
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.

20 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.

    • Rachael H says:

      Here is some additional information following on from Jeff's post.....
      If you create the Custom List by manually entering the values you are limited to 255 char. If you wish to add a list with more more than 255 char use the "Import list from cells" option. If you elect to use the "Import list from cells" option make sure that your list only contains text or numbers i.e. no formulas or you will get the somewhat confounding error message "Cell without simple text were ignored".

  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

  12. Bob Clark says:

    I am trying to figure out how to sort a pivot table based on the results of another pivot table.

  13. Hilary says:

    Thank you. Helpful Visual, so used to sorting rows of data that I wouldn't think to sort the leftmost column by the value in the rightmost to achive a whole pivot table sort.

  14. Maqsood Hasan says:

    Hi Chandoo,

    Thanks for sharing your knowledge.

    A question for you, I have created a custom sort list in excel and it's working fine when I create a pivot table in the data workbook. But if I import/connect data in another workbook and create a pivot table, data is sorted alphabetically and not per custom sort list.

    I am creating a dashboard from the data and want to keep data workbook and dashboard separately.

    I posted this question to a number of excel forum including Microsoft Excel forum but haven't gotten any response yet.

    Appreciate your help.

    Thanks,
    Maqsood

Leave a Reply