Home > Pivot > Layout > Sorting

Pivot Table Sorting Fixes & Tips

How 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.

Pivot Table Sorting Restrictions

In the Pivot Table Row area, pivot items can be sorted in either of the following ways:

• by their labels, A-Z or Z-A
• by one of the Value fields, in Largest to Smallest or Smallest to Largest.

However, the pivot table structure puts restrictions on what will be sorted:

• If there is NO pivot field to the left of the field that you are sorting, all the pivot items will be sorted together in the order that you selected.
• If there ARE fields to the left of the field that you are sorting, the pivot items will be sorted within each item of next field to the left.

Sort By Labels

To quickly sort a pivot field's items alphabetically:

• Click on any pivot item in the pivot field, to select that cell
• On the Data tab of the Excel Ribbon, click the A-Z command or the Z-A command

In the screen shot below, the Product field is in the row area, and a product name is selected, in cell A5. When the Z-A button is clicked, the product names are sorted in descending order.

Sort By Values

To quickly sort a pivot field's items by a value field:

• Click on any pivot item's value, to select that cell
• On the Data tab of the Excel Ribbon, click the A-Z command or the Z-A command

In the screen shot below, the Product field is in the row area, and value in the Orders field is selected, in cell C4. When the A-Z button is clicked, the Orders values are sorted in ascending order.

With Pivot Field to Left

In the examples above, there is no pivot field to the left of the product field. In that situation, all the pivot items are sorted together, so you can see the top and bottom values overall.

If there is a pivot field to the left of the field that you are sorting, the sorting is restricted by that outer field. The labels or values are only sorted within each of the items in the outer field -- the sorting does not occur overall.

In the screen shot below, the Product field is in the row area, and the Category field is to its left, as an Outer field.

The Product quantity in cell C5 is selected, and when the A-Z button is clicked, the Qty values are sorted in ascending order -- within each Category.

Independent Sorting

The outer field, Category, can also have its Qty values sorted, independently of the Product Qty values.

In the screen shot below, the Category quantity in cell C4 is selected, and sorted Z-A (largest to smallest). That moves Cookies to the top of the list, but the Products within each Category are still sorted A-Z (smallest to largest)

New Pivot Table Items Out of Order 

After you create a pivot table, new items might be added to the source data. When you refresh the pivot table, those new items might appear in the drop down lists, at the bottom of the list. Sometimes, the new items don't appear at all, if a filter is applied.

Watch this video to see how to solve problems with new items in a pivot table.

New Excel Pivot Table Items Out of Order 

When you add new records to your pivot table source data, and refresh the pivot table, new items might appear in the drop down lists.

Sometimes, those new items appear at the end of the list, instead of in alphabetical order. For example, in the screen shot below, binders were just added to this pivot table's source data.

When the pivot table was refreshed, Binders appeared at the bottom of the Product list, instead of the top.

Fix the Sort Order

Items in the pivot table drop down lists can get out of order, if the pivot field is set to Manual sort (the default setting). When a field is set for Manual sort, the pivot items are shown in alphabetical order at first, and you are able to drag the pivot items to a different position in the worksheet. Any new items will appear at the end of the drop-down list.

Follow these steps to sort pivot table field in ascending order:

1. Right-click a cell in the Product field. For example, right-click the Envelopes cell.
2. Click Sort, and then click Sort A to Z.

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.

Video: Sort Pivot Table Report Filter 

After 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 Items 

If 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 List

In 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 Order

If 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:

1. Drag the Product field into the Rows area, above any other row fields.
2. Right-click a cell in the Product field. For example, right-click the Envelopes cell.
3. Click Sort, and then click Sort A to Z.
4. Drag the Product field back to the Report Filter area.

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 Table Field Left to Right

Usually you sort a pivot table by the values in a column, such as the Grand Total column.

By sorting, you can highlight the highest or lowest values, by moving them to the top of the pivot table.

To sort pivot table column:

1. Right-click on a value cell, and click Sort.
2. Then, click Sort Smallest to Largest or Sort Largest to Smallest

Sort a Pivot Table Row

You can also sort a pivot table row by its values, left to right. This moves the largest or smallest amounts to the left of the pivot table, so you can focus on them.

For example, in the pivot table shown below, the cities columns are in alphabetical order, with Boston at the left.

To highlight the city with the highest sales, you can sort the Chocolate Chip row, so the sales per city are sorted in descending order.

