Sort Largest to Smallest Grand Totals With Excel Pivot Tables

When working with data in a Pivot table, you may want to know the best-performing products, customers, or regions. This can easily be done by sorting the values from largest to smallest. In this article, you will learn how to sort the largest to smallest grand totals with Excel Pivot tables.

Key Takeaways

• Sorting Grand Totals helps you quickly find the highest values.
• You can sort a Pivot Table using the right-click menu.
• Grand Totals must be enabled before you can sort them.
• Refreshing the Pivot Table may require you to reapply the sort.
• Sorting makes your reports easier to read and analyze.

 

STEP 1: Select any cell in the data table.

STEP 3: In the Create PivotTable dialog box, select the table range and New Worksheet, and then click OK.

STEP 5: Right-click on a Grand Total below at the bottom of the Pivot Table. Go to  Sort > Sort Largest to Smallest

Grand total not visible

If you cannot see the Grand Totals,

• Click in your Pivot Table
• Go to the ribbon menu
• Select PivotTable Tools > Grand Totals > On for Rows and Columns

The data will be sorted in descending order.

 

Overcoming Technical Glitches

Pivot Table not Sorting on Refresh

Sometimes, after refreshing the Pivot the new data may appear at the end instead of being in the correct sorted position. This happens because Excel does not always reapply the sort automatically after a refresh. To fix it, you need to sort the Pivot table again.

Row and Column Label Sorting

If your Pivot Table is not sorting rows or columns correctly, check that you are sorting the correct field.

When a Pivot Table contains multiple fields, Excel may sort by a different field than expected. Click a value in the field you want to sort, then choose Sort A to Z, Sort Z to A, Sort Largest to Smallest, or Sort Smallest to Largest.

Also, make sure manual sorting is not enabled. If needed, turn it off and apply the sort again. This usually fixes most sorting problems in Pivot Tables.

Sort Option is Grayed Out

If the Sort option is unavailable, check whether the Pivot Table is protected or in edit mode. Also, make sure you have selected a value inside the Pivot Table rather than a blank cell.

Numbers Stored as Text

If your source data contains numbers stored as text, Excel may not sort the values correctly. Convert the text values to numbers, refresh the Pivot Table, and sort again.

Blank Values in Data

Blank or empty values usually appear at the bottom of the sorted list. Fill in missing values or filter out blanks before creating or refreshing the Pivot Table.

 

FAQs