To sort an Excel pivot table by its Sum column, right-click any value in that column, choose Sort, then select Sort Largest to Smallest (or Smallest to Largest). This instantly reorders the row labels based on the numeric totals in the Sum column, and it works for both the Grand Total column and any individual subtotal column.
Why Sorting by Sum Matters
Pivot tables are built for summarizing large datasets, but the default order of row labels is alphabetical (or by the original data order for dates). When you have dozens of categories—say, product names, regions, or salespeople—scanning an alphabetical list to find the top performer is slow. Sorting by the Sum column puts the highest or lowest totals at the top, turning the pivot table into an instant ranking. This is one of the most common requests from Excel users because it directly answers questions like “Which product sold the most?” or “Which region has the lowest expenses?”
How to Sort by the Sum Column (Step-by-Step)
Here’s the exact process in Excel for Microsoft 365 on Windows:
- Click anywhere inside the pivot table to activate the PivotTable Tools on the ribbon.
- Locate the column that shows the Sum values (for example, Sum of Sales).
- Right-click on any numeric cell in that column (not the header, but a data cell).
- Hover over Sort in the context menu.
- Choose Sort Largest to Smallest to rank descending, or Sort Smallest to Largest for ascending.
On a Mac, the steps are identical: right-click (or Control-click) a value cell, go to Sort, and pick the order. In Excel for the web, right-clicking a value cell also shows the Sort option with the same two choices. If you prefer the ribbon, you can select the column header cell and use Data > Sort, but the right-click method is faster and works on all platforms.
Sorting by a Specific Subtotal Column
If your pivot table has multiple value fields (e.g., Sum of Sales and Sum of Profit), you can sort by any of them. Right-click a value in the Sum of Profit column and choose Sort Largest to Smallest—the row labels will reorder based on that column’s totals, not the first column. This is useful when you want to rank by a secondary metric. Note that the sort applies to the entire pivot table, not just the visible columns, so the row labels are rearranged globally.
Common Problems and Misconceptions
A frequent issue is that the sort option is grayed out or doesn’t work. This usually happens when you right-click the column header instead of a data cell. The header cell (which displays “Sum of Sales”) doesn’t have the Sort option; you must click a number in the column. Another misconception is that sorting by the Grand Total column works differently—it doesn’t. The Grand Total column is just another column of values, so right-clicking any cell in it and sorting works the same way.
Another problem: if your pivot table has grouped fields (like dates grouped by month), sorting by the Sum column may not reorder the groups as expected. For date groups, Excel keeps chronological order by default, and the Sort option on the Sum column may be disabled. To sort grouped dates, you need to ungroup or use a different approach, such as sorting in the source data.
Sorting by Sum vs. Manual Row Order
Excel offers several ways to order rows in a pivot table, and it’s important to know the difference:
- Sort by Sum column – Reorders rows based on the numeric totals in a value field. This is dynamic: if the source data changes and you refresh the pivot table, the sort order updates automatically.
- Manual drag – You can click and drag row labels to a custom position, but this creates a static order that doesn’t respond to data changes.
- Sort by row label – Right-click a row label (e.g., “Product”) and choose Sort A to Z or Z to A. This sorts alphabetically, ignoring the Sum values.
For most ranking tasks, sorting by the Sum column is the right choice because it’s automatic and reflects the latest data.
Using Sort Options for Advanced Control
Beyond the right-click menu, you can access more sort settings via the Field Settings dialog. Click on the row label field (e.g., “Product”) in the PivotTable Fields pane, then go to PivotTable Analyze > Field Settings (or right-click the field and choose Field Settings). In the dialog, go to the Layout & Print tab and click Advanced. Here you can choose “Descending” or “Ascending” and select the field to sort by—this is useful when you want to sort by a specific value field that isn’t currently displayed. For example, you could sort by “Sum of Profit” even if only “Sum of Sales” is shown in the pivot table.
Frequently Asked Questions
Why can’t I sort by the Sum column?
Most likely you right-clicked the column header or a cell outside the values area. Click a number in the Sum column, then right-click. If the option is still grayed out, check that your pivot table isn’t in Compact Layout with multiple row fields—sometimes you need to right-click a value in the innermost column.
Does sorting by Sum affect the underlying data?
No. Sorting a pivot table only changes the display order of rows; it does not alter your source data or the calculations. The totals remain the same, just reordered.
Can I sort by the Sum column when there are multiple row fields?
Yes. Right-click a value in the Sum column and choose Sort. The sort will apply to the outermost row field by default. To sort by an inner field, you may need to use the Field Settings Advanced option and select the specific field.
How do I sort by the Grand Total column when it’s not visible?
If your pivot table doesn’t show a Grand Total column, go to PivotTable Analyze > Options > Totals & Filters and check “Show grand totals for columns.” Once visible, you can sort by it like any other column.
Will the sort order stay after refreshing the pivot table?
Yes, the sort order is preserved when you refresh, as long as the sort is based on a value field. If the data changes and a different row becomes the largest, the order updates accordingly.
Alternatives to Sorting by Sum
If you need to rank items without using a pivot table, you can use the SORT function in Excel 365. For example, with data in A2:B20 (items in A, values in B), you can put =SORT(A2:B20,2,-1) in a cell to return the list sorted by column B descending. This is a dynamic array formula that updates automatically. For older Excel versions, you could use LARGE or INDEX/MATCH in a helper column, but the pivot table method is simpler for most users.
Another alternative is to use a regular Excel table with a filter: select the data, press Ctrl+Shift+L to add filter arrows, then sort by the value column. This works for simple lists but lacks the aggregation power of a pivot table.
Related
- Excel Normalize Data Range: 3 Easy Methods
- Excel Name Manager Dynamic Range Explained
- Excel Mobile Repair Grand Rapids: Fast Phone Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Gantt Chart: Exclude Weekends in Your ScheduleYes. To make an Excel Gantt chart exclude weekends, build its timeline from working dates rather than consecutive…
- Excel Waterfall Chart: How to Change the Legend TextIn Excel for Microsoft 365 on Windows, you generally cannot rename the built-in Increase, Decrease, and Total legend…
- Excel Speedometer Chart: Build One and Make It WorkAn Excel speedometer chart is a dashboard-style gauge that shows a value on a semicircular scale; Excel does…
- Excel IFS in Pivot Tables: How to Add and Summarize ItYes, you can use an IFS formula with an Excel PivotTable, but the dependable method is to put…
- Excel Pivot Table: Add a Field to Filters and RowsTo add a PivotTable field to both a filter and a row, open the PivotTable Fields pane and…
- Excel Pivot Table: How to Sort Items in the Filter ListIn Excel for Microsoft 365 on Windows, open the PivotTable field’s drop-down and choose Sort A to Z…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.