How to Sort a Pivot Table in Excel?

by

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).

Data for Pivot Table

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.

Pivot Table created using the dataset

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.

  1. Click on the AutoSort option in the Row Label cell as shown in the screenshot
Click on the AutoSort option
  1. It will show various sorting options. You can choose any option but for this example, I am going to select Sort A to Z.
Select the sorting criteria

This will rearrange all the data in the Pivot Table according to the Row Label (which are sorted alphabetically from A to Z)

Row label data in the Pivot table is sorted

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.

  1. 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.
Right click on the cell
  1. 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.
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.

Data sorted based on the selected cell column

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

  1. First of all select any Row label in the Pivot Table.
select any Row label
  1. Now click on the Home tab in the ribbon
Click the Home tab
  1. Click on the ‘Sort & Filter’ option
Click on the 'Sort & Filter' option

3) From the dropdown that shows up select the option Sort A to Z

Sort A to Z

This will sort all the Row Labels alphabetically from A to Z as shown in the following screenshot

Row labels sorted

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.

  1. Select any cell in the ‘Central’ column
Select any cell in the Central column
  1. Now click on the Home tab in the ribbon
Click the Home tab
  1. Click on the option Sort & Filter
Click on sort and filter
  1. From the options that show up click on ‘Sort Smallest to Largest’
Sort Smallest to Largest

This will sort the Pivot Table based on the Central region (smallest to largest) as shown below.

Central column has been sorted

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment