Excel Pivot Table Sort by Value Not Working? Fixes

Charts & pivot tables · Updated November 19, 2026

If an Excel PivotTable will not sort by value, select a number in the value column you want to sort, right-click it, and choose Sort > Sort Largest to Smallest (or Sort Smallest to Largest). If that does not work, use the row field’s menu and choose Sort > More Sort Options, then select the correct value field in the sort dialog.

How value sorting works in a PivotTable

A PivotTable does not sort its source rows when you sort a summarized result. Instead, it rearranges the displayed items in a row or column field according to the calculated values shown in the PivotTable. For example, a sales PivotTable can put product names in descending order of their summed sales, even though the original transaction records remain unchanged.

The sort applies to one field at a time and uses a particular value field, such as Sum of Sales, Count of Orders, or Average of Units. If the table contains more than one value field, the selected field matters. Sorting by Count of Orders can produce a different order from sorting by Sum of Sales. Grand totals and subtotals are summaries, not ordinary category items; Excel does not treat them as additional products or regions to sort among the detail items.

Value sorting is also different from sorting alphabetically. A-to-Z sorting orders labels such as product names, while largest-to-smallest sorting orders those labels by the associated calculated number. Selecting a number cell before sorting helps Excel infer which kind of sort you mean and which value field should control it.

Use the right sort command

In Excel for Microsoft 365 on Windows, try this sequence first:

  1. Click a numeric result in the value column that should determine the order. For instance, click a sales total beside one of the products, not a product name or the grand total.
  2. Right-click the cell, point to Sort, and choose Sort Largest to Smallest or Sort Smallest to Largest.
  3. Check that the row labels have moved into the expected order. The values themselves remain calculated from the source data.

If the table has several value columns, click a number in the specific column you mean to use. Suppose the columns are Sum of Sales and Count of Orders. Clicking a sales result and sorting should order the row items by sales; clicking an order count should sort them by count instead.

You can also open the menu for the row field. Click its drop-down arrow, choose More Sort Options (or right-click a row label and use Sort > More Sort Options), select descending or ascending order, and choose the desired value field in the by box. The wording in the dialog refers to A-to-Z or Z-to-A order, but the selected value field determines the numerical basis of the sort. If the field drop-down arrows are hidden, right-click a row label or enable field headers from the PivotTable display options.

On a Mac, the PivotTable options and context menus can be arranged differently; use a right-click or Control-click on a value or row label and look for the same Sort choices. Excel for the web has a different interface, but the central rule still applies: invoke sorting from the PivotTable and identify the value field that should control it. Exact menu placement can vary by platform and update.

Common reasons the sort appears not to work

  • You sorted labels instead of values. Choosing A to Z from a row-label menu sorts the names, not the numbers. Select a value cell or explicitly choose a value field in More Sort Options.
  • The wrong value column is active. In a PivotTable with multiple measures, a sort can be correct but based on Count, Average, or another measure rather than the intended Sum. Confirm the selected value field in the dialog.
  • The sort is ascending rather than descending. A-to-Z in the sort dialog can look unintuitive when the labels are numbers or dates. Verify that the dialog says descending when you want the largest result first.
  • The values are tied. Items with equal totals may appear in an order that seems arbitrary or unchanged. Excel has no numerical reason to put tied items in one particular order; use a secondary sort or inspect the underlying values if their tie order matters.
  • You sorted a different field level. With Region and Product both in Rows, each is a separate field. A sort applied to Product does not necessarily create one global list of products across all regions. It sorts items at that field level within the PivotTable’s hierarchy.
  • The displayed number is not the measure you think it is. A field set to Count instead of Sum, or Average instead of Sum, changes the figures Excel sorts by. Right-click a value, choose Summarize Values By, and check the selected calculation.
  • The layout or filter hides the expected comparison. A report filter, slicer, or collapsed group may leave only some items visible. The visible order can be valid for the current selection, even if it differs from an unfiltered report.

A quick diagnostic is to click two or three visible result cells and compare their actual displayed values with their item order. If a larger value is above a smaller one in a descending sort, check the active field, direction, and hierarchy before assuming the PivotTable failed. Also make sure you are not comparing a subtotal for a parent category with a detail value for one of its children.

Check the value calculation and source data

Sorting can only use the results currently displayed. If a source column contains numbers stored as text, blanks, or inconsistent entries, the PivotTable may summarize the data in a way that differs from what you expect. For example, a text field may be counted rather than summed, and blank or nonnumeric records can affect a count. Check the source column’s data type and the value field’s calculation before troubleshooting the sort itself.

