Sorting Data In Pivot Table

[Xenobion]

I've looked for a couple threads regarding this but I really wasn't able to find the answer.

I have a pivot table in Excel 2007. The data has been imported in excel from Access 2007.

The trouble I'm having is my numerical data isn't sorting the way I want it to. It sorts ascending/descending when I want it to sort smallest to largest.

Example:

<table width="783" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:14080;width:289pt" width="385"> <col style="mso-width-source:userset;mso-width-alt:14555;width:299pt" width="398"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt;width:289pt" width="385" height="20">Field 1
</td> <td class="xl66" style="width:299pt" width="398">Field 2
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">0800</td> <td class="xl66">10</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">100</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">110</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">120</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">130</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">140</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">150</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">160</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">170</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">180</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">190</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">20</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">30</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl67"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">40</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">50</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">60</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">70</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">80</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl67" style="height:15.0pt" height="20"> </td> <td class="xl66">90</td> </tr> </tbody></table>
As you can see it treats numbers as alphabetical text throwing all the "1" numbers first. Is there a strategy to either export from Access the fields as numerical, import and sort in excel as numerical, or anything else under the sun.

Thanks

 

[avandever]

in the pivot table field list on the right, if you select field2, select value field settings, then change the number format it may work? let me know. i have had a similar problem in normal tables sorting incorrectly...

 

[Xenobion]

in the pivot table field list on the right, if you select field2, select value field settings, then change the number format it may work? let me know. i have had a similar problem in normal tables sorting incorrectly...

If I move it to the values tab it then becomes a count which would be "1" for each value. I've tried playing with the values filters to see if it would actually display the number but most of the options are percentages.

 

[avandever]

i am on excel 2010 so im not sure if this area is different, but on mine you are able to change the number format(number, general, percentage, accounting, etc....) by clicking the number format button in the value field settings window. in the same value field settings window you are able to select sum, count, average, max, min, etc....

 

[avandever]

just looked at my co workers 2007 and it is exactly like 2010. you can go to the pivottable field list and click value field settings on field2, select how to summarize value field and also click the number format button in the lower left hand corner to categorize the data.

 

[Xenobion]

Okay I found the solution. I had to do a manual sort with field 2 by clicking and dragging the out of order numbers in order to correspond with my data.