Home > Pivot > Layout > Sort Macros Pivot Table Sorting Macros I do lots of work with pivot tables, and wrote these macros to save time with pivot table sorting. Quickly sort all pivot fields, or sort the Report Filter items. Or, sort in custom order, based on a worksheet list.

Sort Pivot Fields With a Macro

The following macro will change the Sort setting to Ascending (A-Z), for all row and column fields, in all pivot tables, on all worksheets, in the active workbook. The sort order is based on the item names, so "East" would come before "West".

Note: To sort Report Filter fields with a macro, go to the Report Filter macros section.

Sub AutoSortAZAllFields()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
    
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    For Each pf In pt.RowFields
      pf.AutoSort xlAscending, pf.SourceName
    Next pf
    For Each pf In pt.ColumnFields
      pf.AutoSort xlAscending, pf.SourceName
    Next pf
  Next pt
Next ws

End Sub

Sort Pivot Fields by Values - Macros

The following macros will sort all the Row fields in the selected pivot table, based on the amounts in one of the pivot table's Value fields

• The first 2 macros sort by a specific Value field - Sum of Total
• The next 2 macros sort by the selected Value field. There are versions for
  • Normal Pivot Tables only
  • Normal and/or Data Model Pivot Tables

Sort by Specific Value Field

The following 2 macros will sort all the Row fields in the selected pivot table, based on the values in the "Sum of Total" field.

• The first macro sorts Largest to Smallest (Z-A)
• The second macro sorts Smallest to Largest (A-Z)

NOTE: Before you run the macros, select any cell in the pivot table that you want to sort.

Sub SortAllRowFields_ZA()
Dim pt As PivotTable
Dim pf As PivotField
Dim strVal As String
strVal = "Sum of Total"

On Error Resume Next
Set pt = ActiveCell.PivotTable
If pt Is Nothing Then Exit Sub

For Each pf In pt.RowFields
  pf.AutoSort xlDescending, strVal
Next pf

End Sub
'===========================
Sub SortAllRowFields_AZ()
Dim pt As PivotTable
Dim pf As PivotField
Dim strVal As String
strVal = "Sum of Total"

On Error Resume Next
Set pt = ActiveCell.PivotTable
If pt Is Nothing Then Exit Sub

For Each pf In pt.RowFields
  pf.AutoSort xlAscending, strVal
Next pf

End Sub

Sort by Selected Value Field - Normal Pivot

The following 2 macros will sort all the Row fields in the selected pivot table, based on the values in the selected Value field. (For pivot tables based on the Data Model, go to the next section)

• The first macro sorts Largest to Smallest (Z-A)
• The second macro sorts Smallest to Largest (A-Z)

At the end of the macro, a message shows which Value field the sort was based on.

NOTE: Before you run the macros, select any amount in the Value column that you want to sort by.

Sub SortAllRowFields_ZASelVal()
'select the Value field that
' the sort will be based on
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim strVal As String

On Error Resume Next
Set pt = ActiveCell.PivotTable
If pt Is Nothing Then Exit Sub
Set df = ActiveCell.PivotField

If df.Orientation <> xlDataField Then
  MsgBox "Please select a Values field"
  Exit Sub
Else
  strVal = df.Caption
End If

For Each pf In pt.RowFields
  pf.AutoSort xlDescending, strVal
Next pf

MsgBox "Row fields were sorted Z-A " _
  & vbCrLf _
  & "based on the Value field: " _
  & vbCrLf _
  & strVal

End Sub
'===========================
Sub SortAllRowFields_AZSelVal()
'select the Value field that
' the sort will be based on
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim strVal As String

On Error Resume Next
Set pt = ActiveCell.PivotTable
If pt Is Nothing Then Exit Sub
Set df = ActiveCell.PivotField

If df.Orientation <> xlDataField Then
  MsgBox "Please select a Values field"
  Exit Sub
Else
  strVal = df.Caption
End If

For Each pf In pt.RowFields
  pf.AutoSort xlAscending, strVal
Next pf

MsgBox "Row fields were sorted A-Z " _
  & vbCrLf _
  & "based on the Value field: " _
  & vbCrLf _
  & strVal

End Sub

Sort by Selected Value Field - Normal or DM

The following 2 macros will sort all the Row fields in the selected pivot table, based on the values in the selected Value field. This macro works with normal or Data Model pivot tables

• The first macro sorts Largest to Smallest (Z-A)
• The second macro sorts Smallest to Largest (A-Z)

At the end of the macro, a message shows which Value field the sort was based on.

