Pivot Table Report Filter Sort Question

ExcelInNC

New Member
Joined
Dec 3, 2008
Messages
3
I have Pivot Table (Excel 2007) that contains a Report Filter of Employee Names and Hours Worked. When I append new data for new employees, how do I sort the entries in the Pivot Table Report Filter to include the new employees in Alphabetical Order. Currently, the new employees are at the bottom of the Filter selection drop down list.

The first time I created the table, they were in Alphabetical Order, no matter how the underlying data was sorted.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Pivot table options, sort A-Z, by employee name (or whatever is your column name)

should then put them in to alphabetical order.....
 
Upvote 0
I haven't found a (non-VBA) way of doing it without dragging the field into a Row/Column area, sorting (make sure Sort Options > More Options > Autosort is ticked) and then putting it back into the Report filter.
 
Upvote 0
I'm not so sure. The answer from HOLYSPIRITINME is the same as Stu Dapples', and the answer from AndrewTronick applies to Row fields, as per my answer.
 
Upvote 0
What appears to be working best is:
Move the Report Filter Field to the 1st Column in the Pivot Table.
Sort the Column A-Z
Refresh
Then move the field back to the Report Filter Field
Refresh

This worked with Skill Type Codes (with leading zeros) and Department Names.

It also sorted with the Report Filter having multiple items selected. When the field was moved back to the Report Filter, all of the selected items were still correctly selected.

Make sure that Manual Sort option is not checked.
 
Upvote 0
:) Thought so.

Maybe someone else has found a way though.

It seems to be an omission from the functionality, and it's a pain if the size of your Pivot Table is important, since adding the field to the Row area makes the Pivot Table one column wider.
 
Upvote 0
Below code can be used to sort filter fields in all pivot tables available in a workbook
<------------------------------------------------------------------->

Sub SortFilters()
Dim pFld As PivotField
Dim pvt As PivotTable

'Loop all sheets
For Each sh In ThisWorkbook.Worksheets
'Loop all pivot tables
For Each pvt In sh.PivotTables
'Loop all pivot filters
For Each pFld In pvt.PageFields
'sort it ascenting
pFld.AutoSort xlAscending, pFld.Name

Next

Next

Next
End Sub

<------------------------------------------------------------------------>
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top