When a pivot field is in the row or column area, you can sort the pivot items in that fields, using the built-in commands. For fields in the Report Filter area, there isn’t an easy way to sort the pivot items. I’ll show you how to sort manually, or use the pivot table report filter sort macro below to do the job quickly and easily
Row and Column Sorting
Here’s a screen shot of a pivot field named City, which is in the Row area of a pivot table. Currently, the city names are not in alphabetical order. To sort them:
- Click the arrow in the field heading
- Choose one of the Sort options, such as Sort A to Z
Read more about Pivot Table Sorting on my Contextures website.
Report Filter Fields
If I move the same field to the Report Filter area, the list of cities appears, for filtering, but there are no Sort options listed.
Read more about Report Filters on my Contextures website.
Sort a Report Filter Field Manually
If you just have one or two Report Filter fields to sort, follow these steps to manually sort them.
- Temporarily move the field to the Row or Column area
- Use the Sort options in the Row or Column field heading
- Drag the field back to the Report Filter area
It’s not the ideal solution, certainly, but it works well, and is a good solution for a quick one-off situation.
Use a Report Filter Sort Macro
If you have to sort the Report Filter fields frequently, or if you have several of them to sort, a macro will make the job much quick and easier.
Copy the macro code from the next section, and paste it into a regular code module in your Excel workbook.
Note: If you have a copy of my Pivot Power Premium add-in, go to Filter, and click Sort Report Filters (see the screen shot below). That will sort all the Report Filter fields in all pivot tables on the active sheet.
Report Filter Sort Macro Message
This macro will show a message that asks if you want to temporarily collapse the Row Fields. That will help prevent the pivot table from running out of rows, if the pivot table is large, and one or more of the Report Filter fields contains a high number of pivot items.
Click Yes, unless you have some of the row fields already collapsed, and you don’t want to lose that layout.
Report Filter Sort Macro Code
Copy the following code to a regular code module in your workbook. Then, select a sheet that contains a pivot table, and run the macro.
For more Report Filter macros, visit my Contextures website.
Sub SortReportFilterFields() Dim ws As Worksheet Dim pt As PivotTable Dim pf As PivotField Dim pfRow01 As PivotField Dim lRptPos As Long Dim lDrill As Long On Error Resume Next Set pt = ActiveSheet.PivotTables(1) Set pfRow01 = pt.RowFields(1) If pt.RowFields.Count > 1 Then lDrill = MsgBox("Collapse Row Fields temporarily?", _ vbQuestion + vbYesNo + vbDefaultButton1, "Collapse") End If If lDrill = vbYes Then pfRow01.DrillTo pfRow01.Name End If pt.ManualUpdate = True For Each pf In pt.PageFields lRptPos = pf.Position pf.DrillTo pf.Name pf.Orientation = xlRowField pf.AutoSort xlAscending, pf.SourceName pf.Orientation = xlPageField pf.Position = lRptPos pf.ShowDetail = True Next pf If lDrill = vbYes Then pfRow01.ShowDetail = True End If pt.ManualUpdate = False MsgBox "Report Filters have been sorted" End Sub