How to Sort Pivot Table by Grand Total in Excel: Easy Guide

Sharing is caring!

Working with Pivot Tables in Excel is a fast way to analyze data and summarize large datasets. One of the most useful features is the ability to sort your Pivot Table by Grand Total. This lets you quickly see which items have the highest or lowest totals, making your reports clearer and more impactful.

In this guide, you’ll learn how to sort Pivot Table by Grand Total in Excel with simple steps and best practices. Whether you use Excel 2016, Excel 2019, Excel 2021, or the latest Excel 2025, this method will work for you.

Prerequisites: Setting Up Your Pivot Table

Before you can sort by Grand Total, you need a Pivot Table with visible Grand Totals. Here’s a quick recap of how to set this up:

1. Select your data range. Make sure your data is organized with clear headers for each column.
2. Insert a Pivot Table:
   • Go to the Insert tab.
   • Click PivotTable.
   • Choose the data range and select where you want the Pivot Table to appear.
3. Build your Pivot Table:
   • Drag fields to the Rows and Values areas.
   • Add fields to the Columns area if needed.

If you do not see Grand Totals, enable them by:

• Clicking anywhere inside the Pivot Table.
• Go to PivotTable Tools > Design > Grand Totals.
• Choose On for Rows and Columns.

How to Sort Pivot Table by Grand Total (Step-by-Step)

Sorting a Pivot Table by Grand Total is simple. You can sort either rows or columns by their totals, depending on your analysis needs. Follow the steps below:

Step 1: Identify the Grand Total

First, look at your Pivot Table and locate the Grand Total column (for sorting rows) or Grand Total row (for sorting columns).

Step 2: Right-Click the Grand Total Value

• To sort rows by their total, right-click any cell in the Grand Total column.
• To sort columns by their total, right-click any cell in the Grand Total row.

Step 3: Choose the Sort Option

After right-clicking:

• Hover over the Sort menu.
• Click Sort Largest to Smallest to move the highest totals to the top (or left).
• Or select Sort Smallest to Largest to bring the lowest totals to the top (or left).

Excel will automatically rearrange your Pivot Table to reflect your choice.

Sorting Pivot Table by Grand Total Using the Ribbon

If right-clicking isn’t convenient or you’re using Excel for Mac (where the context menu option may not appear):

1. Select the Grand Total value (cell) for the row or column you want to sort.
2. Go to the Home tab.
3. Click Sort & Filter.
4. Choose Sort Largest to Smallest or Sort Smallest to Largest.

This method works in all modern versions of Excel.

Example: Sorting Sales Data by Grand Total

Let’s say you have a sales dataset like this:

If you want to see which product has the highest overall sales, right-click any cell in the Grand Total column (for example, the “900” for Apples), select Sort Largest to Smallest, and Excel will reorder the products so Bananas (950) appears first, then Apples (900), then Cherries (500).

Limitation: Sorting with Multiple Row Fields

Sorting by Grand Total only works directly when you have one field in the Rows area.
If you add multiple row fields (e.g., Category > Product), Excel sorts inside each subgroup, not by the overall Grand Total.

Workarounds for Multi-Level Pivot Tables

If you need to sort by overall Grand Totals when multiple row fields exist, try these methods:

1. Collapse Higher-Level Fields

• Collapse groups by clicking the – icon next to the higher-level field.
• Sort the visible lowest-level items by Grand Total.

2. Use a Helper Field

• Add a calculated field or use a helper column in your source data with the metric you want to rank.
• Use that field in the Pivot Table’s Values area.
• Sort based on this field.

3. Create a Separate Pivot Table

• Insert another Pivot Table with only the lowest-level field (e.g., Product).
• Sort it by Grand Total to see rankings.
• Keep your grouped Pivot Table for structured reporting.

4. Copy as Values (Static Report)

• Copy your Pivot Table.
• Paste as Values.
• Use normal Excel sorting.
• This removes Pivot functionality but works for final reports.

Tips for Sorting Pivot Tables by Grand Total

• Grand Totals must be visible: If you cannot find the Grand Total, enable it from PivotTable Tools > Design > Grand Totals.
• Sorting is dynamic: If your source data changes, refresh the Pivot Table to update the sort order.
• Works with filters: You can filter your data and then sort by Grand Total for focused analysis.
• Applies to both rows and columns: Use the same steps for sorting columns by their Grand Totals.

Common Issues and Solutions

1. Grand Total Option Not Visible

If you don’t see the Grand Total:

• Click inside the Pivot Table.
• Go to Design > Grand Totals.
• Select On for Rows and Columns.

2. Sort Option Not Available (Excel for Mac)

If the right-click sort option is missing:

• Select the Grand Total cell.
• Use the Sort & Filter option in the Home tab.

3. Pivot Table Not Sorting

If your Pivot Table is not sorting as expected:

• Make sure you are clicking the Grand Total value, not a subtotal or a regular data cell.
• Check if filters are applied that might be limiting the sort order.
• Refresh the Pivot Table after editing the source data.

Using Sorting with Filters and Slicers

Sorting works seamlessly with filters and slicers in Excel. If you apply a filter or use a slicer to display only part of your data, sorting by Grand Total will only affect the visible items. This makes it easy to perform focused analysis on a specific segment of your data.

Best Practices for Sorting Pivot Table by Grand Total

• Refresh regularly: Always refresh your Pivot Table after changing the source data.
• Double-check totals: Ensure Grand Totals are displaying as expected before sorting.
• Use meaningful fields: Make sure you are sorting the most relevant value for your analysis.
• Label clearly: Use custom labels for your Grand Totals to make your reports easy to read.
• Combine with Conditional Formatting: Highlight top performers after sorting to make your Pivot Table more visual.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.