Contextures

Excel Pivot Table Sorting

Fix pivot table items that appear in the wrong order, sort items in the Report filter, sort in a custom order, and other pivot table sorting tips

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

Pivot Table Items

Sort By Values

To quicky 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.

Pivot Table Items

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.

Pivot Table Items

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)

Pivot Table Items

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

Pivot Table Items

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

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 Report Filter Items 

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.

report filter items out of order

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.

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.

Sort a 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 a 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 Z to A

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.

Rows Alphabetical

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

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

More Sort Options

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

Sort by Value

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

pivot table row sorted

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

  • alphabetically, based on the row labels,
  • or by values.

You can also create custom lists, such as department names or cities, and specify a unique sort order for those lists.

Then, in your pivot table, you can use those lists as the sort order, instead of sorting A-Z.

To create a custom list, follow the instructions here: Create a Custom List

Applying the 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, that name 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.

Sort Pivot Fields With VBA

The following macro will change the Sort setting to Ascending (A-Z), for all pivot fields, in all pivot tables, on all worksheets, in the active workbook.

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

Download the Sample File

  • To test the techniques in this tutorial, you can download the sample file. The zipped file is in xlsx format, and does not contain macros.

More Pivot Table Resources

Tutorials:

Search Contextures Sites

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

 


Amazon.com 

 

 

Amazon.com

Last updated: November 16, 2016 4:39 PM