16

As per an explanation found here by Mac CPub fro Excel 2011 (for OS X) supposedly you can sort "Grand Total" columns as per the following: "... Excel for Mac doesn't have a "Sort by Grand Total" option, but you can select the grand total column and then use the Sort button on the ribbon to do this."

When I attempt perform this function it unfortunately seems to be disabled as you can see in this short screencast.

Any idea as to how to allow sorting by Grand Total?

ylluminate
  • 12,102
  • 17
  • 78
  • 152

3 Answers3

20

You should select only the data part of your pivot table, not the headers or footers (in your screencast, starting form E5 down to A?). You can then use the standard sort option.

Romain
  • 1,292
  • 1
  • 10
  • 14
  • That is interesting that the other method does indeed work for the other data columns and simply not the "Grand Total" column. Seems to be a bug in OS X Excel. Further, with an enormous dataset that becomes a hassle; actually this set is so long that it didn't cross my mind to move to the bottom and try this method. Thanks. – ylluminate Oct 04 '12 at 14:45
  • 3
    This works the same in Windows. You have to select the entire column and use the "Sort & Filter" section of the Data tab in the Ribbon Menu. The "Sort" option is still disabled, but you can do a simple sort Ascending or Descending http://aaron-hoffman.blogspot.com/2013/04/microsoft-excel-2013-sort-pivot-table.html – Aaron Hoffman Apr 28 '13 at 18:56
  • 2
    You don't have to select the entire column, any single value from it will work for sorting purposes. Excel 2010. – Victor Zakharov Feb 23 '14 at 00:07
  • @AaronHoffman This is still a valid information for Pivot Tables. I was going crazy not being able to sort columns with context menu. And did not check the sort option from ribbon. – Pimenta Jul 15 '14 at 11:10
2

You can change the sort option of a row label. Click on a row label an choose More sort options . In this menu choose either ascending or descending and choose Count of XXXX from the drop down list.

Steffen
  • 31
  • 3
1

Here is the fix: I had the same problem and found I had to remove the third of three fields from the "Row" box of the pivot field selector boxes on the far right of the sheet. Then I could sort by Grand Total (just by right clicking in that column). Then when I added the third field back into the Row box it sorted correctly within each level. FYI, to see the field list boxes you must click on the pivot table.

gblum19
  • 11
  • 1
  • For my issue of a Grand Total not sorting, this worked. I removed many rows I had and sorted the total, then added the rows back into the Field List and it was still sorted. – MeachamRob Jun 19 '18 at 20:39