Excel Pivot Table Sort by Value Field: How-To

Charts & pivot tables · Updated November 22, 2026

Yes, you can sort a pivot table by a value field directly. Click any number in the value column you want to sort by, then use the Sort A to Z or Sort Z to A buttons on the Data tab, or right-click and choose Sort. This reorders the row or column labels based on that column’s numeric values.

Why Pivot Table Sorting Works Differently

In a regular worksheet range, sorting rearranges entire rows. In a pivot table, sorting only affects the labels (row or column headings) and their associated values. The underlying source data is never moved. This is a key distinction: when you sort a pivot table by a value field, you are changing the display order of the categories, not the data itself. For example, if you have a pivot table of monthly sales by product, sorting by the “Sum of Sales” column descending will list the top-selling product first. The source data remains untouched.

How to Sort by a Value Field (Step by Step)

These steps work in Excel for Microsoft 365, Excel 2021, 2019, and 2016 on Windows. The process is identical on Mac, except the right-click menu is the same but the ribbon buttons are under the Data tab as well.

  1. Click any single cell inside the value field column you want to sort by. For instance, if your values are in column B (Sum of Sales), click any number in column B.
  2. Go to the Data tab on the ribbon.
  3. Click either Sort A to Z (ascending, smallest to largest) or Sort Z to A (descending, largest to smallest).
  4. Alternatively, right-click the same cell, hover over Sort, and choose Sort Largest to Smallest or Sort Smallest to Largest.

That’s it. The row labels will reorder instantly. If you have multiple value fields (e.g., Sum of Sales and Count of Orders), the sort applies only to the column you clicked. To sort by a different value, click a cell in that column and repeat.

Sorting by a Value Field in a Pivot Chart

When you create a pivot chart from a sorted pivot table, the chart inherits the sort order. If you want to change the chart’s order without altering the table, you cannot do it directly on the chart. You must sort the pivot table first, then refresh the chart. Alternatively, you can sort the underlying pivot table using the field list’s sort options. To access those, click the drop-down arrow on the row label field in the pivot table, then choose More Sort Options. There you can select “Descending (Z to A) by” and pick the value field to sort by.

Common Problems and Fixes

  • Sort buttons are grayed out: This happens when you have not selected a cell inside the pivot table, or you have selected a cell in the label area. Click a value cell first.
  • Sorting by one value column also reorders another: That is expected if the pivot table has multiple row levels. The sort applies to the entire row label hierarchy. To avoid this, use the field list’s “More Sort Options” and choose “Manual” for the secondary level, then sort only the top level.
  • The sort resets when you refresh: If you have added new items to the source data, the pivot table may revert to alphabetical order. To force a persistent sort, use the field list’s sort options (right-click the row field > Field Settings > Layout & Print > check “Sort by”) and select the value field. This locks the sort.
  • Numbers are sorting as text: This occurs if the source data column is formatted as text. Ensure the source column contains real numbers, not text that looks like numbers. In the source data, select the column and use Data > Text to Columns > Finish to convert text to numbers.

Sorting by a Custom List or Manual Order

Sometimes you want a specific order that isn’t alphabetical or numeric, like sorting weekdays as Mon, Tue, Wed, not alphabetically. Excel respects custom lists. Go to File > Options > Advanced > Edit Custom Lists. Create a list like “Mon, Tue, Wed, Thu, Fri”. Then, in the pivot table, click the row label drop-down, choose More Sort Options, select “Manual” and then drag the items into your desired order. Alternatively, you can use the custom list by selecting “Ascending” and then in the field settings, choose “More Options” and check “Sort by custom list” – but the manual drag is simpler for most users.

Comparing Sort Methods: Ribbon vs. Right-Click vs. Field Settings

The three methods give the same result for a one-time sort, but they differ in persistence:

  • Ribbon buttons (Data tab): Fastest for a one-off sort. Does not lock the sort; refreshing may reset it.
  • Right-click > Sort: Same as ribbon, but also offers “More Sort Options” which opens a dialog where you can choose the sort key and direction.
  • Field Settings (right-click the row field > Field Settings > Layout & Print): This is the only method that makes the sort permanent across refreshes. You choose the value field to sort by, and the pivot table remembers it.

For most users, the right-click method with “More Sort Options” is the best balance of speed and control. Use Field Settings when you need the sort to survive data refreshes.

Sorting by a Value Field When There Are Multiple Value Columns

If your pivot table has two value fields side by side (e.g., Sum of Sales and Sum of Profit), clicking a cell in the “Sum of Sales” column sorts the labels by sales. Clicking a cell in the “Sum of Profit” column sorts by profit. The sort is always relative to the column you click. There is no way to sort by a value that is not displayed in the pivot table. If you need to sort by a calculated field, you must add that field to the Values area first, then sort by it.

Frequently Asked Questions

Can I sort a pivot table by a value field that is not shown? No. The sort key must be one of the value fields currently in the pivot table. If you need to sort by a hidden calculation, add it temporarily, sort, then remove it – but the sort will revert.

Why does my pivot table sort alphabetically after I refresh? Because the sort was a one-time action. Use Field Settings (right-click the row label > Field Settings > Layout & Print > check “Sort by” and choose the value field) to make it permanent.

How do I sort from largest to smallest in a pivot table? Click any number in the value column, then press the “Sort Z to A” button on the Data tab, or right-click > Sort > Sort Largest to Smallest.

Can I sort by a value field in a pivot table with a filter applied? Yes, sorting works on the visible items only. If you have a filter that hides some rows, the sort applies to the filtered subset.

Is there a keyboard shortcut to sort a pivot table? No direct shortcut exists. But you can use Alt + D + S (old Excel 2003 menu) on Windows to open the sort dialog, then choose the value field. On Mac, there is no equivalent shortcut.

Final Tips for Reliable Sorting

Always ensure your source data has no blank rows or columns, and that numbers are stored as numbers. If you plan to share the workbook, remember that custom sorts (like manual drag) are not automatically preserved when someone else opens the file unless you lock the sort via Field Settings. For most business reporting, sorting by the largest value (descending) is the most common need, and the right-click > Sort Largest to Smallest option is the quickest path.

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.