Home > Pivot > Layout > Sorting Pivot Table Sorting Fixes & TipsHow to fix Excel pivot table sorting problems - show new items in correct order, fix wrong item at top of list. Workaround to sort Report Filter items. Best sorting tips - sort data by values, sort left to right, sort in custom order. Easy steps, and sample macros to help you with pivot table sorting. |
Video: Sort Pivot Table Report FilterAfter you refresh a pivot table, new items might appear in the Report Filter drop down lists. If the new items are not in alphabetical order, you can follow the steps in the short video, to sort them. Sort Report Filter ItemsIf you add new records to fields that are in the pivot table's Report Filter, any new items might appear at the end of the list, instead of in alphabetical order. You can sort the items in alphabetical order, either manually (as described below), or with programming (get the report filter sorting macro here). New Items at End of ListIn this example, the Product field is in the Report Filter area, and Binders is a new product in the source data. When the pivot table was refreshed, Binders appeared at the bottom of the Product list, instead of the top. Fix the Report Filter Sort OrderIf a field is set for Manual sort, new items will appear at the end of the drop-down list. You can't sort the fields that are in the Report Filter area, so move them temporarily to the Rows area. Follow these steps to sort the Report Filter field in ascending order:
When you sort the Product field, its sort setting changes from Manual to Sort Ascending or Sort Descending. This also sorts the drop-down list, and makes it easier for users
to find the items they need. |
Sort Pivot Field in Custom OrderMost of the time, you'll sort a pivot table using one of these options:
However, in Excel you can also sort items based on a Custom List, with a custom sort order. Custom Sort OrderExcel has a few built in Custom Lists that you can use, for custom sort options:
Create a Custom ListYou can also create your own custom lists, such as department names or cities, and specify a unique sort order for those lists. To create a custom list, follow the instructions here: Create a Custom List Custom Lists Sort Order for Pivot TablesIn an Excel pivot table, you can control whether the Excel custom lists are used:
Apply Custom Sort OrderAfter you create a custom list, follow these steps to apply a custom sort order to a pivot table field:
Prevent Custom Order Sorting in a Pivot TableIn some cases, you might not want the custom list sort order used in a pivot table. For example, if a salesperson's name is Jan or May, those names would appear at the top of a list, before other names, such as Ann or Bob. To prevent the custom lists from taking precedence when sorting a pivot table, follow these steps to change the setting:
NOTE: This setting will apply to all the fields in the pivot table. |
Show Values As - Sorting ProblemIn a pivot table, you can apply a "Show Values As" setting to the numbers. These settings show custom calculations, such as Percent Of, or Difference From. Some calculations, like the "Difference From" shown below, have two drop-down lists, Base Field and Base Item. In the Base Item drop-down list, there are two types of items:
Error Message for AutoSortIf you select a positional reference, you might see an error message like the one shown below. Here is the text from that message:
This error message is confusing, because it uses terms from older versions of Excel. Here are the old terms, and what they're currently called:
Click Yes or NoHere's what happens when you click one of the error message buttons:
|
Pivot Field Sort OptionsTo avoid the Excel error message, use the More Sort Options command, if your pivot table has values with positional references -- (previous) or (next). Here are the steps, for a base field, Region, and value field, Diff Units:
More Sort OptionsFollow these steps, when the More Sort Options (Region) dialog box opens:
|
Sort Pivot Fields With a MacroThe 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". To sort Report Filter fields with a macro, go to the Report Filter macros page. 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 - MacrosThe 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
Sort by Specific Value FieldThe following 2 macros will sort all the Row fields in the selected pivot table, based on the values in the "Sum of Total" field.
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 PivotThe 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)
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 DMThe 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
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 |
Get the Sample File
More Tutorials |
Last updated: October 27, 2023 3:23 PM