To sort a pivot table by values in Excel, click any cell inside the Values area (for example, the Sum of Sales column), then use the Sort A to Z or Sort Z to A buttons on the Data tab, or right-click and choose Sort. You can also apply more advanced sorting via the field’s sort options in the PivotTable Fields pane. This article explains the different ways to sort, why your pivot table might not sort the way you expect, and how to fix common sorting problems.
Why Sorting a Pivot Table Is Different
Unlike a normal range, a pivot table is a calculated summary of your data. When you sort a pivot table, you are not moving raw cells—you are reordering the categories (rows or columns) based on the values in the data area. Excel gives you two main sorting levels:
- Label sorting – sorts row or column labels alphabetically or in reverse alphabetical order.
- Value sorting – sorts the labels based on the numbers in the Values area (e.g., largest to smallest total).
Understanding this distinction is key because the sort buttons on the Data tab will sort by the selected field’s labels by default, not by the values. To sort by values, you need to use the sort options that apply to the field that contains the values.
How to Sort Pivot Table Values (Step-by-Step)
Here is the most direct method for sorting by values in Excel for Microsoft 365 on Windows:
- Click any cell within the column or row that contains the values you want to sort. For example, if you have a pivot table with Product in rows and Sum of Sales in values, click on any number in the Sum of Sales column.
- Go to the Data tab on the ribbon.
- Click the Sort Largest to Smallest (or Sort Smallest to Largest) button. This sorts the row labels based on the values in the column you clicked.
Alternatively, right-click a value cell, choose Sort, and then select Sort Largest to Smallest or Sort Smallest to Largest.
If you want to sort by a specific value field when there are multiple value fields (e.g., Sum of Sales and Sum of Profit), click on a cell in the column of that value field first. The sort will apply to that field’s totals.
On a Mac, the steps are the same: click a value cell, then use the Data tab or right-click > Sort. The ribbon layout is similar.
Using the Field Settings for More Control
For more advanced sorting—like sorting by a custom order or sorting by a subtotal—use the field’s sort options:
- Click the drop-down arrow next to the row label field (e.g., “Product”) in the PivotTable Fields pane.
- Choose More Sort Options.
- In the Sort dialog box, select Ascending or Descending, and then choose the field to sort by from the “Sort by” drop-down. You can select a value field like “Sum of Sales” or a label field.
This method also lets you choose Manual sorting, which allows you to drag items in the pivot table manually. Note that manual sorting is only available when you have not applied an automatic sort.
Why Your Pivot Table Isn’t Sorting Correctly
Common issues that prevent proper value sorting include:
- You clicked on a label cell instead of a value cell. Clicking on a row label (e.g., “Product”) sorts alphabetically, not by value.
- Multiple value fields. If you have more than one value field, you must click on the specific column you want to sort by. Otherwise, Excel may sort by the first value field.
- Grouped fields. If you have grouped dates or numbers, sorting may be limited to the group order, not the underlying values.
- Manual sorting is enabled. If you previously dragged items manually, Excel may not apply automatic sorts until you reset the sort options.
- Pivot table is in Compact Layout. In compact layout, row labels are in a single column, and clicking on a value cell may not always select the entire field. Try switching to Outline or Tabular layout to make sorting more predictable.
If sorting still fails, try refreshing the pivot table (right-click > Refresh) to ensure the data is up to date.
Sorting by Values in a Pivot Chart
When you create a pivot chart from a pivot table, the chart reflects the sort order of the pivot table. To change the chart’s order, you sort the pivot table itself. There is no separate sort option inside the chart. If you want the chart to show data in a different order without changing the table, you can create a regular chart from the pivot table’s data, but that breaks the connection to the pivot table.
Comparing Sorting Methods: Manual vs. Automatic
Excel offers two sorting paradigms in pivot tables:
- Automatic sorting – based on labels or values. This is dynamic: when the underlying data changes and you refresh, the sort order updates automatically.
- Manual sorting – you drag items into a custom order. This is static: it stays the same even after refresh, unless you reapply an automatic sort.
Manual sorting is useful for custom orders like “High, Medium, Low” or a specific product sequence. To enable manual sorting, select the field, go to More Sort Options, and choose “Manual.” Then drag items in the row label area. Note that if you later apply an automatic sort, the manual order is lost.
Common Misconception: Sorting the Source Data
Many users think that sorting the source data will automatically sort the pivot table. That is not true. The pivot table has its own sort order that is independent of the source data. Even if you sort the underlying data, the pivot table will not change unless you refresh it and then apply a sort. To sort the pivot table, you must use the pivot table’s own sort features.
Frequently Asked Questions
How do I sort a pivot table by values in Excel?
Click any cell in the values column you want to sort by, then go to the Data tab and click Sort Largest to Smallest or Sort Smallest to Largest. Alternatively, right-click the value cell and choose Sort. This will reorder the row labels based on the numbers in that column.
Why is my pivot table sorting alphabetically instead of by value?
You likely clicked on a row label cell instead of a value cell. Clicking on a label sorts by label text. To sort by value, click on a number cell in the values area, then use the sort buttons. Also check if you have multiple value fields and are clicking on the correct column.
Can I sort pivot table values in descending order automatically?
Yes. After applying a sort, Excel will keep that sort order until you change it. If you refresh the pivot table with new data, the sort order remains the same, but the items will reorder based on the new values. To set a default sort, use the field’s More Sort Options and choose the value field.
How do I sort a pivot table by a custom list (e.g., High, Medium, Low)?
Use manual sorting. Select the row label field, go to More Sort Options, choose “Manual,” then drag the items in the order you want. You can also create a custom list in Excel’s Advanced Options, but the manual drag method is simplest.
Why does my pivot table sort not work after I refresh?
If you have applied a manual sort, refreshing will not change the order. If you have an automatic sort, it should reapply after refresh. If it doesn’t, check that the sort option is still set to the correct value field. Also, ensure you clicked on a value cell before applying the sort.
Can I sort pivot table values by a calculated field?
Yes. If you have a calculated field (e.g., Profit = Sales – Cost), you can sort by that field just like any other value field. Click on a cell in the calculated field’s column and use the sort buttons.
Related
- Excel Normalize Data Range: 3 Easy Methods
- Excel Name Manager Dynamic Range Explained
- Excel Mobile Repair Grand Rapids: Fast Phone Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Add a Line in an Excel Org ChartTo add a reporting line in an Excel org chart, select the employee’s box and use SmartArt Design…
- Excel Radar Charts Over Time: Setup and Better OptionsYes—Excel can use a radar chart to compare measurements from different dates, but a radar chart does not…
- How to Add a Trendline in an Excel GraphTo put a trendline in an Excel graph, select the chart, choose Chart Design > Add Chart Element…
- Add Power Pivot to Excel: Enable It and Get StartedTo add Power Pivot in Excel for Microsoft 365 on Windows, select File > Options > Add-ins, choose…
- Excel Chart Title Formula: Link a Title to a CellTo make an Excel chart title dynamic, select the chart title, click the formula bar, enter a reference…
- Excel Pivot Chart Secondary Y Axis: How to Add OneYes. In Excel for Microsoft 365 on Windows, select a PivotChart data series, open Format Data Series, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.