Yes, you can sort a pivot table by the Grand Total column in Excel. The simplest method is to right-click any cell in the Grand Total row or column, choose Sort, then select Sort Largest to Smallest or Sort Smallest to Largest. This reorders the row or column labels based on the totals, and it works for both row and column Grand Totals in Excel for Microsoft 365, Excel 2021, and earlier versions.
Why Sorting by Grand Total Matters
Pivot tables summarize large datasets, but the default alphabetical or source-order sorting rarely highlights the most important values. Sorting by the Grand Total column lets you instantly see which product, region, or salesperson contributes the most revenue, or which category has the highest count. This is one of the most common pivot table tasks because it turns raw totals into a clear ranking. Without this sort, you might manually scan dozens of rows to find the top performer—sorting by Grand Total does it in one click.
How to Sort by Grand Total (Step-by-Step)
The exact steps depend on whether you want to sort rows by a column Grand Total, or sort columns by a row Grand Total. Here’s how to do both in Excel for Microsoft 365 on Windows.
- Sort rows by a column Grand Total: Right-click any numeric value in the Grand Total column (the rightmost column of the pivot table). Hover over Sort and choose Sort Largest to Smallest or Sort Smallest to Largest. Excel reorders the row labels accordingly.
- Sort columns by a row Grand Total: Right-click any numeric value in the Grand Total row (the bottom row of the pivot table). Hover over Sort and choose the desired order. This reorders the column labels.
- Using the Ribbon: Select any cell in the Grand Total column or row, then go to PivotTable Analyze (or Options in older versions) > Sort > Sort Largest to Smallest or Sort Smallest to Largest.
On a Mac, the right-click menu works the same way. In Excel for the web, right-clicking a Grand Total value also shows the Sort options.
Understanding the Sort Options in Pivot Tables
When you right-click a value in a pivot table, the Sort menu offers several choices: Sort A to Z, Sort Z to A, Sort Largest to Smallest, Sort Smallest to Largest, and More Sort Options. The “Largest to Smallest” and “Smallest to Largest” options are the ones that sort by numeric values. Crucially, these options appear when you right-click a numeric cell that is part of the pivot table’s values area. If you right-click a label cell (like a product name), you’ll only see alphabetical sorting options. So to sort by Grand Total, always right-click a number in the Grand Total row or column, not a label.
Manual Sort vs. Automatic Sort
There are two ways to apply a Grand Total sort: manually (as described above) or automatically using a custom sort. The manual method is quick and works for a one-time analysis. However, if you refresh the pivot table or add new data, the manual sort order is preserved—Excel remembers the sort you applied. For a more robust approach, you can use More Sort Options to set a specific sort key. For example, to sort rows by the Grand Total column, right-click a Grand Total value, choose More Sort Options, select Descending (Largest to Smallest), and in the “Sort by” dropdown, choose the Grand Total field. This ensures the sort is tied to that field even if the layout changes.
Common Problems and Fixes
Sometimes sorting by Grand Total doesn’t work as expected. Here are the most frequent issues and how to resolve them:
- Sort option is grayed out: This happens when you right-click a label cell instead of a value cell. Make sure you click a numeric value in the Grand Total column or row.
- Sort only affects a subcategory: If your pivot table has multiple row fields (e.g., Region and Product), right-clicking a Grand Total value sorts only the outermost field. To sort an inner field, you need to sort by the subtotal of that field, not the Grand Total.
- Grand Total column is hidden: If you don’t see a Grand Total column, go to PivotTable Analyze > Options (or right-click the pivot table > PivotTable Options) > Totals & Filters tab, and check “Show grand totals for columns” and “Show grand totals for rows”.
- Sort resets after refresh: If you refresh the pivot table and the sort disappears, it may be because you used a manual sort on a field that was later removed or re-added. Reapply the sort after refresh, or use the automatic sort via More Sort Options.
Sorting by Grand Total with Multiple Value Fields
If your pivot table has more than one value field (e.g., Sum of Sales and Count of Orders), the Grand Total column shows totals for each field side by side. Right-clicking a value in the Grand Total column will sort by that specific value field. For example, if you right-click the “Sum of Sales” Grand Total, the rows are sorted by total sales; if you right-click the “Count of Orders” Grand Total, they sort by order count. This is useful when you want to rank by one metric but still see others.
Alternatives to Sorting by Grand Total
If you prefer not to use the right-click sort, there are other ways to achieve the same ranking:
- Sort by a specific field using the Field List: In the PivotTable Fields pane, click the dropdown arrow next to a row label field (e.g., “Product”) and choose More Sort Options. Then select “Descending (Z to A)” and in the “Sort by” dropdown, choose the Grand Total field (e.g., “Sum of Sales”). This applies the sort directly to the field.
- Use a custom list: If you have a fixed ranking order, you can create a custom list (File > Options > Advanced > Edit Custom Lists) and then sort by that list using More Sort Options.
- Copy values and sort manually: Copy the Grand Total column and paste as values, then use Excel’s regular sort. This breaks the link to the pivot table, so it’s only for static reports.
Frequently Asked Questions
Why does my sort by Grand Total not work when I right-click a number?
If the right-click Sort menu shows only A to Z options, you likely right-clicked a label cell, not a value cell. Ensure the cell is in the Grand Total column or row and contains a numeric value. Also, check that the pivot table has a Grand Total enabled.
Can I sort by Grand Total in a pivot table with filters applied?
Yes, sorting works with filters. If you have a report filter (e.g., only showing Q1 data), the Grand Total reflects the filtered data, and sorting by it will rank based on that subset.
How do I sort by Grand Total when there are multiple row fields?
Right-click a Grand Total value to sort the outermost row field. To sort an inner field, you need to right-click a subtotal for that inner field, not the Grand Total. For example, if you have Region and Product, right-click a Product subtotal to sort products within that region.
Will sorting by Grand Total affect the original data?
No, sorting a pivot table only changes the display order of the pivot table. It does not alter the underlying source data in any way.
Is there a keyboard shortcut to sort by Grand Total?
There is no direct keyboard shortcut, but you can use the Alt key sequence: select a Grand Total cell, press Alt+D+S (on Windows) to open the Sort dialog, then choose the options. On Mac, use the right-click menu or the ribbon.
Related
- Excel Lowercase: Convert Text to Lowercase
- Excel Sales Tracking Template by Region
- Free Excel Sales Tracking Templates: What Works
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Pivot Chart Not Showing All Data? How to Fix ItA PivotChart usually misses data because its PivotTable source has not been refreshed or expanded, or because a…
- How to Create a 3-D Pie Chart in Excel Step by StepTo create a 3-D pie chart in Excel for Microsoft 365 on Windows, select a category column and…
- Excel Slicers for Multiple Pivot Tables: How to ConnectYes. In Excel for Microsoft 365 on Windows, select the slicer, choose Slicer > Report Connections (called PivotTable…
- How to Create a Pie Chart in Google Sheets Step by StepTo create a pie chart in Google Sheets, arrange your categories in one column and their numeric values…
- Funnel Chart in Excel 2016: How to Make OneExcel 2016 does not include a built-in Funnel chart type, but you can make a useful funnel-style visual…
- Excel Gantt Chart Scroll Bar: Setup and TroubleshootingYes. In Excel for Microsoft 365 on Windows, add a Form Control scroll bar and link it to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.