If an Excel PivotTable is not sorting correctly, first choose the field and sort direction you actually want: right-click a row label or value, select Sort, then choose the appropriate direction or More Sort Options. If the order still looks wrong, check whether the source values are genuine numbers or dates, refresh the PivotTable, and confirm you are sorting by the intended value field rather than by labels.
Start with the field and sort direction
A PivotTable can sort its row labels, column labels, or displayed values, and those are different operations. For example, sorting a list of product names alphabetically is not the same as ordering those products by total sales. Clicking a cell in the wrong part of the report can lead Excel to offer a sort that is valid but does not answer your question.
For a quick label sort, right-click an item in the row or column field and choose Sort > Sort A to Z or Sort > Sort Z to A. For a value-based sort, right-click a number in the relevant value column and choose Sort followed by the desired largest-to-smallest or smallest-to-largest option. If you need to specify the field used for the ordering, open Sort > More Sort Options and select the appropriate ascending or descending option and value field.
Check the result against a small example. If the report shows each salesperson and their total sales, a label sort orders names alphabetically; a value sort orders the displayed totals. The latter may be based on Sum of Sales, Count of Sales, or another summary, so verify the column heading before applying it.
How sorting in a PivotTable works
A PivotTable is a summary of source records, not a regular range of cells. Excel groups records into categories and calculates summaries, then applies sorting to the resulting field items or values. That is why sorting the source worksheet does not necessarily change the order shown in the PivotTable, and why sorting the PivotTable does not rearrange the original records.
Each field has its own context. A report with Region above Salesperson in the Rows area has two levels: sorting regions affects the outer groups, while sorting salespeople generally affects the names within each region. If you sort a salesperson by sales, Excel may order names within their region rather than create one organization-wide list. That is expected for a nested report.
Also distinguish displayed totals from raw records. If a PivotTable summarizes transactions by Sum, a value sort uses each category’s total. If the field is summarized by Count, it uses the number of records instead. A report can therefore appear to sort incorrectly when Excel is correctly sorting a different measure than the one the reader had in mind.
Use More Sort Options for a controlled sort
When the basic sort command does not produce the required order, set the sort explicitly. Click an item in the field you want to arrange, right-click, and choose Sort > More Sort Options. Choose ascending or descending, then inspect the field named in the sort-by selection. For a value-based order, select the summary field that contains the figures you want to compare.
- Click a label or a value in the PivotTable, depending on whether you want to sort categories or results.
- Open the context menu and choose Sort > More Sort Options.
- Choose the direction and, where available, the value field to sort by.
- Apply the sort, then check several entries, including the first, last, and any tied values.
If you have several value columns, take care to choose the correct one. A report containing Sum of Revenue and Sum of Units could have very different rankings depending on which field drives the sort. To compare average order size, for example, make sure the PivotTable includes an average measure and sort by that measure; a revenue total cannot stand in for an average.
Check source data types and inconsistent entries
One of the most common causes of a surprising order is mixed data types. Excel sorts numbers numerically, but values stored as text can behave like labels. A column containing numeric values alongside text such as “Unknown,” imported numbers with leading apostrophes, or entries with inconsistent spaces can produce categories that do not sort as expected.
For instance, text labels “1,” “10,” and “2” may appear in an order that looks alphabetical rather than numeric. A genuine numeric field sorts as 1, 2, 10. Likewise, dates stored as text may sort by their character patterns rather than in calendar order. Look at the source column, not only the PivotTable, and check for inconsistent formats, stray spaces, blanks, or error values.
Correct the source data consistently, then refresh the PivotTable. Do not convert values blindly if text is meaningful: an account code such as 00127 may need to remain text so its leading zeros are preserved. If numeric ordering is required, decide how codes should be interpreted before changing their type.
Refresh the PivotTable and verify its source
A PivotTable can keep showing an earlier view of its source data until it is refreshed. On Windows, click inside the PivotTable and select PivotTable Analyze > Refresh, or right-click inside it and choose Refresh. To update multiple PivotTables and connections, use Data > Refresh All. Refreshing updates the summary; it does not repair incorrectly typed or missing source values.
Check that the source range includes every relevant row. Click inside the report and choose PivotTable Analyze > Change Data Source to inspect the selected range. If new records were added below a fixed range, they may not be included, so refreshing alone will not bring them into the report. Converting the source range to an Excel Table with Insert > Table is often a practical way to let the source expand as rows are added; confirm the table is selected as the PivotTable source.
When a refresh changes the order, distinguish between a refreshed result and an automatic sorting preference. Reapply the intended sort after confirming the new records are present. If your workbook has several PivotTables built from different sources or caches, refresh and inspect the specific report you are troubleshooting rather than assuming every report has identical source data.
Handle dates, months, and grouped fields
Date sorting depends on the underlying values and the way the field is arranged. A genuine date field can be grouped into years, quarters, or months; a text column containing month names is just text. Alphabetical sorting of month names can put April before February, while a true date or a properly grouped date field can follow calendar order.
If months appear in alphabetical order, inspect the source field and the PivotTable field list. If the source contains actual dates, you can group date items by right-clicking a date in the PivotTable and choosing Group, then selecting the time periods that suit the report. If the source contains only month names, Excel does not know which month-year each label represents. Use a real date column where possible, or provide a separate month-order field in the source data.
Grouping by month alone also combines January from different years. If the report needs a chronological timeline across several years, include Year as a grouping level or keep the complete date available. This avoids mistaking a deliberate grouping choice for a sort failure.
Review manual order, custom lists, and nested fields
PivotTable items can retain a manually arranged order, and Excel can also use custom lists for familiar sequences such as weekdays or months. Those behaviors are useful when the desired order is not alphabetical, but they can be confusing if you expect every field to sort like ordinary text. A field may also be nested under another row field, which limits the scope of its sort.
For a standard alphabetical or numerical order, apply the sort command to the intended field rather than dragging items into place. If a custom order is desired, make sure the list is consistent and understand that Excel may prioritize it over a plain alphabetical sequence. In Excel for Windows, custom lists can be reviewed under File > Options > Advanced, in the General section, using Edit Custom Lists. Avoid changing workbook-wide settings just to fix one report unless you understand the effect on other sorting tasks.
When a category appears in a seemingly irregular position, check whether it is a subtotal, a blank item, or a parent item with children. Subtotals and grand totals are report elements, not ordinary category labels, so they may not follow the same order as the detail items. Temporarily reviewing the field hierarchy or turning off subtotals can help clarify what Excel is sorting.
What to try when sorting commands are unavailable
If sort options are disabled or the sort does not apply, check the report’s structure before rebuilding it. A protected worksheet can restrict changes. A cell outside the PivotTable will not provide PivotTable sorting commands. In a report with multiple fields, you may be clicking a subtotal or grand total rather than an item in the field you want to arrange.
- Click a normal row or column item, not a blank area or grand total.
- Confirm the worksheet is not protected in a way that blocks PivotTable changes.
- Check whether the desired field is actually in the Rows or Columns area.
- Refresh after correcting the source, then repeat the sort on the relevant field.
- If the report is filtered, clear or review the filter; hidden items can make the visible order seem incomplete.
On Mac, the same basic approach applies: click or Control-click an item to open its context menu, then use the available sort options. Ribbon labels and placement can vary slightly by Excel release. In Excel for the web, use the PivotTable field’s available sort controls; if a command needed for grouping or advanced report configuration is not available there, open the workbook in the desktop Excel app.
Frequently Asked Questions
Why is my PivotTable sorting alphabetically instead of by value?
Excel may be sorting the row labels rather than the displayed measure. Right-click a value in the measure column, choose Sort, and select the appropriate value direction. In More Sort Options, confirm the sort-by field is the measure you intend to use.
Why does my PivotTable put 10 before 2?
The source values may be stored as text, so Excel is ordering them as labels rather than numbers. Check the source column for text-formatted numbers and inconsistent entries, convert values only when appropriate, and refresh the PivotTable.
Why are my months in alphabetical order?
The month names may be text rather than dates. Text labels sort alphabetically unless you use a recognized custom order. A real date field grouped by month, or a separate month-order field, provides a reliable calendar sequence.
Do I need to sort the source data first?
No. Sorting the source range is not a dependable way to set the displayed order in a PivotTable. Sort the PivotTable field itself. Refresh the report when source records change, and verify that its source range includes those records.
Why did my PivotTable sort change after I refreshed it?
A refresh recalculates the report using current source records, and new items or changed values can affect the displayed result. Check the active sort field and direction, then apply the desired sort again. Also verify that the source values have consistent types.
A reliable way to diagnose the problem
Work from the report outward: identify whether the target is a label or a summarized value, select the correct field, and specify the sort direction. If the order remains unexpected, inspect the source for text-number or text-date problems, confirm the PivotTable’s source range, refresh it, and check grouping, filters, and nested fields. This sequence usually reveals whether the issue is a wrong sort setting, stale data, or an underlying data-structure problem—and helps you fix the cause without rearranging the source records unnecessarily.
Related
- Power Query Basics: Import, Clean, and Transform Data
- Append Queries in Power Query: How to Combine Tables
- Power Query Editor: What It Is, How It Works, and Why
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel: How to Turn On Power Pivot and Build Data ModelsTo turn on Power Pivot in Excel for Microsoft 365 on Windows, select File > Options > Add-ins.…
- Make an Excel Dashboard with AI: A Step-by-Step GuideYes. You can make an Excel dashboard with AI by using an assistant to plan the layout, suggest…
- Balance Chart in Excel: Track an Account Over TimeTo create a balance chart in Excel, list transaction dates and signed amounts, calculate a running balance beside…
- Add a Line to a Waterfall Chart in ExcelExcel for Microsoft 365 does not provide a supported Combo Chart option for adding a data-driven line series…
- Pivot Text to Columns in Excel: Split or Reshape?If you mean splitting text from one Excel column into separate columns, select the cells and choose Data…
- Does Excel Online Have Sparklines? Setup and LimitsYes. Excel for the web—often called Excel Online—can create and format sparklines, the small charts that fit inside…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.