View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Pat Pat is offline
external usenet poster
 
Posts: 210
Default Excel Pivot Table How to Sort data as Percent of Row

I have solved it on my own. The problem is that the defaults are set to Auto
Sort. So when I was trying to manually sort the data it was resorting back to
the default. If you set all the sort options to Manual in the advanced sort
section it will work as expected.

"Pat" wrote:

Here is an example.

If you just use the data in the pivot table and sort it works fine.

Region 1/1/09 2/1/09 3/1/09 4/1/09 GTot
Amercas 59139 47732 59615 53393 219879
Asia 2310 2981 2895 1816 10002
EMEA 3650 557 586 648 5441
GTot 65099 51270 63096 55857 235322

However if you go to filed settings and display the data as "% of Row" as
seen here

Region 1/1/09 2/1/09 3/1/09 4/1/09 GTot
Amercas 26.90% 21.71% 27.11% 24.28% 100.00%
Asia 23.10% 29.80% 28.94% 18.16% 100.00%
EMEA 67.08% 10.24% 10.77% 11.91% 100.00%
GTot 27.66% 21.79% 26.81% 23.74% 100.00%

Then when you try and sort the percentages it sorts the underlying values
not the percentages. This only happens in Excel 2007 and did not happen in
Excel 2003.



"Shane Devenshire" wrote:

Hi,

I have no problem sorting the percent of row numbers? I put my cursor in
the column I want to sort on and click the Data, Ascending or Descending
button. Please supply us with more detail.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Pat" wrote:

Excel Pivot Table How to Sort data as Percent of Row

In Excel 2003 you could display data in a pivot table as percent of row then
sort the percentage. In Excel 2007 this does not work. Is there a work around
this was a very valuable feature and it take too long to copy and past the
table just so you can sort it.