Suppose you have a large dataset and you have created a Pivot Table using that data. Now, you want to sort the values in the Pivot Table from largest to smallest. It may be useful to find top-performing products, highest sales regions, or lowest expenses. In this article, I will show you how to sort Pivot Table data from largest to smallest.
Key Takeaways
- Sorting helps spotlight the highest or lowest values in your Pivot Table instantly.
- You can easily sort a field by right-clicking on a value and choosing sort options.
- The filter drop-down in row or column labels allows ascending or descending sorting.
- When you sort one value, the entire row rearranges.
- You can sort Pivot Tables by numeric values or alphabetically.
Table of Contents
What is a Pivot Table?
A Pivot Table is a useful tool in Excel. It helps you summarize and analyze large amounts of data. It also allows you to group data, calculate totals, compare values, and spot patterns. There is no need to write complex formulas when using Pivot Tables.
How to Sort by Largest or Smallest With Excel Pivot Tables
Using Right Click
STEP 1: Right-click on a Year cell within the Pivot Table.
Go to Sort > Sort Newest to Oldest
This will sort our years in descending order. Notice that 2014 was sorted first.
STEP 2: Now let us try to sort by Quarters.
Right-click any Quarter name within the Pivot Table.
Go to Sort > Sort Newest to Oldest
This will sort our Quarters in descending order. Notice that Quarter 4 was sorted first.
STEP 3: Now let us try for the Sum of SALES.
Right-click on a sum value within the Pivot Table.
Go to Sort > Sort Largest to Smallest
This will sort our sum of Sales in descending order, regardless of the Quarter.
Using Row Label Dropdown
STEP 1: Click the dropdown arrow next to Row Labels.
STEP 2: Select More Sort Options.
STEP 3: Choose Descending (Z to A) by your value field.
STEP 4: Click OK.
Common Mistakes to Avoid
- Sorting the wrong column: Always click the correct value before sorting.
- Forgetting multiple values exist: Sorting one field doesn’t apply to others.
- Using manual sorting accidentally: Dragging items can override automatic sorting.
Frequently Asked Questions
How do I sort Pivot Table values from largest to smallest?
To sort Pivot Table values from largest to smallest,
- Right-click a cell with a value
- Choose Sort
- Select Sort Largest to Smallest
Can I sort multiple fields at once in a Pivot Table?
No, sorting works on one field at a time. But you can repeat the process for each field if needed.
Why isn’t my Pivot Table sorting correctly?
The Pivot Table may not sort correctly due to grouping or formatting issues. You need to make sure your values are not formatted as text.
How to sort Pivot Table rows alphabetically instead of numerically?
To sort Pivot table rows alphabetically,
- Select the row label dropdown
- Choose to sort A to Z or Z to A
Can sorting a Pivot Table change source data?
No. Sorting only changes the data displayed in the Pivot Table. It does not impact the original dataset.
Bryan
Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).
He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.
30 Days - Full Access 
