Pivot table sort order

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

Hello, My pivot table (on a seperate sheet in V2003) seems to sort my data
in an entirely random order. It is not in the order that the iems appear in
the original data, and it is not in size or alphabetical order, and what's
more, I can't change the order, even if I use 'Sort & Top 10' or Advanced
'Field Settings.

Why is this?

How can I determine the order? - Ideally it would be in the order tht the
iems appear, but if that's not possible, I could fiddle it, if I could sort
it at all!

Thanks
M
 
My observation was that tinkering with 'Sort & Top 10', selecting a mixed
data field from the droplist > Ascending, would sort it in the usual order,
viz. by real nums, then by text nums, then by alphas .. Give it another try?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
The Field Settings option to sort Ascending, Descending, or Manual should
sort the data as you specify. You might also try refreshing the pivot table
(when you select the PT you should see a new PT toolbar pop-up, just select
the red exclamation symbol to refresh your PT).

If you're saying the items in your filter (the drop down list in the field's
header) are not sorting, that's an easy fix:
- drag it to the row area if it's not already there
- select the cell for that filter/header which is not sorting properly
- in Excel's toolbar hit the A>Z sort button
- drag it back to the column or page area if needed

If all else fails, this code from contextures.com will sort all your PT
fields. Just right-click the Excel icon in the top toolbar, select "View
Code," paste this in and run it using the green play button, then delete the
macro and close the Visual Basic Editor window.

Sub SortAllFields()
On Error Resume Next
Application.ScreenUpdating = False

Dim pt As PivotTable
Dim ws As Worksheet
Dim pf As PivotField

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.ManualUpdate = True

For Each pf In pt.PivotFields
pf.AutoSort xlAscending, pf.Name
Next pf
pt.ManualUpdate = False
Next pt
Next ws

Application.ScreenUpdating = True

End Sub
 
Back
Top