TL;DR
- Right-click a number in the value column, choose Sort, then pick largest to smallest or smallest to largest.
- To rank by Grand Total, select any number in the Grand Total column and sort it.
- To sort by a specific value field, open the field menu, choose More Sort Options, then Descending (or Ascending) by that field.
- With nested fields, a value sort only reorders items inside their parent group, not across the whole table.
- A custom-list order is not kept after a refresh, so check your sort settings if the order changes.
Table of Contents
The quickest method: right-click a value
This is the method most people need, and it takes about three seconds.
Say you have a PivotTable with sales regions down the left and a Sum of Revenue column. The regions appear alphabetically, which tells you nothing about performance. You want the best region on top.
- Click any number in the Sum of Revenue column. Pick a value cell, not a header.
- Right-click it.
- Choose Sort.
- Select the order you want: smallest to largest, or largest to smallest.
Microsoft Support describes the same route:
right-click a value cell, choose Sort, and select the sort method you need. The order applies to the items at the same level in that column. That detail matters, and we return to it in the nested fields section below.
The same approach works on a Mac. Microsoft's guidance for Excel for Mac confirms that PivotTables can sort numbers smallest to largest or largest to smallest, and that you can right-click an individual value or subtotal to do it.
What actually gets sorted
When you sort on a value cell, you are not sorting the numbers on their own. Excel reorders the row labels (or column labels) so that the numbers line up in the order you asked for. The regions travel with their revenue. That is why your data stays intact and nothing gets scrambled.
It also explains why value sorting and label sorting are two separate operations. Microsoft's Excel object model keeps them apart: xlSortValues sorts a PivotTable report by values, while xlSortLabels sorts by labels. Sorting A to Z on the region names will never rank regions by revenue. Sorting on the revenue number will.
Sort largest to smallest by Grand Total
The most common ranking question is simple: which items are biggest overall? If your PivotTable has several value columns, such as one per month or per product line, the Grand Total column holds the overall figure for each row.
Microsoft's instructions are short: select any number in the Grand Total column and sort it. The rows then rank by total, whatever the individual months look like.
A worked example makes this concrete. Imagine a table of four regions with quarterly revenue in four columns and a Grand Total at the right:
| Region | Q1 | Q2 | Q3 | Q4 | Grand Total |
| East | 120 | 90 | 150 | 110 | 470 |
| North | 80 | 130 | 95 | 160 | 465 |
| South | 200 | 60 | 70 | 90 | 420 |
| West | 100 | 140 | 130 | 120 | 490 |
These figures are made up for illustration. Sorting on any Q1 cell would put South first, because it had the strongest first quarter. Sorting on a Grand Total cell, largest to smallest, gives West, East, North, South. Same table, two different rankings, depending on which column you clicked.
So the column you click is the instruction you give Excel. Before you right-click, ask yourself which number should decide the order.
Sort by a specific value field with More Sort Options
Sometimes the number you want to rank by is not the one you are looking at. You might have three value fields in the table (Revenue, Units, Profit) and want rows ranked by Profit while all three stay visible. Or you may be working from the field's dropdown rather than a cell.
Here is the route Microsoft documents:
- Click the dropdown arrow on the row or column field (the label header, for example Region).
- Choose More Sort Options.
- Under Sort options, select Descending (or Ascending) by.
- In the box, choose the value field that should control the order.
- Click OK.
This is how
More Sort Options lets you choose Ascending or Descending by a selected value field, including a particular Values field. It is also the more reliable way to build a rule, because the sort belongs to the field rather than to one cell you happened to click.
Which one should you use?
| Situation | Best method |
| One value column, quick ranking | Right-click a value, then Sort |
| Rank by overall total across columns | Sort a number in the Grand Total column |
| Several value fields, rank by one of them | More Sort Options, Descending by that field |
| Want a fixed, hand-built order | More Sort Options, Manual |
If you are ever unsure, More Sort Options is the safer default. It shows you exactly what the sort is keyed to.
Sorting with several fields: why the order stays inside groups
This is where most confusion starts. You build a PivotTable with School in the first row field and Department below it, with Donations as the value. You right-click a donation figure and sort largest to smallest. The result looks wrong: the biggest donation in the whole sheet is not at the top.
Nothing is broken. Indiana University's Excel training documentation explains that a value sort stays within the higher-level field, so a donation sort is performed within each school. Departments get ranked inside their own school. The schools themselves keep their existing order, because you sorted the lower level.
That matches Microsoft's wording that a sort applies to items at the same level in that column.
Here is how to get the order you want:
- Rank the outer field. Click a School subtotal (or open the School field's dropdown and use More Sort Options with the donation field). Schools now rank by total donations.
- Rank the inner field. Then sort a Department-level value. Departments rank within each school.
- Want one flat list? Remove the outer field from the Rows area, or move it to Filters. With a single row field, every item sits at the same level and the whole list ranks together.
A useful habit: sort the outermost field first, then work inward. Each sort leaves the one above it alone.
If you want a deeper walkthrough of how row fields, value fields and groupings interact, our lesson on working with PivotTables covers it, and PivotTable fundamentals is the place to start if the field list itself still feels unfamiliar.
Create a custom sort order
Value sorting answers "which is biggest?" Sometimes the question is different. You want Low, Medium, High instead of the alphabetical High, Low, Medium. Or you want your own region order because that is how your leadership team reads the report.
There are two routes, both through More Sort Options:
Manual sort. Choose Manual and drag items into the order you want. You can also drag directly in the PivotTable.
A built-in or custom list. Under More Options, turn off automatic sorting when the report updates. Then choose a built-in or custom list from the First key sort order box. Microsoft documents both: select Manual to drag items, or under More Options with automatic sorting off, choose a built-in or custom First key sort order.
One caveat belongs right where you act. Microsoft notes that a custom-list sort order is not retained after refresh. If you refresh the PivotTable and your hand-built order disappears, that is expected behavior. For a report you will refresh every week, check your sort settings after each update, or rank by a value field instead, since a value-based sort re-evaluates as the numbers change.
When sorting by value doesn't work
A sort that does nothing, or that gives a strange order, almost always traces back to one of a handful of causes. Work through them in this order.
1. You sorted at the wrong level
If the order looks right inside each group but wrong overall, see the nested fields section above. The sort was applied to the lower level, and the outer field kept its order.
2. Leading spaces in your data
Microsoft's troubleshooting advice is to remove leading spaces and check your locale settings. Items with a space in front of them sort ahead of everything else, which looks like random behavior. Clean the source data (the TRIM function does this), then refresh the PivotTable.
3. Numbers stored as text
If numbers are left-aligned in your source data and come up as text items in the PivotTable, they will not rank numerically. Convert them to real numbers in the source range and refresh. The locale point from Microsoft is related: decimal and thousands separators differ by region, and a mismatch can leave values as text.
4. Automatic sorting is changing your order
Use More Options inside More Sort Options to control whether Excel re-sorts automatically when the report updates. If your order shifts after a refresh, this setting is the first place to look. The same Microsoft page notes that custom-list orders do not survive a refresh, so a manual or list-based order may need to be reapplied.
5. You sorted the wrong column
It sounds obvious, but in a table with many value columns it happens constantly. Look at which column your cursor was in when you right-clicked. If the result is ranked by Units when you wanted Revenue, sort again on a Revenue cell, or set the rule explicitly in More Sort Options.
A quick checklist
- Is the sort applied to the level you care about?
- Is the source data free of leading spaces and text-formatted numbers?
- Did you refresh after cleaning the data?
- Is the sort keyed to the right value field in More Sort Options?
- Is automatic sorting on or off, and is that what you want?
Practice until it is automatic
Reading about sorting gets you partway. The reflex of knowing which cell to click, and which level you are sorting, comes from doing it repeatedly on different layouts.
That is what we built LogicExcel for. You type formulas and work through tasks in an Excel-like environment in your browser, get instant feedback, and see an explanation of every mistake. There is no account to create and nothing to install. Start with our free Excel practice exercises, or see the Excel courses if you prefer a structured path.
If you want a fast overview first, PivotTables in thirty seconds shows what a PivotTable does before you start sorting one.
Perspective
> Most people learn pivot tables by watching someone else click. It feels like progress, and then the file is on your own screen, the fields are different, and the sort puts the wrong region on top. The problem is not knowledge. It is that your hands have never made the decision.
>
> Sorting is a good example. It looks like a single menu item, but it is really a question: which number decides the order, and at which level? Every confusing result we see comes from that question going unasked. The right-click was fine; the target was wrong.
>
> That is why we built LogicExcel around active practice. You enter the answer, the environment checks it, and we explain the mistake, so the next attempt is better. Repeating that on varied layouts is what turns a pivot table from a feature you half remember into a tool you reach for without thinking. And we think it should cost nothing and need no sign-up, because the barrier to practicing should be zero.
>
> Thomas Houza
FAQ
How do I sort a pivot table from largest to smallest?
Right-click any number in the value column, choose Sort, and select largest to smallest. To rank by overall total, do this on a number in the Grand Total column. Microsoft Support documents both routes.
Can I sort by a value field that isn't the one I clicked?
Yes. Open the dropdown on the row or column field, choose More Sort Options, select Descending (or Ascending) by, and pick the value field from the list. Excel then ranks the items by that field, wherever it sits in the table.
Why does my pivot table sort only within each group?
A value sort applies to items at the same level, so with nested fields it reorders items inside each parent. Indiana University's training notes give the example of donations sorted within each school. To rank the outer groups, sort the outer field, or use a single row field for one flat ranking.
Why did my custom sort order disappear after I refreshed?
Custom-list sort orders are not retained after a refresh, according to Microsoft. Check the More Options setting for automatic sorting and reapply the order if needed. For reports you refresh often, ranking by a value field is more durable.
Does sorting work the same way on a Mac?
The core steps are the same. Microsoft's Mac guidance says you can right-click an individual value or subtotal to sort numbers from smallest to largest or largest to smallest.
Sources
