Sorting a pivot table in Excel is one of the quickest ways to make your summarized data readable—putting your largest sales figures at the top, your highest scores first, or your dates in the right order. Unlike a normal range, a pivot table has its own sort controls built into the field buttons and the row/column labels. You can sort by the pivot table’s own values (like Sum of Sales) or by the label text (like product names or dates). No formulas are needed, and the sort stays attached to the pivot table even when you refresh it. This guide walks you through every method, from the quick right-click to the more advanced manual drag, and covers the common pitfalls that make sorting seem broken.
What You’ll Need
- A pivot table already created from a data range or table. If you don’t have one, select any cell in your source data and go to Insert > PivotTable.
- The pivot table must have at least one field in the Rows area (for row labels) or Columns area (for column labels) and ideally a value field (like Sum, Count, or Average) in the Values area.
- Excel for Microsoft 365 on Windows is assumed. Mac and Excel for the web have the same sort options, but the right-click menu may look slightly different—look for Sort in the context menu.
Step-by-Step
Method 1: Sort by Values (e.g., Largest to Smallest)
- Click any cell inside the pivot table to activate the PivotTable Tools on the ribbon.
- Right-click on a value cell (for example, a cell showing a sales total) in the column you want to sort by. A context menu appears.
- Hover over Sort and choose Sort Largest to Smallest (or Smallest to Largest if you prefer ascending). This sorts the row labels based on that column’s values.
- If you have multiple value columns (e.g., Sum of Sales and Sum of Profit), the sort applies to the column you right-clicked. To sort by a different column, right-click a cell in that column instead.
- Alternatively, use the ribbon: go to PivotTable Analyze > Sort > Sort Largest to Smallest (or the other options) while a value cell is selected.
Method 2: Sort by Row or Column Labels (Alphabetical or Chronological)
- Click the drop-down arrow on the Row Labels or Column Labels header in the pivot table (the small arrow that appears when you hover over the header).
- In the dropdown, choose Sort A to Z or Sort Z to A for text, or Sort Oldest to Newest / Newest to Oldest for dates.
- Click OK. The labels reorder immediately.
- You can also right-click a row label cell (like a product name) and choose Sort > Sort A to Z.
Method 3: Manual Sorting (Drag and Drop)
- Click on a row label cell (e.g., a product name) so it is selected (the cell gets a border).
- Hover over the border of the selected cell until the cursor changes to a four-sided arrow (or a hand on Mac).
- Drag the cell up or down to the desired position. Release the mouse button. The pivot table reorders that item while keeping other items in their original order.
- This works best when you want a custom order that isn’t alphabetical or numeric. To remove manual ordering later, right-click any row label and choose Sort > More Sort Options, then select Manual (or reset to automatic).
Method 4: Using the Sort Dialog for More Control
- Right-click a row or column label cell, then choose Sort > More Sort Options.
- In the dialog, choose Ascending (A to Z) or Descending (Z to A).
- Under Sort By, select the value field you want to sort by (e.g., Sum of Sales). If you leave it as the default (the first value field), it sorts by that.
- Check Summary at the bottom to see what the sort will do. Click OK.
Common Mistakes
- Sorting the source data instead of the pivot table. If you sort the original data range, the pivot table order may not change—or it may change temporarily but revert when you refresh. Always sort within the pivot table itself.
- Right-clicking a value cell that is not part of the row/column you want to sort. For example, right-clicking a grand total cell often gives no sort option. Right-click an individual value cell inside the table.
- Expecting a custom drag order to stick after a refresh. Manual drag orders are lost when you refresh the pivot table unless you set a manual sort option. To keep a custom order, use the More Sort Options and choose Manual after dragging.
- Sorting by a column that has blank cells. Blanks can cause items to group at the top or bottom unexpectedly. Fill blanks with a placeholder like “0” or “N/A” in the source data, then refresh.
- Forgetting to refresh after changing source data. If you add or remove rows in the source, the pivot table won’t update until you refresh (right-click > Refresh). Sorting may appear off if the underlying data changed.
Tips
- Use the field list to sort by a specific value field. If you have multiple value fields (e.g., Sales and Profit), click the drop-down arrow next to the field name in the Rows area of the PivotTable Field List, then choose More Sort Options and pick the value field.
- Sort dates chronologically by default. Excel usually sorts dates as text if they aren’t recognized as dates. Ensure your source data has real date values (not text) so the sort works correctly.
- Create a custom list for non-standard orders. If you want a specific order like “High, Medium, Low”, go to File > Options > Advanced > Edit Custom Lists and create a list. Then use the sort dialog and choose that list.
- Use the SORT function for dynamic sorting outside the pivot. If you need a sorted copy of the pivot data for further analysis, you can reference the pivot table with
=SORT(pivotrange, sortindex, -1)in a new area (requires Excel 2021 or 365). - Keep an eye on the “Sort New Items” option. In the Sort dialog, there’s a checkbox for “Sort automatically every time the report is updated”. Leave it checked if you want new items to follow the same sort rule.
Troubleshooting
- Sort option is greyed out. This usually happens when you select a cell outside the pivot table or a grand total cell. Click a cell inside the pivot table’s data area (not the totals) and try again.
- Sort doesn’t seem to do anything. Check if you have multiple row fields (e.g., Product and Region). The sort applies to the innermost field by default. To sort by the outer field, right-click a cell in that field’s column and sort.
- Items are in a weird order after refresh. Manual drag orders are lost on refresh. Re-apply your sort, or use More Sort Options and set a manual sort order that persists.
- Dates are sorting alphabetically (e.g., April before January). This means your dates are stored as text. Convert them to real dates in the source data (select column > Data > Text to Columns > Date) and refresh the pivot.
- Sort by value only sorts one column. If you have multiple value columns, you must right-click inside the specific column you want to sort by. The sort is column-specific.
- Pivot table is in compact layout and sorting seems limited. Switch to Outline or Tabular layout (PivotTable Analyze > Report Layout) to see all fields as columns, which makes sorting more intuitive.
Frequently Asked Questions
Q: Can I sort a pivot table by a value field that is not in the Values area?
No, you can only sort by fields that are actually in the pivot table (either as row/column labels or as values). If you need to sort by a field not in the table, add it to the Values area temporarily.
Q: How do I sort by a specific column when I have multiple value fields?
Right-click on a cell in the column you want to sort by (e.g., the “Sum of Profit” column) and choose Sort. The sort will reorder the row labels based on that column’s values.
Q: Why does my pivot table keep reverting to alphabetical order after I drag items?
Because manual drag orders are not remembered when you refresh. To keep a custom order, right-click a row label > Sort > More Sort Options, and select “Manual” as the sort order. Then refresh and your order stays.
Q: Can I sort a pivot table by cell color or conditional formatting?
No, pivot tables do not support sorting by color. You can sort by values or labels only. If you need color sorting, copy the pivot table as values and use the normal range sort.
Q: Does sorting in a pivot table affect my original data?
No, sorting a pivot table only changes the display order in the pivot. Your source data remains untouched.
Q: How do I sort a pivot table in Google Sheets?
In Google Sheets, click the drop-down arrow on the row label header and choose Sort A→Z or Z→A. To sort by value, right-click a value cell and choose “Sort sheet by column”—but note this sorts the underlying data, not just the pivot. For pivot-specific sorting, use the field list’s sort options.
Related
- Excel Template for Timesheet Job Tracking
- Excel Template for Snowball Debt Payoff
- Excel Template for Saving Goal Tracking
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Add a Pie Chart in Excel: Labels, Slices, and TipsTo add a pie chart in Excel for Microsoft 365 on Windows, arrange category names in one column…
- Excel Chart Area Gradient Fill: How to Apply ItTo apply a gradient fill to a chart area in Excel for Microsoft 365 on Windows, select the…
- Excel Power Pivot Table Tutorial for BeginnersIn Excel for Microsoft 365 on Windows, enable Power Pivot, add your data tables to the workbook Data…
- Excel Pivot Table: How to Group Dates by MonthIn Excel for Microsoft 365 on Windows, click a date in the PivotTable, right-click, choose Group, select Months,…
- BI with Excel Power Pivot: Models, Reports, and LimitsYes—Excel Power Pivot can support business intelligence (BI) by combining data from multiple sources into a relational data…
- Excel Combo Chart: How to Combine Bars and a Line in ExcelTo make a bar-and-line combo chart in Excel for Microsoft 365 on Windows, select your data, choose Insert…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.