NOTE: Before you run the macros, select any amount in the Value column that you want to sort by.

Sub SortAllRowFieldsDM_ZASelVal()
'select the Value field that
' the sort will be based on
' for normal or Data Model PTs
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim strVal As String

On Error Resume Next
Set pt = ActiveCell.PivotTable
If pt Is Nothing Then Exit Sub
Set df = ActiveCell.PivotField

If df.Orientation <> xlDataField Then
  MsgBox "Please select a Values field"
  Exit Sub
Else
  If pt.PivotCache.OLAP = True Then
    strVal = df.Name
  Else
    strVal = df.Caption
  End If
End If

For Each pf In pt.RowFields
  pf.AutoSort xlDescending, strVal
Next pf

MsgBox "Row fields were sorted Z-A " _
  & vbCrLf _
  & "based on the Value field: " _
  & vbCrLf _
  & strVal

End Sub
'===========================
Sub SortAllRowFieldsDM_AZSelVal()
'select the Value field that
' the sort will be based on
' for normal or Data Model PTs
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim strVal As String

On Error Resume Next
Set pt = ActiveCell.PivotTable
If pt Is Nothing Then Exit Sub
Set df = ActiveCell.PivotField

If df.Orientation <> xlDataField Then
  MsgBox "Please select a Values field"
  Exit Sub
Else
  If pt.PivotCache.OLAP = True Then
    strVal = df.Name
  Else
    strVal = df.Caption
  End If
End If

For Each pf In pt.RowFields
  pf.AutoSort xlAscending, strVal
Next pf

MsgBox "Row fields were sorted A-Z " _
  & vbCrLf _
  & "based on the Value field: " _
  & vbCrLf _
  & strVal

End Sub

Sort All Report Filter Fields

Sometime the items in a pivot field get out of alphabetical order -- usually if new items have been added to the data, and they appear at the end of the list. This macro will sort the items in each Report Filter.

Tip: You can manually sort the report filter fields, by moving them to the row area,sorting, and then moving them back.

Sort Report Filters Macro Code

To use this code in your workbook, copy the code below into a regular code module. The code is also available in the Sort Report Filters sample file

Sub SortReportFilters()
'downloaded from www.contextures.com
'sorts all report filter fields
'in first pivot table
'on active sheet
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim lPF As Long
Dim lCount As Long
Dim lPos As Long
Set ws = ActiveSheet
Set pt = ws.PivotTables(1)

lPF = pt.PageFields.Count
For lCount = 1 To lPF
  Set pf = pt.PageFields(lCount)
  lPos = pf.Position

  With pf
    .Orientation = xlRowField
    .AutoSort xlAscending, pf.Name
    .Orientation = xlPageField
    .Position = lPos
  End With
Next lCount
End Sub

Sort Pivot Items Based on List

The following macro will sort the Product field in the Orders pivot table, based on a list in another sheet. The list is named (ProdList).

• If the sort list contains items that are not in the pivot field, or are not visible in the pivot table, those items will be ignored.
• Any items that are not in the sort list, but are in the pivot field, will drop to the end of the sorted pivot items.

Sub SortPT()
Dim rngSort As Range
Dim c As Range
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim lCount As Long
On Error Resume Next
Application.EnableEvents = False

Set rngSort = Sheets("Orders") _
    .Range("ProdList")
Set pt = Worksheets("OrdersPT") _
    .PivotTables("ptOrders")
Set pf = pt.PivotFields("Product")
lCount = 1

pt.ManualUpdate = True
With pf
  .AutoSort xlManual, pf.SourceName
  For Each c In rngSort
    Set pi = Nothing
    Set pi = .PivotItems(c.Value)
      If Not pi Is Nothing Then
        If pi.Visible = True Then
          pi.Position = lCount
          lCount = lCount + 1
        End If
      End If
  Next c
End With

pt.ManualUpdate = False
pt.RefreshTable
Application.EnableEvents = True
End Sub

Get the Sample Files

1. Sort Report Filters -- Download the Sort All Report Filter Fields sample file. To see the code, right-click the button on the Sales Pivot sheet, and click Assign Macro, then click Edit.
2. Macro - Sort Value: To test the Sort Based on a Value macros, download the Sort Based on Values file. The zipped file is in xlsm format, and contains macros from this page.
3. Macro - Sort List: To test the Sort Based on a List macro, you can download the Sort Based on List file. The zipped file is in xlsm format, and contains macros.

More Tutorials

Pivot Table Sorting Tips

Report Filters

Manually Move Pivot Items

Clear Old Items in Pivot Table