I once watched someone manually retype an entire pivot table into a new range just to get it sorted in the order they wanted, because they assumed pivot tables could only sort alphabetically and did not know value-based sorting existed.
This happens more often than you would expect. Basic alphabetical sorting is what people discover by accident, but the sorting options that answer genuinely useful business questions — show me the top performers, sort this by total value rather than name, keep one specific category always at the top regardless of its value — require knowing where to look, because they are not the default behavior anyone stumbles into accidentally.
The Default Behavior and Why It Confuses People
When you first build a pivot table by dragging a field like Salesperson into Rows, Excel sorts that field alphabetically by default. This is rarely what anyone actually wants to see first — a business audience almost always cares about who has the highest sales, not whose name comes first alphabetically.
The good news is that changing this to value-based sorting takes seconds once you know the specific click sequence, which is the first method covered below.
Method 1: Sort by Value Using the Field Dropdown
Click the dropdown arrow next to your row field header inside the pivot table itself (for example, the dropdown on “Row Labels,” or on the specific field name if you have renamed it).
Select Sort, then choose either Sort A to Z, Sort Z to A for alphabetical sorting, or look for an option like “More Sort Options” for value-based sorting.
In the More Sort Options dialog, choose “Descending” and select your Values field (like Total Sales) from the dropdown beneath it, rather than sorting by the row label names themselves. Click OK.
Your pivot table now reorders to show the highest value first, descending down to the lowest — answering the much more commonly needed business question of “who or what performed best” rather than alphabetical order.
Method 2: The Faster Click-and-Sort Shortcut
Once your pivot table has a Values field already in place, you can skip the dialog entirely for a quicker approach: right-click directly on any specific value inside the Values column of your pivot table (an actual number, not the field name), hover over Sort in the context menu, and choose “Largest to Smallest” or “Smallest to Largest.”
This sorts the entire pivot table based on that value column immediately, without needing to navigate through the More Sort Options dialog. This is the method I use most often during live analysis, since it requires fewer clicks once you know it exists.
Method 3: Sorting When You Have Multiple Value Columns
If your pivot table has two or more fields in the Values area — say, both Total Sales and Total Units — right-clicking a value gives you the choice of which specific column to sort by, since Excel needs to know whether you want to sort by the Sales numbers or the Units numbers when both exist side by side.
Right-click a value specifically within the column you want to sort by (not the other value column), and the resulting Sort options will apply based on that specific column’s numbers, leaving the other value column simply following along in whatever order the sort produces for the primary column you chose.
Method 4: Keeping a Specific Item Always at the Top Regardless of Value
Sometimes you want most items sorted by value, but one specific category — perhaps a “Returns” or “Adjustments” row, or a specific reference category your business always wants visible first regardless of its numeric size — needs to stay pinned at a fixed position rather than moving based on its value.
Pivot tables do not have a simple built-in “pin to top” feature for this, but a practical workaround exists: manually drag that specific row label up to the top of the pivot table directly (click and drag the row label cell itself). Excel will respect this manual positioning and exclude that specific item from subsequent automatic sorts, treating it as manually placed, while still auto-sorting the remaining items around it.
This manual drag approach is somewhat fragile — if you later apply a fresh full sort through the dropdown menu, it may override your manual positioning, so this technique works best for a final, mostly-finished pivot table rather than one still being actively rebuilt and resorted during analysis.
Method 5: Sorting With Multiple Row Fields (Nested Sorting)
When you have two fields in Rows simultaneously — for example, Region nested with Salesperson within each region — sorting behaves at each level independently rather than as one flat combined sort.
Sorting the outer field (Region) by value reorders which region appears first, second, and so on, based on each region’s total. Sorting the inner field (Salesperson) by value, applied while your cursor is on a salesperson row rather than a region row, reorders the salespeople within each region by their individual values, but this reordering is typically applied independently within each region group rather than as one combined ranking across all regions and salespeople together.
This nested behavior is usually exactly what you want for hierarchical reports — best region first, and within that region, best salesperson first — but it surprises people who expect a single flat sort across every individual combination regardless of grouping.
Method 6: Custom Manual Order (Not Alphabetical, Not by Value)
For situations where neither alphabetical nor value-based sorting matches what you need — for example, sorting months in calendar order (January through December) rather than alphabetical order, where “April” would otherwise incorrectly sort before “January”
If your underlying date field is genuinely formatted as a date and grouped using the date grouping feature covered in an earlier tutorial, Excel automatically displays months in correct calendar order rather than alphabetical order, since it understands the underlying chronological value rather than just the displayed month name as plain text.
For other custom orderings that are not date-based — perhaps a specific priority ranking of product categories that does not match either alphabetical or numeric value order — Excel supports custom lists (File, then Options, then Advanced, then Edit Custom Lists) which, once defined, allow a field to sort according to your custom-defined sequence rather than the standard alphabetical or value-based options. This is a more advanced, less commonly needed setup, but worth knowing exists for genuinely custom ordering requirements.
Showing Only the Top Items After Sorting
Sorting and filtering work well together for a very common business request: show me only the top ten performers, sorted from highest to lowest.
After sorting by value as described above, use the same Row Label dropdown, navigate to Value Filters, and select “Top 10.” You can adjust the number from the default ten to any number you need, and choose whether you want the top items by sum, average, or another calculation type depending on what is in your Values area.
Combining sorting with this Top filter produces exactly the “leaderboard” style view that business stakeholders frequently request, built entirely through pivot table native features without needing any separate formula-based ranking calculation.
A Quick Reference for Common Sorting Needs
| Business Need | Method |
|---|---|
| Highest value first, general case | Right-click a value, Sort, Largest to Smallest |
| Alphabetical order (rarely the actual need) | Field dropdown, Sort A to Z |
| Specific category pinned at top | Manually drag that row label to top |
| Months in calendar order | Ensure date field is grouped, not displayed as text |
| Custom non-alphabetical category order | Define a Custom List in Excel Options |
| Top 10 leaderboard view | Sort by value, then apply Top 10 Value Filter |
The Habit Worth Building
Before presenting any pivot table to a business audience, ask whether the current sort order actually answers the question they care about, or whether it is simply showing whatever default order Excel applied when the field was first added. Alphabetical order rarely matches a genuine business priority, and the seconds it takes to apply a value-based sort instead consistently makes the difference between a table that requires the viewer to scan for the answer and one that presents the answer immediately at the top.
What field are you trying to sort, and what specifically does not match what you need — wrong order, a category that will not stay pinned, or something else? Describe your situation and I can point you to the exact method that solves it.