To inspect the calculation, right-click one of the PivotTable values and choose Summarize Values By. Select the intended option, such as Sum, Count, or Average. If Sum is unavailable or the result remains unexpected, inspect the source records and how the PivotTable was created. A field with text values cannot be summed like a numeric field. Correct the source data, then refresh the PivotTable and confirm the value calculation again.

Be careful to distinguish a value’s display format from its underlying amount. Currency symbols and rounded decimals change how a result looks, not necessarily the amount used to sort it. Two values that both display as $1,000 may have different unrounded totals, so Excel can put one ahead of the other. Increase the displayed decimal places temporarily if you need to diagnose close results.

Refresh the PivotTable and verify its source range

If new or corrected records are missing, refresh the PivotTable before judging its order. Right-click inside it and choose Refresh, or use Data > Refresh All to update workbook connections and PivotTables. Refreshing updates the results from the data source; it does not automatically guarantee that a manually maintained source range includes every new row.

To inspect the source, click inside the PivotTable and open PivotTable Analyze > Change Data Source. Check that the selected range covers the full dataset, including recent rows and the relevant value column. For data that grows regularly, formatting the source as an Excel Table before creating the PivotTable can make new appended rows easier to include. After changing the source, refresh and repeat the value sort.

Refresh may also restore a sort based on the current data, so check the order after updating. If the source values changed, the previous order may no longer be appropriate. A PivotTable summarizes its source rather than continuously recalculating every change in the way a normal formula cell does.

Understand nested fields, filters, and manual order

Row fields create levels. For example, a report with Region above Product groups products beneath each region. Sorting Product by Sum of Sales is a sort of product items at that level, not necessarily a single ranked list that ignores the region structure. To create a truly global product ranking, remove the higher-level field from Rows or build a separate PivotTable with Product as the only row field and the desired measure in Values.

Filters change the set of data being summarized. A product’s total after selecting one year or one region can differ from its total across all records, and the sort follows the current PivotTable results. Clear report filters or slicer selections when you need to compare the complete dataset. Likewise, a collapsed parent group conceals detail items; expand it before checking their relative order.

Manual ordering can also make the result confusing. If items have been dragged into a custom sequence, switch back to a value-based sort through the row field’s sort menu. Conversely, if you deliberately want a custom order, do not expect it to stay consistent with changing values. PivotTable sorting is based on the field and measure you choose, while manual positioning is based on a user-arranged sequence.

When sorting still fails

Try rebuilding the sort from a clean selection rather than repeatedly clicking the header. Click a value cell in the intended measure, choose the sort direction, and then open More Sort Options to confirm the exact field. If items remain in a surprising order, check for ties, hidden filters, nested row fields, and values that are rounded for display. These checks explain most cases where the command appears to have had no effect.

If the PivotTable uses an external source or the Excel Data Model, some field behaviors can differ from a simple range-based PivotTable, especially where measures and hierarchies are involved. Confirm that the value being sorted is available as a value field and that you are sorting the intended level. If the sort options are disabled, verify that the active cell is inside the PivotTable and that the workbook is not in a state that prevents editing, such as a protected sheet. If necessary, make a small copy of the PivotTable in an unprotected worksheet to isolate the issue.

Finally, distinguish sorting from filtering. A value filter hides items that do not meet a condition; it does not merely move them lower in the list. If some labels disappear after using a menu, review the field’s filter settings. Remove the value filter if you want every item shown, then apply the sort separately.

Frequently Asked Questions

How do I sort a PivotTable from highest to lowest?
Click a numeric result in the value field you want to use, right-click, and choose Sort > Sort Largest to Smallest. If the result is wrong, open More Sort Options and select the intended value field.

Why does my PivotTable sort alphabetically instead of by sales?
You likely chose an A-to-Z sort for the row labels. Select a sales value and use Largest to Smallest, or open More Sort Options and set the sort to use the sales value field.

Can I sort by one value column when my PivotTable has several?
Yes. Select a result in the desired value column before sorting, or choose that specific measure in the More Sort Options dialog. A sort based on Count may differ from one based on Sum.

Why does refreshing change my PivotTable’s order?
Refreshing recalculates the summary from the source data, which may change the values and therefore their ranking. Refresh also does not add records outside the PivotTable’s source range; check the range if recent rows are missing.

Can I sort products across all regions instead of within each region?
A PivotTable with Region above Product preserves that hierarchy, so product order is associated with the region grouping. For a global product ranking, use a separate PivotTable with Product as the row field and the measure to rank in Values.

Keep the sort reliable

For repeatable results, use a clearly defined numeric source column, confirm the PivotTable summarizes it as intended, and sort from a value cell or explicitly choose the measure in More Sort Options. When you add records, verify the source range and refresh before comparing rankings. These habits help separate a genuine sort problem from a change in the data, calculation, filter, or report structure.

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.