Is there a way to sort the grand total *row* of a three-column PivotTable in
Excel? I have a month-by-month analysis to do on client spending, and have
consolidated the three months into a PivotTable using the wizard. Now I need
to sort this list by total spend but when I try to sort I get the error:

"Cannot determine which PivotTable field to sort by". I have tried using
the Advanced options for sorting but these are no good as they don't work on
the Grand Total column itself. Has anyone managed to make a PivotTable sort
without using VB? I'm sure there must be a simple answer (it's one of those
Excel functions that you expect it to do without any effort)!

If nobody has a proper solution, you can fall back on:

1. Select the entire pivot table
2. Copy it and paste it Value elsewhere.
3. The copy is a simple table and can be sorted using normal approaches
--
Gary's Student


"Aaron Howe" wrote:

Is there a way to sort the grand total *row* of a three-column PivotTable in
Excel? I have a month-by-month analysis to do on client spending, and have
consolidated the three months into a PivotTable using the wizard. Now I need
to sort this list by total spend but when I try to sort I get the error:

"Cannot determine which PivotTable field to sort by". I have tried using
the Advanced options for sorting but these are no good as they don't work on
the Grand Total column itself. Has anyone managed to make a PivotTable sort
without using VB? I'm sure there must be a simple answer (it's one of those
Excel functions that you expect it to do without any effort)!

Have the same problem, there must be an easy way. Looking hering from anyone
who know the way

"Aaron Howe" wrote:

Is there a way to sort the grand total *row* of a three-column PivotTable in
Excel? I have a month-by-month analysis to do on client spending, and have
consolidated the three months into a PivotTable using the wizard. Now I need
to sort this list by total spend but when I try to sort I get the error:

"Cannot determine which PivotTable field to sort by". I have tried using
the Advanced options for sorting but these are no good as they don't work on
the Grand Total column itself. Has anyone managed to make a PivotTable sort
without using VB? I'm sure there must be a simple answer (it's one of those
Excel functions that you expect it to do without any effort)!

Double-click the first Row field button
Click the Advanced button
For AutoSort, choose Descending
From the Using Field dropdown, select the data field,
e.g. "Sum of Units"
Click OK, click OK.

Aaron Howe wrote:
Is there a way to sort the grand total *row* of a three-column PivotTable in
Excel? I have a month-by-month analysis to do on client spending, and have
consolidated the three months into a PivotTable using the wizard. Now I need
to sort this list by total spend but when I try to sort I get the error:

"Cannot determine which PivotTable field to sort by". I have tried using
the Advanced options for sorting but these are no good as they don't work on
the Grand Total column itself. Has anyone managed to make a PivotTable sort
without using VB? I'm sure there must be a simple answer (it's one of those
Excel functions that you expect it to do without any effort)!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thank you Debra. That is actually the method from the help file and I really
can't understand why I couldn't make it work before. However, it works now!
It might be because I'm doing this on consolidation now rather than from the
same data area. In any event, thank you very very much!

"Debra Dalgleish" wrote:

Double-click the first Row field button
Click the Advanced button
For AutoSort, choose Descending
From the Using Field dropdown, select the data field,
e.g. "Sum of Units"
Click OK, click OK.

Aaron Howe wrote:
Is there a way to sort the grand total *row* of a three-column PivotTable in
Excel? I have a month-by-month analysis to do on client spending, and have
consolidated the three months into a PivotTable using the wizard. Now I need
to sort this list by total spend but when I try to sort I get the error:

"Cannot determine which PivotTable field to sort by". I have tried using
the Advanced options for sorting but these are no good as they don't work on
the Grand Total column itself. Has anyone managed to make a PivotTable sort
without using VB? I'm sure there must be a simple answer (it's one of those
Excel functions that you expect it to do without any effort)!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

I have a similar problem. I have a pivot table that I update on a
monthly basis. It has company name and region followed by units and
volume in the data area. within each region, the companies are listed.
I then sort the companies descending by volume to see who is the
highest in each region. My problem is that whenever I refresh the pivot
table I loose that formatting and have to re-sort each region again. Is
there a way to make the sort "stick".

Hope that all made sense.

Thanks in advance for any help provided.


--
jesterhs
------------------------------------------------------------------------
jesterhs's Profile: http://www.excelforum.com/member.php...o&userid=20902
View this thread: http://www.excelforum.com/showthread...hreadid=380503