Excel Pivot Table Sorting Macro Data Model

When you add fields to the pivot table row area, you can sort the items alphabetically, or by the numbers in the Values area. The sorting can be done manually or with a macro. See below for an Excel pivot table sorting macro. Data Model pivot tables can be sorted with it too.

Pivot Table Sorting Rules

In the Pivot Table Row area, the pivot items can be sorted:

by their labels, A-Z or Z-A
by a Value field, in Largest to Smallest or Smallest to Largest.

Keep these pivot table sorting rules in mind:

If you’re sorting the first pivot field at the left, all its pivot items will be sorted together in the order that you selected.
If you’re sorting an inner pivot field, the pivot items will be sorted within its subheading.

Sorting Example

For example, this pivot table has Category and Product in the Rows area. When the Quantity field is sorted smallest to largest

Bars products are sorted smallest to largest
Below that, the Cookies products are sorted smallest to largest

Pivot Table Sorting Macros

If you’re automating a pivot table project, you can use macros to sort the row fields. There are two macros below, that you can copy to your workbook, and use. There are instructions for copying the code on my Contextures website.

The first macro – SortAllRowFields_ZA – sorts by the values in the “Sum of Total” field, largest to smallest.

NOTE: Before you run the macro, select a 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

'==========================

Pivot Table Sorting Macro Selected Value Field

The following macro 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 code checks the pivot cache, to see if it is OLAP-based (Data Model).

If True (data model), the macro uses the field’s name in the code
If False (normal pivot table), the macro uses the field’s caption in the code

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

'==========================

Get the Sample File

To test the macros, and to see other macros and pivot table sorting tips, go to the Excel Pivot Table Sorting page on my Contextures site.

There is a sample file without macros (xlsx format), and two sample files with macros for sorting pivot tables.

______________________________