Sorting a pivot table in Excel
You sort a pivot table by right-clicking a value in the row or column you want to reorder, then choosing Sort from the menu. Excel offers three basic options: sort A to Z (or smallest to largest), sort Z to A (or largest to smallest), or open the full sort dialog to arrange by a different field entirely. The sort applies only to that one area of the pivot table — you can sort rows independently from columns, and sorting one pivot table does not affect others on the same sheet.
Where you click matters. If you click a cell in the row labels area, you sort the rows. If you click a cell in the data area (the numbers in the middle), you sort by those values. If you click a column header, you sort that column. The menu that appears changes based on what you clicked, so the steps look slightly different depending on which direction you want to reorder.
Key Takeaways
- Right-click any cell in the area you want to sort, then select Sort from the context menu to open sorting options.
- Sorting by row labels (A to Z or Z to A) reorders the rows without changing the data values themselves.
- Sorting by data values (smallest to largest or largest to smallest) reorders rows or columns based on the numbers in the pivot table body.
- Use the full sort dialog to sort by a field that is not currently visible or to apply multiple sort levels at once.
- Sorting one pivot table does not affect other pivot tables or the underlying data in your worksheet.
Sort rows by their labels
To arrange rows alphabetically or numerically by their labels, right-click any cell in the row labels column (the leftmost area of the pivot table). You will see options like Sort A to Z or Sort Z to A. Click the one you want, and the entire row section reorders instantly.
This is the most common sort. If your pivot table shows product names in rows and sales by month in columns, sorting A to Z arranges the products alphabetically while keeping all the sales numbers aligned with each product. The data does not change — only the order of the rows.
Sort columns by their labels
Column headers work the same way. Right-click a cell in the column labels area (usually near the top of the pivot table), and you will see sort options. Sort A to Z moves columns left to right alphabetically, and Sort Z to A reverses that order.
This is useful when your pivot table shows months or regions across the top and you want to reorder them. For example, if months are out of sequence, you can sort them chronologically by clicking one month name and choosing the appropriate sort direction.
Sort rows or columns by data values
To reorder based on the numbers in the pivot table body rather than the labels, right-click a cell in the data area itself — one of the actual values, not a label. The menu offers Sort Smallest to Largest and Sort Largest to Smallest. Choose one, and the rows (or columns) reorder so the smallest or largest values appear first.
This is how you answer questions like "which products sold the most?" or "which regions had the lowest revenue?" Right-click any number in the column you want to sort by, pick your direction, and the entire pivot table reorganizes around that value. If you have multiple data columns, make sure you click in the one you actually want to sort by — clicking in the wrong column sorts by the wrong numbers.
Use the sort dialog for more control
For sorting options beyond the basic four, right-click any cell in the pivot table and select Sort, then choose More Sort Options (or Sort again if that option appears). This opens the full sort dialog, where you can sort by a field that is not currently visible in the pivot table, or apply multiple sort levels at once.
The dialog shows a list of all available fields. Select the one you want to sort by, choose ascending or descending order, and click OK. This is also where you go if you want to sort by one field first, then by a second field to break ties. For example, you could sort regions by total sales (largest first), then sort products within each region alphabetically.
What happens when you refresh the pivot table
Your sort order stays in place when you refresh the pivot table with new data. If the underlying data changes and you press Refresh (or right-click the pivot table and select Refresh), the pivot table updates but keeps the sort you applied. New rows or columns added to the source data will appear in their default position, not sorted, until you sort again.
If you want the pivot table to sort automatically every time it refreshes, you need to set that up in the pivot table options. Right-click the pivot table, select Pivot Table Options, go to the Data tab, and look for sort settings there — but most users find it simpler to just sort manually after each refresh.
Sorting does not change the underlying data
A pivot table is a summary view of your data, not the data itself. Sorting a pivot table only changes how the summary appears on screen. The original data in your worksheet stays exactly as it was. If you delete the pivot table and create a new one from the same data, the new pivot table will not remember your sort — you will have to sort it again.
This also means you can have multiple pivot tables from the same data source, each sorted differently, all on the same sheet or different sheets. Each pivot table maintains its own sort order independently.
Frequently Asked Questions
Can I sort a pivot table by a field that is not showing?
Yes. Right-click any cell in the pivot table, select Sort, then More Sort Options. The dialog lists all available fields, including ones not currently displayed. Select the field you want to sort by, choose your direction, and click OK.
What if I sort by the wrong column by accident?
Right-click any cell in the pivot table, select Sort, then More Sort Options. Change the sort field to the correct one and click OK. You can also undo the sort by pressing Ctrl+Z immediately after sorting.
Does sorting a pivot table affect other sheets or other pivot tables?
No. Each pivot table maintains its own sort order. Sorting one pivot table does not change any other pivot table or the underlying data in your worksheet. You can have multiple pivot tables sorted in completely different ways on the same sheet.
Will my sort stay if I add new data to the source?
Yes, your sort order stays when you refresh the pivot table. New rows or columns from the source data will appear unsorted until you apply a sort to them. If you want automatic sorting on refresh, check the pivot table options under Data settings.