How to Sort an Excel PivotTable Largest to Smallest

To sort an Excel PivotTable from largest to smallest, right-click a number in the value column you want to rank, then choose Sort > Sort Largest to Smallest. Excel reorders the row items by that measure; it does not change the underlying source data.

Sort a PivotTable by its values

These steps apply to PivotTables in Microsoft Excel for Microsoft 365 on Windows. For example, suppose a PivotTable lists product names in rows and sums of sales in a column. To put the highest-selling product first:

Click a numeric value in the sales column for an ordinary product row. Do not select a column heading or a Grand Total cell.
Right-click the selected cell.
Choose Sort, then Sort Largest to Smallest.

The product names move with their corresponding totals, so each row remains intact. If the context menu does not show the expected choice, confirm that you clicked a value inside the PivotTable rather than a cell beside it. You can also open the drop-down for the row field, choose More Sort Options, and set a descending sort based on the required value field.

On Excel for Mac, right-clicking a PivotTable value also opens sorting options; a trackpad may require a two-finger click or Control-click. The wording and menu placement can vary slightly by release, but the key is to sort the row field by the value field, not merely alphabetically.

What largest-to-smallest means

A PivotTable groups source records and summarizes a chosen measure, such as sales, units, hours, or counts. Sorting largest to smallest ranks the displayed row items according to their summary values. If the PivotTable shows Sum of Sales, Excel compares each item’s sum of sales. If it shows Count of Orders, the ranking is based on the count instead.

This is a sort of the summarized report, not a sort of the original records. A product with the largest total may have many moderate transactions, while another product may have one unusually large transaction. Sorting the PivotTable answers which grouped item has the greatest summary; it does not rearrange individual transactions in the source table.

The operation also does not remove items. It changes their order. To show only a subset, such as the ten highest-selling products, use a value filter as well as, or instead of, sorting.

Choose the value field to sort by

A PivotTable can contain more than one measure. For example, it might show Sum of Sales, Sum of Units, and Average of Price beside each product. A descending sort needs one of those fields as its ranking basis. Right-click a number in the measure you care about and choose the sort command, or use the row-field menu’s More Sort Options dialog to select the measure explicitly.

Check the column heading before sorting. Clicking a number under Sum of Units ranks products by units, even if the sales column is more visually prominent. A common source of confusion is expecting a sort to follow whichever measure a reader considers most important; Excel uses the selected value field or the field specified in the sort settings.

If the report has several value columns split by another field—for example, sales by product with a separate column for each year—choose a value from the year or measure that should determine the ranking. The result may rank products using only that selected column, not a combined total across every year.

Sort from the row-field menu

The context-menu method is quick, but the field menu is useful when you want to inspect or specify the sort basis. Select the drop-down beside the row field, such as Row Labels or Product, and choose More Sort Options. In the sort dialog, choose the descending option and select the value field to rank by, then confirm.

The exact field label depends on the layout and Excel version. A compact PivotTable may display a general Row Labels heading, while a tabular layout displays the actual field name. If there are multiple row fields, open the menu for the field whose items you want to reorder. Sorting the wrong field can produce a different-looking result even when the selected direction is correct.

Use this method when the right-click menu is ambiguous, when multiple value fields are present, or when you need to change an existing sort. To return to ascending order, use the corresponding smallest-to-largest or ascending option in the same sorting controls.

Understand nested row fields

Many reports use more than one row field, such as Region followed by Product. In that layout, sorting Product by sales ordinarily orders products within each region. It does not necessarily create one flat, workbook-wide ranking of every product across all regions, because the PivotTable is preserving the parent-child grouping.

If you need a single overall product ranking, make Product the only row field, or create a separate PivotTable with Product as its row field and the desired measure as its value. You can keep Region as a report filter or slicer if you want to examine one region at a time. This avoids mistaking a within-region order for a company-wide ranking.

