If an Excel PivotTable will not sort, first select an item or value in the field you want to order, then use the field’s sort controls: right-click and choose Sort, or open the Row Labels or Column Labels drop-down. For a value-based order, select a number in the relevant values column before choosing Sort; sorting the wrong field or the wrong kind of cell is a common cause.
Choose the kind of sort you need
A PivotTable can sort its displayed items alphabetically, chronologically, numerically, or according to a summarized value. Those are different operations. Sorting the source worksheet does not necessarily rearrange the PivotTable, and sorting a PivotTable does not rearrange the underlying source records.
- Sort labels: Order row or column items such as product names from A to Z, or dates from oldest to newest. Open the arrow beside Row Labels or Column Labels and choose an ascending or descending option.
- Sort by a value: Arrange categories by a measure, such as sales from largest to smallest. Click a number in the sales column, right-click, choose Sort, and select the desired direction.
- Manual order: Place selected items in a custom sequence. This is useful for a workflow such as New, In Progress, and Complete, but it can conflict with automatic sorting.
Before changing settings, identify both the field to sort and the basis for the order. For example, “sort products by name” means a label sort, while “sort products by total revenue” means a value sort.
How PivotTable sorting works
A PivotTable summarizes records from a source range, Excel table, or other connection. It displays field items in rows or columns and calculates values such as sums or counts. Sorting changes the order of those displayed items; it does not simply sort every source row as if the PivotTable were a normal range.
When you sort labels, Excel orders the items in that particular PivotTable field. When you sort by a value, Excel uses the summarized result associated with each item. Suppose a report has product names in rows and quarterly sales in columns. To rank products by fourth-quarter sales, select a number under the fourth-quarter heading and sort from largest to smallest. Selecting a number under the grand total instead ranks products by the overall total.
That distinction explains many apparent failures. If you click a product label and choose a value sort, Excel may not use the measure or column you had in mind. If the report contains several value fields, make sure the selected number belongs to the intended measure. A PivotTable also cannot independently sort one cell inside a summarized column while keeping its row items in place; the row or column field items move as a group.
Sort a PivotTable by labels or values
In Excel for Microsoft 365 on Windows, try the direct controls first. The contextual menu usually provides the quickest check, while the field drop-down and More Sort Options offer more control.
- For alphabetical or date order, select a label in the field, or click the arrow beside Row Labels or Column Labels. Choose Sort A to Z or Sort Z to A; date fields may show oldest-to-newest or newest-to-oldest wording.
- For a numeric ranking, select a value cell in the measure column that should determine the ranking. Right-click it, choose Sort, then choose Largest to Smallest or Smallest to Largest.
- For a particular field or measure, open the field drop-down and choose More Sort Options. In the dialog, choose ascending or descending and, where available, select the value field to sort by. Check that the selected field and measure match the report you want.
- Check the result at the correct level. If the PivotTable has nested row fields, such as Region and then Product, sorting products affects products within their parent groups; it does not necessarily reorder the regions.
On Mac, the same general approach applies, although ribbon labels and context-menu wording can differ by Excel release. Select a PivotTable item and look for the sort choices in the contextual PivotTable ribbon or its right-click menu. In Excel for the web, available controls can vary by report and account; if a detailed sort dialog is missing, open the workbook in the desktop app to use the full options.
When the sort command seems to do nothing
Start by checking whether the visible order is already correct for the chosen sort. A descending sort of a field with equal values will not produce an obvious change. Likewise, sorting by grand total can look different from sorting by an individual month or region, even though Excel has applied the requested order.
Other common causes include selecting a label when you intend to sort by a value, selecting the wrong value column, or sorting a nested field at a different level than expected. If the field has only one item, or filters leave just one visible item, there may be nothing to rearrange. Clear or adjust the relevant field filter and try again.
Look for manual ordering, too. If items have been dragged into a custom sequence, an alphabetical sort may not appear to persist, especially when the field is configured to sort automatically after an update. Use the field’s sort menu to choose a defined order rather than dragging, or use manual ordering deliberately and avoid expecting it to behave like a continuously refreshed alphabetical sort.
- Confirm the active cell is inside the PivotTable, not in the source data or a neighboring worksheet range.
- For a value sort, select a number in the exact measure and period you want to use.
- Check for filters that hide the items you expect to see.
- Check for nested fields and determine whether you are sorting the parent field or a child field.
- Try the field drop-down’s More Sort Options when the quick sort gives an unexpected order.
Check the source data and value types
A PivotTable can only summarize and sort the data it receives. If a number column contains a mixture of real numbers and numbers stored as text, Excel may summarize or order the items in a way that does not match your expectations. For instance, text values such as “100” and “20” can behave differently from numeric values when they are sorted as text. Inspect the source column for inconsistent formats, leading apostrophes, extra spaces, or imported values that Excel did not recognize as numbers.
Dates deserve the same check. A column that looks like dates may contain a mix of genuine Excel dates and text strings. If the entries sort alphabetically or appear in an unexpected sequence, verify that the source cells are actual date values, not merely text formatted to look like dates. Correct the source consistently, then refresh the PivotTable.
Also verify the summary calculation. If Excel is counting records instead of summing amounts, sorting by that value field ranks categories by record count, not sales. Select a value in the PivotTable and check the value field’s settings from the contextual PivotTable Analyze tab, using Field Settings or Value Field Settings as appropriate. The command name can vary with whether the selected item is a row field or a value field.
Blanks and errors in the source can create visible blank items or unexpected groups. They do not always prevent sorting, but they can make the result seem wrong. Review the source range, especially if the data was pasted from a report or exported from another system.
Refreshes, filters, and sort order changes
If the source records have changed, refresh the PivotTable before judging its order. Select a cell in the report and choose PivotTable Analyze > Refresh, or use Data > Refresh All to refresh workbook connections and PivotTables. A refresh updates the summary from its source; it does not repair incorrectly typed source values or guarantee that a manually arranged order will match a newly desired sort.
For a field that keeps returning to an unwanted order after refresh, open its sort settings through the field drop-down and More Sort Options. Review the selected direction and the option to sort automatically whenever the report is updated. Automatic sorting is useful when new categories should be placed in order, but it is not suitable if you want a stable custom sequence. Choose one approach intentionally rather than alternating between dragging items and automatic sorting.
Filters affect what is visible, so a correct sort can still look incomplete. A report filtered to a few products ranks only the visible items in that context. Check the filter icons on row and column fields, as well as slicers and report filters, before concluding that categories have disappeared or failed to move.
Special cases: custom lists, grouped fields, and data models
Some labels follow a meaningful order that is not alphabetical. Months, weekdays, or business stages may be arranged according to an Excel custom list or a manual order. If months appear alphabetically, inspect the field’s sort options and the underlying date field. When possible, use real dates and group or display them appropriately rather than relying on month names stored as text. Text month names do not inherently tell Excel which year or date they represent.
Grouped dates and numbers can also change what is being sorted. A PivotTable might show years and quarters rather than individual dates; sorting the displayed year field orders the groups, not every date in the source. If grouping is not what you intended, inspect the field layout and the source values before changing the sort direction.
PivotTables based on an OLAP source or the Data Model can have restrictions that do not apply to a simple worksheet range. The source may define member order or limit which fields can be sorted by a measure. If the detailed sort choices are unavailable or disabled, check the connection and PivotTable field layout. The limitation may come from the data source rather than a broken worksheet command.
A reliable troubleshooting sequence
Work from the displayed report back to the source instead of repeatedly clicking sort commands. This sequence narrows down whether the problem is selection, configuration, filtering, or data quality:
- Decide whether the desired order is alphabetical, chronological, numerical, or based on a summarized measure.
- Select a label for a label sort, or a value under the exact measure and period for a value sort.
- Use the relevant field’s drop-down or right-click Sort; open More Sort Options if the quick choices are insufficient.
- Check field nesting, filters, slicers, and whether only one item is visible.
- Confirm that the value field uses the intended summary, such as Sum rather than Count.
- Inspect the source for mixed text and numeric values, text dates, blanks, or errors; correct the source and refresh.
- If the order changes after refresh, review automatic sorting and decide whether the field should use an ongoing sort or a manual custom sequence.
This method is more reliable than sorting the source worksheet and assuming the PivotTable will follow. The report has its own field layout, filters, summary calculations, and sort settings, so each should be checked in the context of the report.
Frequently Asked Questions
Why does my PivotTable sort alphabetically instead of by sales?
You likely sorted the row labels rather than the sales values. Select a sales number in the period or measure you want to rank by, then right-click and choose Sort > Largest to Smallest. For more control, use More Sort Options and specify the value field.
Why does my PivotTable sort only within each group?
The field is probably nested under a parent row field. For example, Product may sort within each Region while Region stays in its existing order. Select and sort the parent field separately if you want to change the order of regions too.
Why does the PivotTable order change after I refresh?
The field may use automatic sorting, or a custom manual order may not match the refreshed items. Review More Sort Options for the field and check its automatic-sort setting. Also confirm that newly added source items are meant to follow an alphabetical or value-based order.
Can I sort the source data and have the PivotTable follow?
Not reliably as a way to control the PivotTable’s displayed order. The PivotTable has its own sort controls. Sort the field in the report itself, and refresh after source changes so the summary reflects the latest records.
Why are dates or numbers in the wrong order?
The source column may mix real dates or numbers with text that only looks like those values. Check representative source cells, standardize the column’s data, and refresh the report. Also make sure the PivotTable is displaying the date or number field you intend to sort.
Related
- Excel Number Format for Negative Numbers in Parentheses
- Excel Number Format for Lakhs With Commas
- Excel Number Format for Millions With One Decimal Place
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- 3D Pie Chart in Excel: How to Create and Format ItTo create a 3-D pie chart in Excel for Microsoft 365 on Windows, select a two-column list of…
- Excel Bar Over Bar Graph: How to Overlay Two Data SeriesTo put one data series over another in an Excel bar chart, select a bar, open Format Data…
- How to Format Data for a Combo Chart in ExcelTo format data for an Excel combo chart, put category labels in the first column, give each data…
- Inserting an Excel Chart Into PowerPoint: Steps and OptionsTo insert an Excel chart into PowerPoint, select the chart in Excel, press Ctrl+C, then in PowerPoint choose…
- 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…
- Excel Copy Trendline Equation to a Cell: What WorksYou cannot directly link an Excel chart’s displayed trendline equation to a worksheet cell with a built-in formula.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.