Pivot Table Issues

How to Sort a PivotTable by Sum or Total Values in Excel

John Wilson Oct 8, 2026 869 views

Question details

The user wants to sort an Excel PivotTable by the sum or grand total values, but finds that the data only sorts within individual row groupings instead of applying the sort to the entire table.

How to Sort a PivotTable by Sum or Total Values

Product: Excel
Device & OS: not provided
Scenario: Organizing and analyzing grouped data in a PivotTable.
Observed behavior: The PivotTable sorts values only within individual row groupings when multiple fields are present, instead of sorting the entire table by the grand total.

Before you start

Ensure your PivotTable is refreshed to include the latest data, and check that you have Grand Totals enabled in your PivotTable design settings.

Solution 1Recommended

Sort Values Within Each Row Grouping

Apply sorting to each row field individually to organize subtotals within their respective groups.

By default, PivotTables respect the hierarchy of your nested fields. When you apply a sort to a value column, Excel sorts the items within the lowest grouping level.

Select a value to sort

Right-click on any numeric value in the column you want to sort within your PivotTable.

Apply the sort order

Select 'Sort' from the context menu, then choose 'Sort Largest to Smallest' or 'Sort Smallest to Largest'.

Repeat for nested fields

To ensure all levels are ordered properly, repeat this right-click and sort process for each nested row field grouping.

Grouping limitations: This method sorts the subtotals for that specific grouping only. The entire table cannot be sorted by the sum of the values without removing the other groupings.

Solution 2

Ungroup Fields to Sort the Entire Dataset

Remove secondary row fields to allow the PivotTable to sort the entire dataset by the total value without sub-group interruptions.

Solution 3

Add a Total Column as the Leftmost Field

Create a workaround by placing a net amount or total field on the left side of the PivotTable to force an overall sort.

Efficient Data Analysis with WPS Spreadsheet

Easily Create and Sort PivotTables in WPS Office

WPS Spreadsheet provides robust PivotTable features, allowing you to summarize, group, and sort large datasets by total values effortlessly. It offers seamless compatibility with Microsoft Excel files, ensuring your data analysis workflows remain uninterrupted.

1. Insert a PivotTable: Open your dataset in WPS Spreadsheet, select your data range, go to the 'Insert' tab, and click 'PivotTable'.
2. Arrange your fields: Drag your desired data fields into the Rows and Values areas in the PivotTable pane on the right.
3. Sort within groupings: Right-click the value column you wish to sort, select 'Sort', and choose your preferred sorting order to arrange subtotals.
4. Sort by grand total: To sort the entire table by grand totals, easily drag out and remove nested row fields directly from the pane to eliminate sub-groupings.

Fully compatible with Microsoft Excel (.xlsx) PivotTables and data models.Intuitive drag-and-drop interface for building and sorting complex PivotTables.Lightweight and fast, even when processing large datasets with multiple groupings.Free to download and use with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my PivotTable not sorting correctly by grand totals?

When multiple row fields are grouped together, PivotTables prioritize the grouping hierarchy and only sort the subtotals within each specific group. To sort the entire table by the absolute grand total, you must remove the secondary sub-groupings.

Can I sort a PivotTable horizontally by column totals?

Yes, you can sort horizontally. Right-click a value in the Grand Total row at the bottom of your PivotTable, select 'Sort', click 'More Sort Options', and change the sorting direction to sort left to right.

How do I show Grand Totals if they are missing in my PivotTable?

Click anywhere inside your PivotTable, go to the 'Design' tab on the top ribbon, click on the 'Grand Totals' button, and select 'On for Rows and Columns'.

Excel & Data Problems