Pivot Table Sort by Value Not Working? Fixes in Excel

To sort an Excel PivotTable by its numbers, click a value in the column you want to rank, then right-click and choose Sort > Largest to Smallest or Sort > Smallest to Largest. If that does not work, the usual causes are sorting the wrong field or value column, a nested row hierarchy, a manual sort order, or values that Excel is treating as text.

In Microsoft Excel for Microsoft 365 on Windows, sorting a PivotTable is different from sorting the source data: the command changes the order of items in the report according to the displayed summary. The checks below help identify which setting is getting in the way and how to make the ranking match the question you are asking.

How value sorting works in a PivotTable

A PivotTable groups records and calculates summaries such as Sum, Count, or Average. A value sort orders the labels in a row or column field according to one of those summaries. For example, a table with Product in Rows and Sum of Sales in Values can put the highest-selling product at the top. The source rows do not need to be arranged in that same order.

The selected value matters. If the PivotTable contains both Sum of Sales and Count of Orders, clicking a number under Count of Orders and choosing Largest to Smallest sorts products by order count, not sales. Similarly, if the table has a Region column field, select a value in the appropriate region column if you want to rank by that region rather than by another displayed measure.

Excel sorts items in the field associated with the labels, using the selected summary values as the ranking basis. It does not sort every detail row in the original data, and it does not necessarily create one global ranking across multiple levels of row labels.

Use the right sort command

Click a numeric value in the PivotTable, in the value column you want to use for ranking. Avoid clicking a row label or a subtotal or grand-total cell.
Right-click the cell and choose Sort, then choose Largest to Smallest or Smallest to Largest.
Check the result. The row labels should move while the corresponding values stay attached to their records.

If you need to specify the measure explicitly, right-click a label in the row field you want to order and choose Sort > More Sort Options. Choose Descending (Z to A) by or Ascending (A to Z) by, then select the value field from the list. The wording in the dialog can look alphabetical even though Excel is ordering the labels by their associated numeric values. If the dialog offers automatic sorting when the report is updated, leave that option enabled when you want the ranking reapplied after refresh.

For example, suppose Products are listed in rows and Sum of Sales shows 450 for Cedar Desk, 900 for Maple Shelf, and 700 for Pine Lamp. Largest to Smallest should display Maple Shelf, Pine Lamp, and Cedar Desk, in that order. If it instead orders them alphabetically, reopen More Sort Options and confirm that the sort is based on Sum of Sales, not the product label.

Check the selected field and value column

A common source of confusion is placing several fields in Rows. Imagine Region above Product in the Rows area. Sorting Product by sales will generally order products within each region; it does not turn the whole report into a single list of products ranked across all regions. The parent grouping remains in place because each region has its own set of product items.

To get a global product ranking, make Product the only row field, or create a separate PivotTable with Product as its row field and the desired measure in Values. If you need to retain Region and still compare products across all regions, consider a second report that summarizes sales by product without the Region grouping. That makes the ranking criterion visible and avoids reading a within-region order as a company-wide ranking.

With column fields, the value column you click can also change the meaning of the sort. A table showing sales by month may rank row labels using January sales if you click a January value, while choosing a total value can rank by the overall total. Inspect the selected column heading and the measure name before sorting. If the report has multiple value fields, verify both: which measure is selected and which field of labels is being ordered.

When the sort appears not to work

You clicked a label. Sorting a label may produce alphabetical order. Click a numeric value first, or use More Sort Options and choose the intended value field.
You clicked the wrong measure. A PivotTable may show sums, counts, and averages side by side. Sort from the measure that represents the ranking you want.
The field has a manual order. Dragged items or a custom list can make the sequence look fixed. Use the sort menu to apply a value-based order rather than relying on the current item arrangement.
You are sorting a nested field. A child field is typically ordered inside its parent groups. Remove or rearrange the parent field if you need one overall ranking.
You are trying to sort a total row. Grand totals summarize the report; they are not ordinary items in the row field. Select a regular item’s value instead.
The numbers are not numeric. Imported amounts stored as text may be counted or summarized unexpectedly. Check the Values field name and the summary calculation.
A filter hides items. A value sort only orders the items currently displayed. Clear or adjust the row, column, or report filters if the expected label is missing.

