How to: Sort Items in a Pivot Table
- 3 minutes to read
Sort Item Labels
For example, the following code sorts items of the "Product" field in ascending order.
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
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.
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.
// Access the pivot table by its index in the collection. PivotTable pivotTable = worksheet.PivotTables; // 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);
The following image shows the result of the code’s execution.