Sorting Values in Pivot Table: Quick Guide

Charts & pivot tables · Updated November 22, 2026

To sort values in a pivot table, click the arrow in the row or column label header (or the Value field’s drop-down) and choose Sort A to Z, Sort Z to A, or More Sort Options to sort by the numeric values in a specific field. For example, if you have a sales pivot table with products in rows and total sales in the Values area, clicking the Row Labels arrow and selecting “More Sort Options” > “Descending (Z to A) by” > “Sum of Sales” instantly reorders the products from highest to lowest sales.

Why Pivot Table Sorting Works Differently Than Normal Ranges

In a regular spreadsheet range, sorting rearranges the actual cell contents. A pivot table is a dynamic summary that recalculates whenever the underlying data changes, so its sort order is stored as a property of the pivot table’s fields, not as a static arrangement of cells. This means you can sort by a value field (like Sum of Sales) even though that field isn’t displayed as a column in the pivot table layout. The sort order persists when you refresh the pivot table, and it applies to the entire field level—so sorting by one value field affects all rows or columns in that field group.

A common misconception is that you must manually drag rows to reorder them. While dragging works, it only changes the visual order temporarily and often gets lost on refresh. Using the built-in sort options is the reliable method because Excel stores the sort criteria with the pivot table definition.

How to Sort by Values in Excel (Microsoft 365)

Here are the exact steps for Excel for Microsoft 365 on Windows. On a Mac, the menus are the same but the keyboard shortcuts differ (use Cmd instead of Ctrl).

  1. Click anywhere inside the pivot table to activate the PivotTable Analyze and Design tabs on the ribbon.
  2. Click the drop-down arrow in the Row Labels or Column Labels header cell (the one that shows the field name, like “Product”).
  3. Choose More Sort Options from the bottom of the menu.
  4. In the Sort dialog, select Descending (Z to A) by or Ascending (A to Z) by.
  5. In the drop-down next to that option, pick the value field you want to sort by—for example, “Sum of Sales” or “Count of Orders”.
  6. Click OK.

Alternatively, you can right-click a number in the Values area (like a total in the Sum of Sales column), then choose Sort > Sort Largest to Smallest or Sort Smallest to Largest. This is often faster because it directly targets the numeric field you’re looking at.

For Excel for the web, the steps are the same: click the row/column label arrow, then use the sort options. The right-click method also works.

Sorting by Multiple Levels (Nested Fields)

If you have multiple fields in the Rows area (for example, Region then Product), sorting by a value field will sort within each parent group, not globally across all rows. This is a key behavior to understand. Suppose your pivot table shows Region in Row Labels and Product as a sub-field. If you sort by Sum of Sales descending, Excel will sort the products within each region from highest to lowest, but the regions themselves will remain in their original order unless you also sort the Region field.

To sort the parent field (Region) by its total sales, you need to sort by the value field while the parent field is the only one selected, or use the “More Sort Options” dialog and choose the parent field as the sort-by field. In practice, most users sort the parent field first (e.g., Region by total sales), then sort the child field (Product) within each region. Excel remembers both sort orders independently.

How to Sort Manually or by Custom Lists

Sometimes you want a specific order that isn’t alphabetical or numeric—like sorting months chronologically (Jan, Feb, Mar) or sorting by a custom priority (High, Medium, Low). Excel’s default sort often handles months and weekdays correctly because they are recognized as date/time formats, but for custom lists you have two options:

  • Use a custom list: Go to File > Options > Advanced > Edit Custom Lists. Create a list like “High, Medium, Low”. Then in the pivot table sort dialog, choose “More Options” and check “First key sort order” to select your custom list.
  • Manual drag: Click a single cell in the row label column, then hover over its border until the cursor becomes a four-sided arrow, and drag it to the desired position. Excel will reorder that item within its group. This is useful for one-off adjustments but won’t persist if you refresh and then re-sort.

For chronological sorting of months, ensure your source data has real dates (not text). If the months are text like “Jan”, Excel may sort alphabetically (Apr, Aug, Dec). To fix this, convert the month text to a real date in the source data, or use a numeric month column.

Common Problems and Fixes

Several issues can confuse users when sorting pivot tables:

  • Sort options are grayed out: This happens when you click a cell in the Values area that isn’t part of a row/column label. Click the actual row label cell (e.g., the cell containing “Product”) to enable the sort menu.
  • Sort order resets after refresh: If you manually dragged items, the order resets. Using the built-in sort options should persist, but if you have a calculated field or a filter that changes, re-apply the sort after refreshing.
  • Sorting by a value field that isn’t in the layout: You can still sort by it if you add it to the Values area temporarily, or use the More Sort Options dialog—it lists all value fields in the pivot table, even if they’re not displayed.
  • Numbers are sorting as text: This occurs when the source data column is formatted as text. Ensure the source column contains real numbers, not text that looks like numbers. You can check by selecting the column and looking at the Number Format in the Home tab.
  • Sorting doesn’t affect grand totals: The Grand Total row/column is always at the bottom/right by default. To change that, you can right-click the Grand Total label and choose “Move” to place it at the top/left, but sorting won’t reposition it relative to the data rows.

Sorting in Google Sheets vs. Excel

Google Sheets pivot tables have a similar but slightly different interface. In Google Sheets, click the row or column field’s drop-down arrow in the pivot table editor (the panel on the right). You’ll see a “Sort by” option where you can choose the field and order (Ascending/Descending). You can also sort by a value field by selecting it from the list. In Excel, the sort dialog is more prominent, but the logic is identical. One difference: Google Sheets does not support custom list sorting in pivot tables natively—you’d need to add a helper column with a numeric rank.

For Excel, if you need to sort by a custom list, you must set it up in Excel Options as described above. Google Sheets users can work around this by adding a column with a sort order number in the source data, then including that field in the pivot table and sorting by it.

Frequently Asked Questions

How do I sort a pivot table by a specific value column without changing the row order?
Click the drop-down on the Row Labels header, choose More Sort Options, then select the value field you want to sort by. This reorders the rows based on that value field’s totals, not just the displayed column.

Why does my pivot table sort alphabetically instead of by number?
This usually means the value field isn’t being used as the sort key. Make sure you choose “Descending by” or “Ascending by” and select the numeric value field in the dialog. Also check that the source data column is formatted as numbers, not text.

Can I sort a pivot table by a calculated field?
Yes. Calculated fields appear in the value field list in the sort dialog. Select the calculated field name as the sort key, and Excel will sort by its computed values.

How do I keep the sort order after refreshing the pivot table?
Use the built-in sort options (not manual dragging). Excel stores the sort criteria with the pivot table, so it persists across refreshes. If you manually dragged items, the order will reset on refresh.

What is the fastest way to sort largest to smallest in a pivot table?
Right-click any number in the Values area (like a sales total), then choose Sort > Sort Largest to Smallest. This applies the sort to the corresponding row or column field instantly.

Does sorting a pivot table affect the original data?
No. Sorting a pivot table only changes the display order of the summary. The underlying source data remains untouched. This is a key difference from sorting a regular range.

Practical Example: Sorting Sales Data

Assume you have a pivot table with products in rows and Sum of Sales in the Values area. To see the best-selling products first:

  1. Click the cell that says “Row Labels” (or the product field name).
  2. Click the drop-down arrow that appears.
  3. Select “More Sort Options”.
  4. Choose “Descending (Z to A) by” and pick “Sum of Sales”.
  5. Click OK.

Your products now appear from highest total sales to lowest. If you have a filter (like a date range), the sort applies to the visible data only. Remember that if you add a new product later and refresh, it will be inserted in the correct sorted position automatically.

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.