Sort Pivot Table by Grand Total in Excel

Charts & pivot tables · Updated November 22, 2026

To sort a pivot table by the Grand Total column, click any cell inside the Grand Total row or column, then use the Sort A to Z or Sort Z to A button on the Data tab (or right-click and choose Sort). For example, clicking a Grand Total cell and choosing Sort Largest to Smallest reorders all row labels so the highest total appears at the top.

Why Sorting by Grand Total Matters

Pivot tables summarize large datasets into compact views, but the default order of row or column labels is alphabetical (or based on the source data). When you have dozens of products, regions, or employees, the Grand Total row or column holds the key metric—total sales, total count, total sum. Sorting by that total instantly reveals your best performers and worst performers without manual scanning. This is especially useful for dashboards, monthly reports, and quick top-N analysis.

Step-by-Step: Sort by Grand Total (Row Labels)

Assume your pivot table has row labels (e.g., product names) and a Grand Total column at the far right. To sort the row labels by that Grand Total column:

  1. Click any single cell inside the Grand Total column (for example, the cell that shows the total for the first product).
  2. Go to the Data tab on the ribbon.
  3. Click either Sort Largest to Smallest (for numeric totals) or Sort Smallest to Largest.
  4. Excel immediately reorders all row labels based on the values in that Grand Total column.

Alternatively, right-click the same cell and choose Sort > Sort Largest to Smallest. On a Mac, the Data tab has the same buttons; the right-click menu also works identically.

Sorting by Grand Total When It’s a Row (Column Labels)

If your pivot table has column labels (e.g., months across the top) and a Grand Total row at the bottom, the process is symmetric. Click any cell in the Grand Total row, then use the same Sort buttons. Excel will reorder the column labels based on the totals in that row. This is common when comparing quarterly performance across regions.

What If the Grand Total Is Not Visible?

Sometimes the Grand Total is hidden. To show it, go to the pivot table’s Design tab, click Grand Totals, and choose On for Rows and Columns (or just the one you need). If you’re using a PivotChart, the Grand Total is not displayed on the chart itself—you must sort the underlying pivot table first.

Common Problems and Solutions

  • Sorting affects the wrong field: If you click a cell that is not in the Grand Total row/column, Excel sorts by that specific column instead. Always verify the cell is in the Grand Total row/column.
  • Sorting does nothing: This happens when the pivot table has multiple row fields (e.g., Region then Product). Clicking a Grand Total cell sorts only the outermost field. To sort an inner field, you must first collapse the outer field or use the Sort options in the field’s dropdown.
  • Numbers appear as text: If your source data has numbers stored as text, the Grand Total may sum incorrectly or sort alphabetically. Fix the source data by converting text to numbers (e.g., using =VALUE() in a helper column) and refresh the pivot table.
  • Sort order resets after refresh: Pivot tables remember manual sorts, but if you add new items, they may appear at the end. Reapply the sort after refreshing.

Using Custom Sort Options for More Control

If the simple A–Z or Z–A buttons don’t give you the exact order you want, use the More Sort Options dialog. Right-click a Grand Total cell, choose Sort > More Sort Options. In the dialog, you can choose to sort by the Grand Total field and specify whether to sort ascending or descending. You can also set a custom order (e.g., by a specific value) using the “Sort by” dropdown. This is useful when you have multiple value fields (e.g., Sum of Sales and Sum of Profit) and want to sort by one of them.

Sorting by Grand Total vs. Sorting by a Specific Column

Many users confuse sorting by the Grand Total with sorting by a regular data column. For example, if your pivot table shows sales by product and by month, sorting by the “January” column reorders products based only on January figures. Sorting by the Grand Total reorders based on the entire year. The choice depends on your analysis: use the Grand Total for an overall ranking, or use a specific column for a period-specific ranking. You can also sort by a column that is not the Grand Total by clicking a cell in that column and using the same sort buttons.

Frequently Asked Questions

How do I sort a pivot table by grand total in Excel 2016? The steps are identical: click a cell in the Grand Total row or column, then use the Sort buttons on the Data tab. Excel 2016 has the same interface as later versions for this feature.

Can I sort by grand total without changing the original data? Yes, sorting a pivot table only affects the pivot table’s display order. Your source data remains untouched. You can always revert by clicking the field’s dropdown and choosing “More Sort Options” > “Manual” to return to the original order.

Why does my Grand Total show as a column but I can’t click it? If the Grand Total column is not visible because it’s outside the pivot table’s scroll area, you may need to scroll right. Also, ensure the pivot table is not in Compact Layout, which hides some labels; switch to Outline or Tabular layout via the Design tab if needed.

How do I sort by grand total when I have multiple value fields (e.g., Sum of Sales and Sum of Profit)? Click a cell in the Grand Total column that corresponds to the value field you want to sort by. For example, if the Grand Total column has two sub-columns (Sales and Profit), click a cell under “Total Sales” to sort by that field. Then apply the sort.

Is there a way to sort by grand total automatically when refreshing? No, Excel does not have a built-in setting to auto-sort after every refresh. You can use a VBA macro to reapply the sort on refresh, but that requires coding. For most users, manually clicking the sort button after refreshing is sufficient.

Does sorting by grand total work in Excel for the web? Yes, Excel for the web supports pivot table sorting. Click a cell in the Grand Total row or column, then use the Sort buttons on the Data tab (available in the browser version). The right-click menu also works.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.