Pivot Table is a very handy feature in Excel to calculate, analyze and summarize data. Using Pivot Table, we can see comparisons, patterns, and trends in our data.
However, when you create a Pivot Table, the data is not sorted automatically. However, if you need it to be sorted based on row/column labels or based on cell values, you can easily do that.
This tutorial will guide you through all the methods using which you can easily sort data in Pivot Table so that it will be easy for you to analyze and summarize data.
For demonstration purposes, I will use a sample dataset of sales done by various representatives in different regions (shown below).
Based on the above data, I have created a Pivot Table that provides the Grand Total of sales done by various representatives across different regions.
You can see from the above Pivot Table that the data is not sorted. However, you can sort the data in the Pivot Table based on the Row Label, Column Label, or based on values.
Method 1: Sort Pivot Table Using AutoSort Feature
Let’s first look at how to sort a pivot table using the autosort option that can be accessed right from the Pivot Table headers.
Sort Pivot Table Using Row/Column Labels
You can alphabetically sort Pivot Table based on the Row/Column Label. Let’s do it step by step.
- Click on the AutoSort option in the Row Label cell as shown in the screenshot
- It will show various sorting options. You can choose any option but for this example, I am going to select Sort A to Z.
This will rearrange all the data in the Pivot Table according to the Row Label (which are sorted alphabetically from A to Z)
In the above method, we have sorted data alphabetically based on the Row Labels. Similarly, you can do this with Column Labels as well. Just click on the Column Label AutoSort option and you will get the same options.
Also read: How to Connect Slicer to Multiple Pivot Tables
Sort Pivot Table using Cells Values
You can sort the Pivot Table based on the values by following the below steps.
- Right-click on a cell based on which you want to sort the Pivot Table. In this example, I am going to sort the Pivot Table according to the sales done by various representatives in the Central region. So, I am going to select a cell in the Central region column as shown.
- From the options that show up, you can choose any sorting criteria but for demonstration purposes, I am going to choose Sort Smallest to Largest.
This will rearrange all the data in the Pivot Table based on the Central region (which is sorted from smallest to largest) as shown in the screenshot.
The above steps would sort the Pivot Table using the values in the Central column (from smallest to largest). All the other columns would automatically adjust to keep the records intact.
Also read: How to Move Pivot Table in Excel?
Method 2: Sort Pivot Table Using Option in the Ribbon
In this example, I will show you how you can sort Pivot Table using the ‘Filter and Sort’ options available in the ribbon.
Sort Pivot Table Using Row/Column Labels
Using the Filter and Sort option available in the ribbon you can alphabetically sort Row/Column Labels. Let’s do it step by step
- First of all select any Row label in the Pivot Table.
- Now click on the Home tab in the ribbon
- Click on the ‘Sort & Filter’ option
3) From the dropdown that shows up select the option Sort A to Z
This will sort all the Row Labels alphabetically from A to Z as shown in the following screenshot
Similarly, you can do so with the Column Labels or any other Text data in the Pivot Table.
Sort Pivot Table Using Cell Values
If you want to sort any numeric data in the Pivot Table you can quickly do so by using the Sort & Filter option available in the ribbon.
In this example, I am going to sort the Pivot Table based on the Central region (from smallest to largest). Let’s do it step by step.
- Select any cell in the ‘Central’ column
- Now click on the Home tab in the ribbon
- Click on the option Sort & Filter
- From the options that show up click on ‘Sort Smallest to Largest’
This will sort the Pivot Table based on the Central region (smallest to largest) as shown below.
In this tutorial, I showed you various methods of sorting pivot tables based on the text and value.
While I have shown you by taking a simple example, you can sort any Pivot Table using the methods covered in this tutorial. In case your Pivot table has dates, you can still use the same method (where the sorting options would change accordingly).
I hope this Excel article was useful and helped you get a better understanding of how to sort Pivot Tables in Excel.
Other Excel articles you may also like:
- How to Refresh Pivot Table in Excel? (Shortcut + VBA)
- Remove Grand Total From Pivot Table in Excel (Easy Steps)
- How to Add Calculated Field to Pivot Table?
- Change Count to Sum in Excel Pivot Table
- How to Delete a Pivot Table? 4 Different Ways!
- How to Group by Months in Excel Pivot Table?
- How to Sort by Date in Excel (Single Column & Multiple Columns)
- How to Unsort in Excel (Revert Back to Original Data)
- How to Sort by Length in Excel? Easy Formula!