How to: Sort Items in a Pivot Table

• Apr 01, 2021
• 3 minutes to read

The examples below demonstrate how to sort items in PivotTable fields by labels and summary values.

Sort Item Labels

To sort items in a specific PivotTable field in ascending or descending order, assign the corresponding PivotFieldSortType enumeration member to the PivotField.SortType property.

For example, the following code sorts items of the "Product" field in ascending order.

View Example

PivotFieldActions.vb

Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet

' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Access the pivot field by its name in the collection.
Dim field As PivotField = pivotTable.Fields("Product")
' Sort items in the "Product" field. 
field.SortType = PivotFieldSortType.Ascending

PivotFieldActions.cs

Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the pivot field by its name in the collection.
PivotField field = pivotTable.Fields["Product"];
// Sort items in the "Product" field. 
field.SortType = PivotFieldSortType.Ascending;

Sort Items by Summary Values

Besides sorting item labels, you can also sort items in a row or column field based on values in the data area of the PivotTable report. To do this, call the PivotField.SortItems method for the field you wish to sort and pass the following parameters.

• A PivotFieldSortType enumeration member that specifies the sort order (ascending or descending).
• A zero-based index of the data field in the PivotDataFieldCollection collection by which items in the field should be sorted.
• An array or collection of the PivotItemReference objects that represent references to specific PivotTable items describing a particular row or column in the data area containing values to sort by. If this parameter is not specified, items in the field are sorted by their grand total values.

Note

If you try to apply sorting to a field that is not currently shown as a row or column field in the report, or the sort settings you specify are invalid, an exception occurs. To view sort options applied to a specific field, use the field’s PivotField.Sort property.

The following code example shows how to sort products in descending order by sales values in the first quarter.

C#

// Access the pivot table by its index in the collection.
PivotTable pivotTable = worksheet.PivotTables[0];

// Create a reference to the "Q1" item of the "Quarter" field.
PivotItemReference item = new PivotItemReference(0, 0);
// Sort the "Product" field by the "Sum of Amount" field in descending order using values in the "Q1" column.
pivotTable.Fields["Product"].SortItems(PivotFieldSortType.Descending, 0, item);

VB.NET

' Access the pivot table by its index in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables(0)

' Create a reference to the "Q1" item of the "Quarter" field.
Dim item As New PivotItemReference(0, 0)
' Sort the "Product" field by the "Sum of Amount" field in descending order using values in the "Q1" column.
pivotTable.Fields("Product").SortItems(PivotFieldSortType.Descending, 0, item)

The following image shows the result of the code’s execution.