Also distinguish a value sort from a value filter. A filter such as Top 10 keeps or removes items based on a rule; sorting changes their order. A report can be correctly sorted while still showing only a filtered subset, so inspect the field filter if some expected categories are absent.

Check the summary calculation and source data

Look at the Values area or the value heading to see whether the measure is Sum of Sales, Count of Sales, Average of Sales, or another calculation. If Excel displays Count where you expected Sum, some source entries may be blank or stored as text, or the field may have been configured to count. Right-click a value and choose Summarize Values By to inspect or change the calculation. The exact available choices depend on the source and the field.

For a numeric sales field, verify that the source cells contain actual numbers, not text that merely looks like numbers. In the source data, check for inconsistent entries such as a currency symbol typed into some cells, leading apostrophes, or words mixed into the amount column. Correct the source values, refresh the PivotTable, and confirm that the Values summary is the intended one. Changing the display number format alone does not convert text into numbers.

Blanks and errors can also make a summary differ from expectations. Compare a few PivotTable totals with the underlying records for one category. If the report uses a calculated field, the Data Model, or an external connection, verify that the displayed measure is the one you intend; its calculation rules may not match a simple sum of a source column.

Refreshes, manual order, and persistent sorting

When source records change, use PivotTable Analyze > Refresh to update the report. A refresh updates the summary from the source; it is separate from the act of sorting. After refreshing, check whether the desired sort direction and sort-by field are still in effect, especially when new categories have appeared or the report was manually rearranged.

If items were dragged into a custom sequence, apply the value sort again through the context menu or More Sort Options. If the sort keeps reverting, reopen the options for the relevant field and check that automatic sorting is enabled where the dialog provides that setting. Also check that you are not looking at a different PivotTable or a copy with separate settings.

Refreshing cannot include source rows that are outside the PivotTable’s source range. If new records are missing, confirm the source range or use an Excel Table as the source so it can expand as records are added, then refresh. This is a data-source issue rather than a sorting failure.

Excel for Mac and Excel for the web

The instructions above describe Excel for Microsoft 365 on Windows. In Excel for Mac, select a value and use the right-click or Control-click context menu to find the sort choices; a trackpad or mouse may use a different gesture. The labels and dialog layout can vary slightly by build, but the key is still to sort the row field by the intended value field.

Excel for the web also supports PivotTable sorting, although its available controls can differ from the desktop application. If you cannot find a More Sort Options control or a particular setting in the web interface, open the workbook in the desktop app and check the field’s sort options there. Avoid manually sorting the source worksheet as a substitute: that changes the source row order, not the order of summarized PivotTable items.

Frequently Asked Questions

Why does my PivotTable sort alphabetically instead of by value?
Excel may be sorting the label field alphabetically because you selected a label or chose an alphabetical sort. Click a numeric value and choose Largest to Smallest, or open Sort > More Sort Options and specify the value field.

How do I sort a PivotTable from highest to lowest?
Click a regular value in the measure you want to rank, right-click, and choose Sort > Largest to Smallest. Confirm that the labels move into the expected order and that you selected the correct measure column.

Can I sort a PivotTable by Grand Total?
You can often rank row items using their overall totals by selecting a value in the Grand Total column and applying a descending value sort. Do not select the Grand Total row itself; it is an aggregate, not an item to place among the row labels.

Why are products sorted separately inside each region?
When Region is a parent row field and Product is nested below it, Excel preserves the region groups and sorts products within those groups. Remove Region from Rows or make a separate product-only PivotTable for one overall product ranking.

Why does my PivotTable show Count instead of Sum?
Excel may be treating some source entries as text or blanks, or the Values field may be set to Count. Check Summarize Values By and inspect the source data for nonnumeric entries, then correct the data and refresh.

Will sorting the source data fix the PivotTable order?
No. Sorting worksheet rows changes their order in the source range but does not reliably set the order of grouped PivotTable items. Apply the sort to the PivotTable itself using its value cells and sort options.

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.