Sorting a pivot table means rearranging its rows or columns by value, alphabetically, or by the order you choose
A pivot table in Excel starts with a default sort order — usually alphabetical for text fields and smallest-to-largest for numbers. You can change this order without rebuilding the table. The method depends on whether you want to sort by the row labels themselves, by the data values in the table, or by a custom list you define.
The fastest way is to click any cell in the row or column you want to sort, then use the sort buttons in the Data tab on the ribbon. For more control — like sorting by multiple fields at once or by a specific data column — you use the pivot table's built-in sort dialog instead of the standard Excel sort.
Key Takeaways
- Click any cell in the field you want to sort, then use Data > Sort A to Z or Z to A to change the order when ready.
- To sort by the values in a data column rather than by the field labels, right-click the pivot table and choose Sort, then pick the column you want to sort by.
- Custom sorts — like sorting months in calendar order instead of alphabetically — require you to define a custom list in Excel's options first.
- Sorting a pivot table does not change the source data; it only changes how the table displays the information you already summarized.
The quick sort: alphabetical or numerical order
If you want to sort a field alphabetically (A to Z) or reverse alphabetically (Z to A), or sort numbers from smallest to largest, click any cell in that field. Then go to the Data tab on the ribbon and click either Sort A to Z or Sort Z to A.
For example, if your pivot table lists product names in the row area and you want them in reverse alphabetical order, click any product name in the table. Then click Data > Sort Z to A. The entire pivot table rearranges so the products appear in reverse order, and all the data rows move with them.
This method works for any field in the pivot table — row labels, column labels, or the values themselves. Excel recognizes whether the field contains text or numbers and sorts accordingly.
Sorting by data values instead of field labels
Often you want to sort not by the row names but by the numbers in the table. For instance, you might have regions listed alphabetically but want to sort them by sales revenue from highest to lowest. Clicking a cell and using the quick sort buttons will not work — they sort by the region names, not by the revenue.
Instead, right-click anywhere in the pivot table and select Sort. A dialog opens with options specific to pivot tables. Choose the field you want to sort (the row or column labels), then under "Sort by" select the data column you want to use for ordering. Pick "Descending" for largest to smallest or "Ascending" for smallest to largest.
This keeps your row labels intact but reorders them based on the values you care about. If you have multiple data columns, you can sort by any of them — sales, profit, quantity, or whatever you included in the pivot table.
Sorting by multiple fields at once
If your pivot table has nested row fields — for example, Region and then Product within each region — you can sort by more than one field in a single operation. Right-click the pivot table and select Sort again.
In the sort dialog, you see options to add multiple sort levels. Set the first level to sort by Region (or whichever field you want as the primary sort), then add a second level to sort by Product within each region. You can specify ascending or descending for each level independently.
This is faster than sorting one field, then sorting the other, because it preserves the relationship between the levels. All products stay grouped under their region, and both fields are ordered the way you specified.
Custom sorts: months, quarters, and non-alphabetical order
Excel has built-in custom lists for months (January, February, March) and days of the week (Monday, Tuesday, Wednesday). If your pivot table includes a date field broken down by month, you can sort by these lists so months appear in calendar order instead of alphabetically.
Click any cell in the month field, go to Data > Sort, and in the dialog you should see an option to sort by a custom list. Select the month list, and the pivot table sorts January through December regardless of alphabetical order.
If you need a custom sort order that Excel does not have built in — like sorting regions by sales territory or products by category — you must create a custom list first. Go to File > Options > Advanced, scroll to General, and click Edit Custom Lists. Add your list in the order you want, then save. After that, you can use it to sort any pivot table.
Undoing a sort and returning to the original order
If you sort a pivot table and then change your mind, you can undo the sort by pressing Ctrl+Z when ready after sorting. This restores the previous sort order.
If you have already done other work since sorting, undo may not be available. In that case, you can re-sort the table by the field you want. For instance, if the table was originally sorted alphabetically by product name, click a product cell and use Data > Sort A to Z again. The table returns to that order.
Sorting never deletes or changes the underlying data — it only changes the display order of the pivot table. You can sort and re-sort as many times as you want without affecting the source information.
What happens when you refresh the pivot table
If you refresh a pivot table — by right-clicking it and selecting Refresh, or by pressing Ctrl+Shift+F5 — the sort order you applied stays in place. The table pulls in any new data from the source, but it maintains the sort you set.
The exception is if the source data has changed so much that a field no longer exists. For example, if you sorted by a product name that is no longer in the source data, that sort rule is removed when you refresh.
Frequently Asked Questions
Can I sort a pivot table by a field that is not visible in the table?
No. You can only sort by fields that appear in the pivot table — either as row labels, column labels, or data values. If you need to sort by a field from the source data, you must add that field to the pivot table first, even if you hide it afterward.
Why does my sort not work when I click the quick sort buttons?
The quick sort buttons sort by the field labels in the cell you clicked. If you want to sort by the data values instead, use the right-click Sort dialog and specify which data column to sort by. The quick buttons cannot do that.
Does sorting a pivot table change the original data in Excel?
No. A pivot table is a summary view of your data. Sorting the pivot table only changes how the summary appears. Your original data remains untouched and in its original order.
Can I sort one row field while keeping another row field in its original order?
Not independently. If you have nested row fields, sorting one affects the display of both. You can use the multi-level sort dialog to control the sort order of each level, but you cannot sort one without affecting the other.