Choose a Sort Option

To sort the pivot table row:

1. In the pivot table, right-click a value cell in the Chocolate Chip row.
2. Click Sort, and then click More Sort Options, to open a sort options dialog box

3. In the Sort By Value dialog box, under Sort Options, select the Largest to Smallest sort option.
4. Under Sort direction, select Left to Right. In the Summary section, you can see a description of the sort settings.

5. Click OK to close the dialog box.

After you sort the Chocolate Chip row, left to right, the values in the Chocolate Chip row are sorted largest to smallest, from left to right.

The City column order has changed, and Chicago, which has the highest Chocolate Chip sales, is at the left.

NOTE: Rows for other products may not be in descending order, because the column order has been set by the values in the Chocolate Chip row.

Sort Pivot Field in Custom Order

Most of the time, you'll sort a pivot table using one of these options:

• alphabetically, based on the row labels, A-Z or Z-A
• by numeric value - largest to smallest or smallest to largest

However, in Excel you can also sort items based on a Custom List, with a custom sort order.

Custom Sort Order

Excel has a few built in Custom Lists that you can use, for custom sort options:

• Full month names, January to December
• Short month names, Jan to Dec
• Full weekday names, Sunday to Saturday
• Short weekday names, Sun to Sat

Create a Custom List

You 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 Tables

In an Excel pivot table, you can control whether the Excel custom lists are used:

• turn the setting on, and allow Excel to use those custom lists as the sort order, instead of sorting A-Z
• turn off the setting, and prevent Excel from sorting based on a custom list

Apply Custom Sort Order

After you create a custom list, follow these steps to apply a custom sort order to a pivot table field:

1. Refresh the pivot table.
2. If the field which has items from a custom list is set for Automatic sort, it should change to the custom list's sort order.
3. If the field is currently set for manual sorting, it won't sort according to the custom list order. To change it to automatic sorting, right-click a city label, click Sort, and then click Sort A to Z.

Prevent Custom Order Sorting in a Pivot Table

In 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:

1. Right-click a cell in the pivot table, and click PivotTable Options.
2. In the PivotTable Options dialog box, click the Totals & Filters tab.
3. In the Sorting section, remove the check mark from "Use Custom Lists When Sorting"

NOTE: This setting will apply to all the fields in the pivot table.

Show Values As - Sorting Problem

In 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:

• Positional reference: (previous) or (next)
• Specific reference: pivot items in the selected Base Field.

Error Message for AutoSort

If you select a positional reference, you might see an error message like the one shown below.

Here is the text from that message:

• Error Description: AutoSort and AutoShow can't be used with custom calculations that use positional references. Do you want to turn off AutoSort/AutoShow?
• Buttons: Yes and No

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:

• Custom Calculations - now called Show Values As
• AutoSort - now called Sort Options
• AutoShow - now called Top Ten Filters

Click Yes or No

Here's what happens when you click one of the error message buttons:

• No: Excel will discard your Show Values As settings, instead of adding that change to the selected values column.
• Yes: Excel will add your Show Values As setting to the column.
  • However, that error message will appear again, when you try to sort any of the value columns, using an A-Z command, or Sort Options in the right-click menu.
  • Instead, use the More Sort Options command, in base field's heading cell. There are steps in the next section

Pivot Field Sort Options

To 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:

• In the Region field's heading cell, click the drop-down arrow
• In the list of commands, click More Sort Options
  • The Sort (Region) dialog box opens
• In the Sort options section, click the radio button for Ascending or Descending
• In the drop down list, select the value field you want to sort - Diff Units in this example.
• Next, click the More Options button

More Sort Options

Follow these steps, when the More Sort Options (Region) dialog box opens:

• At the top, in the AutoSort section remove the check mark for "Sort automatically every time the report is updated.
• Next, in the Sort By section, click the radio button for Values in selected column
• Click in the box, then go to the worksheet, and click a cell in the column that you want to sort
• The Summary section shows the sort settings that you have selected.
• Click OK, to close the More Sort Options dialog box.
• Finally, click the OK button, to close the Sort dialog box, and to apply the sort settings

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".

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 - 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 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 File

1. Pivot Table Sorting: To test the techniques in this tutorial, you can download the pivot table sorting sample file. The zipped file is in xlsx format, and has NO macros.
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

Manually Move Pivot Items

Clear Old Items in Pivot Table

Report Filters