If an Excel PivotTable will not sort, select an item in the field you want to reorder, right-click it, and choose Sort followed by the appropriate ascending or descending option. If the result is still unexpected, check whether you are sorting labels or values, whether the source data contains mixed types, and whether a filter, custom list, or field hierarchy is affecting the order.
How PivotTable sorting works
A PivotTable is a summary of source data, not a normal range of cells. Its displayed rows and columns represent fields and grouped items, while its values are calculations such as sums, counts, or averages. Sorting a PivotTable changes the order of those displayed items; sorting the original worksheet cells does not necessarily change the PivotTable’s arrangement.
There are two common kinds of sort:
- Label sort: Orders field items alphabetically or numerically by their displayed labels, such as sorting product names from A to Z.
- Value sort: Orders the items in one field according to a summarized measure, such as sorting sales regions by total revenue.
These operations can produce very different results. If you choose A to Z while trying to rank regions by sales, Excel will sort the region names, not the sales totals. To rank by a measure, select a numeric value in the relevant PivotTable column before opening the sort commands.
Choose the correct sort command
In Excel for Microsoft 365 on Windows, the right-click menu is usually the clearest way to sort a PivotTable. To alphabetize row labels, right-click an item in the row field and choose Sort > Sort A to Z or Sort > Sort Z to A. For a column field, right-click one of its displayed items and use the corresponding label sort.
To rank items by a summary value, click or right-click a number in the value column you want to use. Choose Sort > Sort Largest to Smallest or Sort > Sort Smallest to Largest. For example, if the PivotTable shows each salesperson and a Sum of Sales column, right-click a sales total to rank salespeople by that total. Selecting a salesperson’s name instead may sort the names alphabetically.
When the simple options do not express the desired order, open Sort > More Sort Options from the field’s context menu. Depending on the field and layout, the dialog lets you choose ascending or descending order and identify the value field used for sorting. Check the displayed setting before confirming, especially when the PivotTable contains more than one measure.
Sort by a specific value field
A PivotTable can contain several measures, such as Sum of Sales, Count of Orders, and Average of Sales. Excel needs to know which measure should determine the ranking. Selecting a number from the wrong measure can make the sort appear broken even though Excel is following the instruction it received.
For a reliable value sort:
- Find the value column that represents the ranking you actually want, such as Sum of Sales.
- Right-click a number in that column, preferably in the row of an ordinary item rather than a grand total or subtotal.
- Choose the appropriate largest-to-smallest or smallest-to-largest command.
- Check several rows to confirm that the selected measure is moving in the intended direction.
If there are multiple value fields and the result remains unclear, use More Sort Options and inspect the sort-by selection. In a compact layout, several row fields may appear in one column, so confirm that you are acting on the intended field rather than a parent or child field in the same display.
Check filters, subtotals, and field hierarchy
Filters can make a correct sort look incomplete: Excel sorts only the items currently visible in the PivotTable. Clear or adjust the field filter if you expect to see every item. A report filter can also change the values used in a ranking, because the totals reflect only the data included by the current filter selections.
When a PivotTable has nested row fields—for example, Region above City—each field has its own items and ordering. Sorting cities does not necessarily reorder regions, and sorting regions does not alphabetize all cities across the entire report. The city items are arranged within their parent regions. Expand or collapse the hierarchy as needed, then apply the sort to the field whose items you intend to rearrange.
Subtotals and grand totals are calculations, not ordinary source items. Do not use a subtotal or grand total cell as the basis for a sort if you want to rank individual entries. Choose a value belonging to one item. Also remember that a report with subtotals may appear to have blocks that cannot be freely mixed: those blocks represent the PivotTable’s field structure.
Look for text, numbers, and blank cells in the source
Unexpected ordering often starts in the source data. A column that appears numeric may contain numbers stored as text, leading or trailing spaces, blanks, or a mixture of numbers and text. For example, product codes such as 2, 10, and 100 may sort alphabetically as 10, 100, 2 if Excel treats them as text. That order is not a failure of the PivotTable; it is a consequence of the data type or the fact that the values are identifiers rather than quantities.
Inspect the source column and make its values consistent before relying on a numeric or alphabetical order. Remove unintended spaces, convert text-formatted numbers to numbers when they represent quantities, and standardize category labels such as North, north, and North with a trailing space. Do not convert identifiers with meaningful leading zeros into ordinary numbers: an item code like 0042 may need to remain text so the zeros are preserved.
Blank entries can also become a visible (blank) item, and inconsistent labels can create separate PivotTable items that look nearly identical. Correct the source records, then refresh the PivotTable. If the source is an Excel Table, new rows are generally included when the Table expands, but the PivotTable still needs a refresh to incorporate the changed data.
Refresh the PivotTable after changing its data
A PivotTable may show an older view of its source because it has not been refreshed. On Windows, right-click inside the PivotTable and select Refresh, or use PivotTable Analyze > Refresh. To refresh PivotTables throughout the workbook, use Data > Refresh All. Refreshing updates the summary from its source; it does not repair inconsistent source values or automatically make an alphabetical sort into a value sort.
If you changed the source range itself, verify that the PivotTable still points to the intended data. Select the PivotTable and use PivotTable Analyze > Change Data Source to inspect or revise the source. When possible, format a changing data range as an Excel Table before creating the PivotTable; an expanding Table is easier to maintain than a fixed range that omits newly added rows.
Refreshing may also bring back source items that were previously absent or change totals enough to alter a value-based ranking. After refresh, reapply the sort if the displayed order does not match what you expect. Be aware that refresh behavior and saved sorting can depend on the PivotTable’s layout and settings.
Dates, custom lists, and manual ordering
Date fields can be grouped into years, quarters, or months. If a date field is grouped, you are sorting the displayed groups rather than necessarily sorting individual dates. Chronological ordering should normally follow date values, but text that merely looks like a date may sort alphabetically. Check that the source column contains real Excel dates and that the PivotTable is grouped as intended.
Excel can also use custom lists for familiar sequences such as weekdays or months. That can make items appear in calendar order rather than ordinary alphabetical order. If you expect Monday, Tuesday, Wednesday but see another sequence, the custom-list behavior may be intentional. Custom list sorting can be controlled in Excel’s sort options; availability and exact settings can vary by version and platform.
If the goal is an order that has no natural alphabetical or numeric sequence—such as Bronze, Silver, Gold—use a custom list or arrange the items manually if Excel allows it for that field. Manual order is not the same as sorting by a measure. A later refresh or a change to the PivotTable can affect manual arrangements, so document the intended sequence or add a sort-order column to the source when the order is important and must be repeatable.
When the sort options are unavailable or ineffective
If sort commands are disabled or do not behave as expected, first confirm that the active cell is inside the PivotTable and that you selected a field item or a relevant value—not a blank area, chart, or grand total. A protected worksheet may restrict changes. Some PivotTable layouts and connections to external or OLAP data sources also limit which operations are available; the available controls depend on the source and connection.
Try these checks in order:
- Click an actual row or column item, then open its context menu again.
- For ranking, select a number in the measure column that should control the order.
- Clear filters temporarily to see whether hidden items explain the apparent gap.
- Refresh the PivotTable after correcting or extending the source data.
- Check whether the field contains text, blanks, duplicates, or grouped date items.
- Look for a custom list or an existing manual order if the sequence is not alphabetical.
Do not try to fix a PivotTable sort by selecting and sorting a portion of its cells with the ordinary worksheet sort command. PivotTable cells are arranged as a summary, and sorting only a piece of that layout can be unavailable or can conflict with the report structure. Change the field’s PivotTable sort instead.
Windows, Mac, and Excel for the web
The instructions above use Microsoft 365 for Windows. Excel for Mac also supports sorting PivotTable labels and values, commonly through the field’s context menu or PivotTable sort controls, although ribbon placement and wording can differ by release. If a menu item is not where it is on Windows, select a PivotTable item and inspect the contextual PivotTable tabs and the right-click menu.
Excel for the web provides PivotTable sorting, but some advanced options and controls may differ from desktop Excel. For a straightforward label or value sort, use the PivotTable item’s available menu commands. If the workbook uses an external connection, a complicated hierarchy, custom sorting, or features the web version does not expose, open it in the Excel desktop app to check the full set of options. On Mac, right-click can be performed with a two-finger click on a trackpad or another configured secondary-click method.
Frequently Asked Questions
Why does my PivotTable sort alphabetically instead of by sales?
Most likely, the sort was applied to a label field. Right-click a number in the sales measure column and choose the largest-to-smallest or smallest-to-largest option. If needed, open More Sort Options and confirm that the sales measure is selected.
Why does 10 appear before 2 in my PivotTable?
The source values may be stored as text, or they may be codes that Excel is correctly treating as text. If they represent quantities, make the source values consistently numeric and refresh the PivotTable. Keep codes as text when leading zeros or character formatting matters.
Why did my PivotTable sort change after I refreshed it?
Refresh updates the summary from the source, which may add items, change totals, or affect a manual arrangement. Check the source and filters, then reapply the desired sort. If a fixed category sequence matters, use an intentional custom order or a sort-order field in the source.
Can I sort a PivotTable by more than one column?
PivotTables sort items within fields, and value-based sorting uses a selected value field. They do not behave exactly like a flat worksheet range with unrestricted multi-column sorting. For a hierarchy, sort each relevant field at its level; for complex ranking, consider adding a helper field or changing the report layout.
Why are some PivotTable sort commands greyed out?
You may have selected a total, blank area, or cell outside the PivotTable, or the worksheet or data connection may restrict sorting. Select an ordinary field item or value and check the context menu. If the source is external or OLAP-based, some sorting capabilities may not be available.
Related
- Excel Material List Template: Plan, Price, and Track
- Excel List Template Free? Where to Find and Use One
- Excel Phone List Template: Create a Contact List
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Pivot Chart Grand Totals: Why Excel Leaves Them OutNo. In Microsoft Excel for Microsoft 365, a PivotChart does not plot a PivotTable’s grand total, even when…
- Standard Deviation Graph in Excel: Create and Read ItExcel does not have a single chart type called a standard deviation graph. To show a distribution, calculate…
- Excel Line Chart: Switch X and Y Axes the Right WayIn Excel, a line chart’s Switch Row/Column command changes which data series are plotted; it does not literally…
- Access Power Pivot in Excel: Ribbon Steps and FixesIn Excel for Microsoft 365 on Windows, open File > Options > Add-ins, choose COM Add-ins beside Manage,…
- Excel Pivot Chart Legend Order: How to Change It EasilyIn Excel for Microsoft 365 on Windows, change a PivotChart’s legend order by changing the order of its…
- Excel Pareto Chart: Show the Cumulative PercentageYes. Excel’s built-in Pareto chart includes a line for cumulative percentage, usually plotted against a percentage scale on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.