If your Excel pivot table won’t sort by values, the most common cause is that you’re trying to sort a field that contains text-formatted numbers, or you’re clicking the wrong sort option. To fix it, right-click any cell in the Values area, choose Sort, then select Sort Largest to Smallest (or Smallest to Largest). If that doesn’t work, check that the source data numbers are truly numeric (not text) and that the pivot table is refreshed.
Why Sorting by Values Fails in Pivot Tables
Pivot tables have a specific sorting behavior that differs from regular Excel ranges. When you click the drop-down arrow on a Row Label, you see sort options for that row field (alphabetical or custom), but the Values sort is hidden in the right-click menu. Many users try to sort by clicking the header of a value column (like “Sum of Sales”) and dragging, which doesn’t work. The sort must be initiated from within the pivot table’s field area.
Another common reason: if your source data contains numbers stored as text (e.g., with a leading apostrophe or formatted as “General” but left-aligned), Excel treats them as text and sorts them lexicographically (1, 10, 11, 2, 3…). This is especially common when data is imported from other systems.
How Pivot Table Sorting Actually Works
Pivot table sorting is based on the underlying data of the field you’re sorting. When you sort by values, Excel reorders the row labels based on the aggregated values (Sum, Count, Average, etc.) in the Values area. This is different from sorting by the row label text. The sort order is stored with the pivot table, so it persists when you refresh, but it can be overridden by manual drag-and-drop or by a custom list.
For example, if you have a list of products with sales, and you want to see the best-selling product first, you would right-click on a product name in the Row Labels area, select Sort, then Sort Largest to Smallest. Excel will reorder the products based on the sum of sales for each product.
Step-by-Step: How to Sort by Values Correctly
Here’s the exact process for Microsoft 365 on Windows (similar on Mac):
- Click anywhere inside the pivot table to activate the PivotTable Tools.
- Right-click on any cell in the Row Labels column (the field you want to sort).
- Hover over Sort in the context menu.
- Choose Sort Largest to Smallest (if your values are numeric) or Sort Smallest to Largest.
- If you want to sort by a specific value field (e.g., if you have multiple value fields like Sum of Sales and Count of Orders), right-click on a cell in that value column instead, then choose Sort and pick the order.
Alternatively, you can use the Sort by Value dialog: right-click a value cell, select Sort > More Sort Options, then choose the sort key and direction.
Common Problems and Their Fixes
Here are the most frequent issues and how to resolve them:
- Sort option is grayed out: This happens when the pivot table is in Compact Layout and you right-click on the subtotal or grand total cell. Right-click on a regular row label instead.
- Sorting resets after refresh: If your source data changes, the pivot table may lose the sort if a new item is added. To keep the sort, use the More Sort Options dialog and select “Manual” then re-apply, or use a custom list. Refreshing usually preserves the sort unless the data structure changes.
- Numbers are sorting as text: Select the source data column, go to Data > Text to Columns, and finish with the default settings to convert text numbers to real numbers. Or use the VALUE function in a helper column.
- Sort by values doesn’t work with multiple row fields: When you have multiple row fields, sorting by values only sorts within the outermost field. You may need to sort each inner field separately or rearrange fields.
- Pivot table is in a different sort order (e.g., custom list): Excel may apply a custom sort order (like months or weekdays). Go to File > Options > Advanced > Edit Custom Lists to see if a list is interfering, or clear it.
Sorting by Values When Using a Calculated Field
If you have a calculated field (e.g., Profit = Sales – Cost), sorting by that field works the same way: right-click on any value cell in that column and choose Sort. However, note that the sort is based on the calculated result, not the formula. If you need a custom sort order (e.g., by a ranking), you might need to add a helper column in the source data with a numeric rank.
Alternative Methods: Using Slicers and Filters
If sorting by values is still problematic, you can use a slicer to filter and then sort manually. Or, you can use the Filter option in the Row Labels drop-down to show only top/bottom items (e.g., Top 10). This is not the same as sorting, but it serves a similar purpose. To do this, click the drop-down arrow next to Row Labels, choose Value Filters, then Top 10.
Another alternative is to convert the pivot table to regular data using Copy and Paste Special as values, then sort normally. This is useful if you need a static report.
Frequently Asked Questions
Why does my pivot table sort alphabetically even when I choose “Sort Largest to Smallest”?
This usually means the values in the source data are stored as text. Check the source column: if numbers are left-aligned, they are text. Convert them to numbers using Text to Columns or by multiplying by 1 in a helper column, then refresh the pivot table.
How do I sort a pivot table by a specific value column when I have multiple value fields?
Right-click on a cell in the specific value column (e.g., “Sum of Sales”) and choose Sort. Excel will sort the row labels based on that column. If you right-click on a row label, it sorts by the first value field by default.
Can I sort by values in a pivot table on Mac?
Yes, the process is the same: right-click (or Control-click) on a row label or value cell, choose Sort, and select the order. The menu paths are identical in Excel for Mac.
Why does my sort order change when I refresh the pivot table?
If you have a custom sort order (like months) or if new items are added to the source data, the pivot table may revert to the default sort. To lock a sort, use the More Sort Options dialog and select “Manual” after applying the sort, or use a PivotTable style that preserves the order.
Is there a way to sort by values in a pivot table using a formula?
No, pivot table sorting is a manual operation. However, you can use the SORT function on the pivot table’s output if you convert it to a range, or use the GETPIVOTDATA function to extract data and sort it with formulas.
Final Tips for Reliable Pivot Table Sorting
To avoid sorting issues, always ensure your source data is clean: numbers as numbers, no blank rows, and no merged cells. Refresh the pivot table after any data changes. If you need a specific order (like a custom ranking), consider adding a rank column to your source data and sorting by that field instead of by values. And remember: when in doubt, right-click on the value cell, not the row label, to sort by that specific value.
Related
- Excel HR Templates for Attendance: A Practical Guide
- Excel: How to Split Information in a Cell
- How to Rotate a Table in Excel: 3 Easy Ways
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Waterfall Chart: Can You Add an Axis Break?No: Microsoft Excel for Microsoft 365 does not provide a built-in axis-break control for a native waterfall chart.…
- Excel Histogram Is Blank? Fix the Chart and Data IssuesAn Excel histogram is usually blank because its source range contains no usable numeric values, the chart points…
- Conditional Formatting Bar Chart in Excel: How It WorksTo make a bar chart inside Excel cells, select the values, then choose Home > Conditional Formatting >…
- Google Sheets Pivot Tables: Create, Customize, RefreshTo create a pivot table in Google Sheets, select a range of data, choose Insert > Pivot table,…
- Excel Remove Trendline: Steps, Effects, and FixesTo remove a trendline in Excel for Microsoft 365 on Windows, select the chart, then choose Chart Design…
- Excel Pivot Tables: Do They Update Automatically?No—an Excel PivotTable in Microsoft 365 for Windows does not normally update as soon as you change its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.