Yes, you can sort a pivot table by the sum of a value field. Click the arrow in the row or column label header, choose More Sort Options, then select Descending (Z to A) by and pick your sum field from the drop-down. This sorts the entire pivot table so the largest sum appears first. Below I explain every method, including manual drag-and-drop, custom lists, and troubleshooting common sorting problems.
Why Sorting by Sum Matters in Pivot Tables
A pivot table’s default order is alphabetical for text and chronological for dates. That rarely shows the most important data first. When you have a sales report by product, you usually want the highest-selling product at the top, not the one that starts with “A”. Sorting by sum value reorders the row or column labels based on the numeric total of a value field (like Sum of Sales, Count of Orders, or Average of Price). This instantly highlights your best or worst performers and makes the table readable for stakeholders.
How to Sort by Sum Value (Step-by-Step)
The fastest way uses the field header’s context menu. Here’s the exact process in Excel for Microsoft 365 on Windows:
- Click the drop-down arrow on the row label (e.g., “Product”) or column label (e.g., “Region”) in the pivot table.
- Choose More Sort Options (at the bottom of the menu).
- In the Sort dialog, select Descending (Z to A) by or Ascending (A to Z) by.
- From the drop-down next to that, pick the value field you want to sort by (e.g., “Sum of Sales”).
- Click OK.
The pivot table instantly reorders all labels based on that field’s totals. On a Mac, the steps are identical—the dialog looks the same. In Excel for the web, the process is also the same, though the dialog may appear as a side pane.
Sorting by a Specific Value Field When You Have Multiple
If your pivot table has several value fields (e.g., Sum of Sales and Sum of Profit), you must explicitly choose which one to sort by. The sort dialog’s drop-down lists all value fields in the table. For example, with “Sum of Sales” and “Sum of Profit”, you can sort by either. If you want to sort by a calculated field or a custom measure, ensure that field is added to the Values area first—otherwise it won’t appear in the sort drop-down.
Manual Sorting and Custom Lists
Sometimes you want a specific order that isn’t based on values—like sorting months chronologically (Jan, Feb, Mar) instead of alphabetically (Apr, Aug, Dec). Excel automatically sorts date fields chronologically, but for text like “Q1, Q2, Q3, Q4”, you may need a custom list. To do this:
- Go to File > Options > Advanced (on Windows) and scroll to “General”, then click Edit Custom Lists.
- Create a list like “Q1,Q2,Q3,Q4” and click Add.
- In the pivot table, use the sort dialog and choose More Options > First key sort order and pick your custom list.
On Mac, go to Excel > Preferences > Custom Lists. This is useful for sorting by a non-numeric hierarchy like “Low, Medium, High”.
Sorting by Sum Value When Using Slicers and Filters
When you apply a slicer or a report filter, the pivot table’s visible data changes. Sorting by sum value always reflects the current filtered data. For example, if you filter to a single region, the sort will reorder based on that region’s sums only. This is helpful, but be aware that if you later clear the filter, the sort order remains as it was—it does not automatically recalculate to the new dataset. To re-sort after changing filters, repeat the sort steps.
Common Problems and Fixes
Here are frequent issues people hit when sorting by sum value:
- Sort option is grayed out: This happens if the pivot table is in “Compact” layout with multiple row fields. Click anywhere in the pivot table, go to PivotTable Analyze > Field Settings (or right-click > Field Settings) and under Layout & Print, choose “Outline” or “Tabular” layout. Then the sort option becomes available.
- Sort by sum doesn’t work with “Show Values As” percentages: If you’ve changed the value field to “% of Grand Total”, the sort still works, but it sorts by the percentage, not the raw sum. To sort by the raw sum, add a second copy of the field as “Sum” and sort by that.
- Sort order resets after refreshing: Excel does not remember your sort if you refresh data that changes the field order. After refreshing, re-apply the sort. This is a known limitation.
- Sorting by a subtotal: If you have grouped fields, the sort applies to the entire row label, not individual subtotals. To sort within a group, you need to sort each subgroup separately.
Comparing Sort Methods: Context Menu vs. Ribbon vs. Right-Click
There are three main ways to sort by sum value:
- Context menu on the field header: Quickest, as described above.
- Ribbon: Select any cell in the pivot table, go to Data > Sort (or PivotTable Analyze > Sort in older versions), then choose the sort options. This opens the same dialog.
- Right-click a cell in the value area: Right-click on a number in the Sum of Sales column, choose Sort, then Sort Largest to Smallest. This sorts the entire pivot table by that column’s values.
All methods produce the same result. The right-click method is often fastest because you don’t need to locate the field header.
Sorting by Sum When Using Pivot Charts
If you create a pivot chart from your pivot table, the chart’s category order follows the pivot table’s sort order. So if you sort by sum descending, your bar chart will show the largest bar first. However, if you later change the sort in the chart (by clicking the sort buttons on the chart’s axis), it may override the pivot table’s sort. To keep them in sync, always sort from the pivot table, not the chart.
Frequently Asked Questions
Why can’t I see the “More Sort Options” option?
This option appears only when you click the drop-down arrow on a row or column label. If you don’t see the arrow, make sure the pivot table is selected and the field has a header. In some layouts, the arrow is hidden until you hover over the label.
Can I sort by sum value in a pivot table that uses “Count” instead of “Sum”?
Yes. The sort dialog lists all value fields, including Count of Orders, Average of Price, etc. Just pick the field you want. The sort will use that field’s values.
How do I sort by sum value in Excel for the web?
Excel for the web supports the same sort dialog. Click the field header arrow, choose “More Sort Options”, and select your value field. The interface is nearly identical to desktop Excel.
Does sorting by sum value affect the underlying data?
No. Sorting only changes the display order of the pivot table. Your source data remains untouched. You can always re-sort or clear the sort without losing any data.
Why does my pivot table sort by sum but then reverts to alphabetical when I refresh?
Excel does not persist manual sort settings across refreshes if the underlying data changes the field list or order. After refreshing, re-apply the sort. This is a known behavior, not a bug. To avoid it, you can use a macro to re-sort automatically, but that requires VBA.
Conclusion
Sorting a pivot table by sum value is a simple yet powerful way to make your data more insightful. Whether you use the context menu, ribbon, or right-click method, the key is to select the correct value field in the sort dialog. Remember to re-apply after refreshing and to use custom lists for non-numeric orders. With these techniques, you can quickly turn a cluttered pivot table into a clear ranking of your most important numbers.
Related
- Excel: How to Split Information in a Cell
- How to Rotate a Table in Excel: 3 Easy Ways
- Excel How to Select Certain Cells: 6 Quick Ways
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Gantt Chart Template: Word or Excel for Your Project?Choose an Excel Gantt chart template if your schedule will change, needs date-based bars, or must track progress;…
- Excel Chart Ignore Blank Cells? How to Control GapsYes. In Excel for Microsoft 365 on Windows, select the chart, choose Chart Design > Select Data >…
- Excel Pivot Table Unique Count: How to Get ItYes—Excel can count unique values in a PivotTable. In Excel for Microsoft 365 on Windows, create the PivotTable…
- Excel Pie Chart: How to Change the Order of SlicesTo change the order of slices in an Excel pie chart, reorder the category and value rows in…
- Pivot Chart Slicers: How to Add and Use ThemYes. In Excel for Microsoft 365 on Windows, select the PivotChart, choose PivotChart Analyze > Insert Slicer, select…
- Funnel Chart Creator in Excel: How to Build One EasilyExcel for Microsoft 365 includes a built-in funnel chart creator: put stage names in one column and their…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.