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.
 

Stu Dapples

Active Member
Joined
Jan 12, 2009
Messages
250
Pivot table options, sort A-Z, by employee name (or whatever is your column name)

should then put them in to alphabetical order.....
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,928
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.
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,928
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.
 

ExcelInNC

New Member
Joined
Dec 3, 2008
Messages
3
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.
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,928
:) 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.
 

Shashi1801

New Member
Joined
Feb 18, 2015
Messages
1
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

<------------------------------------------------------------------------>
 

Forum statistics

Threads
1,081,564
Messages
5,359,622
Members
400,540
Latest member
JimUSMC

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top