Sorting Hi to Low

[Jon Scorey]

Hi,
I apologize in advance if this question has been answered, I've looked and cant' find the answer. I am new to Pivot Tables, but have figured out how to create the database and the resulting Pivot Table. My Sales Database includes the Sales $ and GP$ for each line. In the Pivot Table I've managed to create a calculated field for GP %, no problem.

i have also managed to figure out how to express each Row as a % of the Pivot Total, also very easy.

What I need to know is how can I sort my Pivot Table High to Low using these calculated fields?

Can anyone help?

Thx
Jon

 

[Andrew Poulsom]

Welcome to the Board.

Right click the Row field and choose Field Settings. Click Advanced, check an AutoSort option, select the Field to use and click OK twice.

 

[Jon Scorey]

Hi Andrew, I'm sorry, but I don't see where to do this?
How do I show you my sheet?
Jon

 

[Andrew Poulsom]

What don't you understand?

 

[Jon Scorey]

Hi,

I have right clicked everywhere I can think of, found Field Settings, but I can't see advanced anywhere. This is a calculated field column (Value). Maybe the terminology is too new to me.

Jon

 

[Andrew Poulsom]

No, you need to right click on a field in the row area, like customer or product. You can then sort by a field in the data area.

 

[Jon Scorey]

I see exactly what you're saying, but it will not sort on my "Calculated" field. I can sort on all the fields that came from the Field List, but not the new ones.

I guess I'm stuck

thanks anyway
JOn

 

[Dougie1]

Do you need to display the data in a Pivot table form ??

If not - you could always copy and paste special values your pivot table onto a new sheet and then sort by your calculated field.

D

 

[Jon Scorey]

Yes, I really like the power of the Pivot Table. I know how to make this happen in a regular sheet.

THanks
Jon

 

[Andrew Poulsom]

That's strange. It worked for me:

<TABLE style="WIDTH: 238pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=317 border=0 x:str><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=74 height=17> </TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 64pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=85>Data</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=71> </TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=87> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Name</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Sum of Sales</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Sum of GP</TD><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Sum of Field1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>b</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="400">400 </TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="204">204 </TD><TD class=xl30 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="00.51">51.00%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>a</TD><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="350">350 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="128">128 </TD><TD class=xl31 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.36571428571428571">36.57%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>d</TD><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="600">600 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="152">152 </TD><TD class=xl31 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.25333333333333335">25.33%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>c</TD><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="500">500 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="73">73 </TD><TD class=xl31 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.14599999999999999">14.60%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Grand Total</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="1850">1,850 </TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="557">557 </TD><TD class=xl32 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="0.30108108108108106">30.11%</TD></TR></TBODY></TABLE>