Likewise, sorting a higher-level field such as Region orders the regions by the selected summary measure. It does not necessarily change the order of the products inside each region. Select the field that represents the items you want to rank, then verify the visible result against the report’s hierarchy.

Example: rank products by sales

Imagine a source table with Date, Product, and Sales columns, and a PivotTable that puts Product in Rows and Sum of Sales in Values. The report displays:

Notebook — $4,800
Desk lamp — $3,250
Office chair — $6,100

Right-clicking one of the sales amounts and choosing Sort > Sort Largest to Smallest places Office chair first, Notebook second, and Desk lamp third. The dollar values are only illustrative; the important point is that the products are ranked by their displayed sales totals. If the same PivotTable also shows units sold, sorting a number in that column instead ranks by units, which can yield a different order.

For a report with monthly columns, decide whether the question is “Which product sold the most overall?” or “Which product sold the most in March?” A sort based on March’s column answers the second question. For the first, use a total sales value that actually summarizes all months, rather than assuming a single month’s values represent the overall result.

Common sorting problems and fixes

The order is alphabetical, not numeric. You may have sorted the row labels instead of the values. Open More Sort Options and choose descending by the relevant value field.
The wrong measure controls the order. Re-sort using a cell in the intended value column, or specify that field in the sort dialog.
Some items seem missing. Check whether a label filter, value filter, report filter, or slicer is restricting the PivotTable. Sorting cannot display items excluded by a filter.
Ties appear in an unexpected order. Items with equal summarized values have no unique rank from that measure alone. Their relative order may reflect another sort or the existing item order; add another field or inspect the tied values if a tie-breaker matters.
Grand Total is at the bottom. That is normal: Grand Total is a summary row, not an ordinary item to rank with the detail rows. Avoid using it as the sort target.
A refresh changes the report. Refreshing updates summaries from the source. Check that the desired value-based sort remains selected and reapply it if the displayed order is no longer the one you need.

Also check for blank or error values in the source, and confirm that a numeric measure is being summarized as intended. If values are unexpectedly counted rather than summed, the PivotTable’s value-field settings may need attention; sorting faithfully follows the summary currently displayed, even when that summary is not the one you intended.

Sorting versus filtering and source-data sorting

Sorting and filtering solve different problems. Sorting places all currently included items in an order. A value filter limits which items are shown—for instance, products whose Sum of Sales is above a threshold. A Top 10 value filter can show the highest-ranked items, while a descending sort makes their order easy to read. Use both when you want a short list in rank order.

Sorting the source table is different again. It rearranges transaction rows, whereas a PivotTable sort changes the report’s displayed group order. If your goal is to rank customer or product totals, sort inside the PivotTable. If your goal is to inspect the original transactions chronologically or by amount, sort the source table instead. Keep the distinction in mind when sharing a workbook, because the visible PivotTable order does not mean the source records have been reordered.

Frequently Asked Questions

Can I sort an Excel PivotTable by Grand Total?
Usually, sort by the relevant value field rather than clicking the Grand Total cell. Select a detail value and specify the measure that represents the total for each row item. Grand Total is the overall summary, not an item in the ranking.

How do I sort a PivotTable from smallest to largest?
Right-click a number in the value field and choose Sort > Sort Smallest to Largest. Alternatively, open the row-field menu, choose More Sort Options, and select an ascending sort based on the appropriate value field.

Can I sort a PivotTable by a calculated value?
Yes, if that value appears as a PivotTable value field, you can use it as the sort basis. Select a displayed value in that field or choose it in the sort dialog. The ranking will reflect the calculated results currently shown.

Why does sorting one column change the order of the whole PivotTable?
Each row item is a complete record in the summarized report, so Excel moves the item’s label and its other displayed measures together. That keeps a product’s sales, units, and other values on the same row.

Will a PivotTable stay sorted after refresh?
Excel generally recalculates a value-based sort when the PivotTable updates, but changes to fields, filters, or layout can affect what you see. After refreshing, verify the ranking and reapply the sort if necessary.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.