A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Excel Pivot Table How to Sort data as Percent of Row



 
 
Thread Tools Display Modes
  #1  
Old May 19th 09, 06:32 PM posted to microsoft.public.excel.worksheet.functions
Pat
external usenet poster
 
Posts: 210
Default Excel Pivot Table How to Sort data as Percent of Row

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.
Ads
  #2  
Old May 20th 09, 05:56 AM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_2_]
external usenet poster
 
Posts: 3,346
Default Excel Pivot Table How to Sort data as Percent of Row

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.

  #3  
Old May 20th 09, 11:08 AM posted to microsoft.public.excel.worksheet.functions
Pat
external usenet poster
 
Posts: 210
Default Excel Pivot Table How to Sort data as Percent of Row

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.

  #4  
Old May 20th 09, 01:34 PM posted to microsoft.public.excel.worksheet.functions
Pat
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.

  #5  
Old May 20th 09, 03:33 PM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_2_]
external usenet poster
 
Posts: 3,346
Default Excel Pivot Table How to Sort data as Percent of Row


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

Cheers,
Shane Devenshire


"Pat" wrote:

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

  #6  
Old May 20th 09, 03:47 PM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_2_]
external usenet poster
 
Posts: 3,346
Default Excel Pivot Table How to Sort data as Percent of Row

Hi,

Well the reason it didn't happen in 2003 is 2003 didn't have all the option
(power) of 2007. Also, this problem really had nothing to do with the % of
Row option.

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

Cheers,
Shane Devenshire


"Pat" wrote:

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

  #7  
Old May 20th 09, 04:55 PM posted to microsoft.public.excel.worksheet.functions
Pat
external usenet poster
 
Posts: 210
Default Excel Pivot Table How to Sort data as Percent of Row

I agree it had nothing to do with the % of row. However the feature is not
working as expected and there may still be a bug.
The way I discover the solution is that if you change the field from "% of
row" to "% Different from (Previous)" you actually get an error (warning)
that it is not going to work "AutoSort and AutoShow cannot be used with
custom calculations that use positional reference. Do you want to turn off
AutoSort/Show?" This does not appear in the "% of Row" situation. The bug is
that weather you choose Yes or No the data still will not sort unless you
turn off the AutoSort from the More Sort Options on the on the row header.
Being able to choose Yes and have the option automatically turned off would
be helpful. In addition the setting only applies to the current row header.
If you pivot on a different row the setting does not stay and needs to be
reapplied.


"Shane Devenshire" wrote:

> Hi,
>
> Well the reason it didn't happen in 2003 is 2003 didn't have all the option
> (power) of 2007. Also, this problem really had nothing to do with the % of
> Row option.
>
> --
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
> "Pat" wrote:
>
> > 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.

  #8  
Old April 3rd 12, 11:57 AM
OkOkYay OkOkYay is offline
Junior Member
 
First recorded activity by ExcelBanter: Apr 2012
Posts: 1
Default

Hi Pat!

I have been having this exact same issue and it is most frustrating. Thank you for explaining the problem so clearly and concisely- I wasn't sure how to express the glitch.

I am, however, a bit lost on how you resolved the problem. Could you please clarify what you did to enable you to sort a column according to percentage (not the actual values)?

You say the solution is to "set all the sort options to Manual in the advanced sort section". Unfortunately, I am not sure how to go about this.
On the ribbon, under the Data tab, in the "Sort & Filter" section, I can see an "advanced" button. Is this what you mean?
The problem is that the "advanced" button shades over grey (as unavailable) when I select any cell within my pivot table...so I'm not sure how it could work....

Hope you can help (3 years later!)

Thanks



Quote:
Originally Posted by Pat View Post
I agree it had nothing to do with the % of row. However the feature is not
working as expected and there may still be a bug.
The way I discover the solution is that if you change the field from "% of
row" to "% Different from (Previous)" you actually get an error (warning)
that it is not going to work "AutoSort and AutoShow cannot be used with
custom calculations that use positional reference. Do you want to turn off
AutoSort/Show?" This does not appear in the "% of Row" situation. The bug is
that weather you choose Yes or No the data still will not sort unless you
turn off the AutoSort from the More Sort Options on the on the row header.
Being able to choose Yes and have the option automatically turned off would
be helpful. In addition the setting only applies to the current row header.
If you pivot on a different row the setting does not stay and needs to be
reapplied.


"Shane Devenshire" wrote:

> Hi,
>
> Well the reason it didn't happen in 2003 is 2003 didn't have all the option
> (power) of 2007. Also, this problem really had nothing to do with the % of
> Row option.
>
> --
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
> "Pat" wrote:
>
> > 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.
  #9  
Old November 18th 16, 04:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Excel Pivot Table How to Sort data as Percent of Row

Wow. I've been looking for the answer to this for DAYS. So glad I found your post, explanation and simple solution. Thanks!
  #10  
Old November 19th 16, 09:34 AM
bulong bulong is offline
Banned
 
First recorded activity by ExcelBanter: Nov 2016
Posts: 7
Default

up phụ bác chủ dùng khá l* ok mua mấy cái rồi mai chuyển em cái nữa nhé
_________________
kinky videos

https://t.co/Kum5dsnOzx
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table Data Column Sort Excel 2007 unclemuffin Excel Discussion (Misc queries) 1 October 31st 08 12:58 PM
Do not sort data alphabetically in a Pivot table Denise Charts and Charting in Excel 1 October 17th 08 08:10 PM
How do I sort pivot table data outside a pivot table Michael Excel Worksheet Functions 1 January 4th 07 02:45 PM
Pivot table question: How to display total and percent for data simultaneouly [email protected] Excel Discussion (Misc queries) 1 January 18th 06 07:12 PM
how do i automatically sort data in a pivot table Esche Excel Worksheet Functions 1 June 11th 05 12:43 PM


All times are GMT +1. The time now is 02:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.