How to Sort a Pivot Table in Excel

Excel is a powerful tool to store, organize, and visualize large volumes of data. A cell, a rectangular block is used to store each data unit. It can be used to visualize data using a graph plot or to get insights from data using formulas and functions. Generally, account professionals use this tool for financial accounting but everyone can use it according to their needs.

How to Sort Pivot Table in Excel

A pivot table is a tool in Excel that allows users to aggregate data based on certain criteria to compare patterns and trends through the data in less time. Sorting the data in a Pivot table will make the data easy to analyze. Sorting can be done in multiple ways: Lowest to highest, Highest to lowest, or any other Custom order. Let’s see how to do pivot table sorting.

First, we have to create a pivot table to perform sorting on it. For demonstration, we have the following table given below, and we will create a pivot table to compare the total value spent by each individual:

Step 1: Insert the Pivot Table

Select the data and then select Insert-> Pivot table. To learn how to insert a pivot table in detail click here.

Step 2: Select the Option

Select the option ‘amount by buyer‘ from the given options and then select the existing sheet option (to view the pivot table in the same sheet as of data).

Step 3: Give the cell value

Give the cell where you want to place the pivot table and then select Insert.

Step 4: Pivot table Created

How to Sort the Data in Ascending Order

Perform the below steps to sort the pivot table in ascending order of “sum of amount” values(i.e. least amount should be at the top).

Step 1: Select any value

Select any value from the field on which you want to perform sorting.

Step 2: Select the Sort option

Right-click on the selected cell and select the ‘sort’ option.

Step 3: Select the Sorting

 Select the sorting order you wish to perform (ascending in this case).

Step 4: Preview Result

We can see that the amount values are sorted in increasing order.

Similarly, we can sort the data in descending order.

How to Sort the Data in Descending Order

Follow the same steps to create a pivot table as mentioned above.

Step 1: Select any value

Select any value from the field on which you want to perform Sorting.

Step 2:  Select the option

Right-click on the selected cell and select the ‘Sort’ option.

Step 3: Select the Sorting

Now Select the sorting order that is descending order (Z-A).

Step 4: Preview the Result

How to Custom Sort in a Pivot table

Follow the below steps to sort your data using custom sort.

Step 1: Create a dataset in Excel

Step 2: Insert a Pivot table

Step 3: Right-click on the Column or Row labels that you want to sort and Select the Sort option.

Or Sort option can be accessed from the pivot table menu or toolbar.

Step 4: Choose Custom Sort

Choose “Custom sort” in the Sort option and define the custom sort order according to your choice.

Note: Multiple Levels of sorting can be added if needed.

Apply the custom sort order and observe the result Your data is now sorted according to your choice.

Restrictions in Pivot Table Sorting

You can sort the data in the following ways:

• By labels, A-Z or Z-A
• By one of the value Fields, in Largest to Smallest or Vice Versa.

Restrictions:

If there is no Pivot field to the left of the field that you are sorting, all the Pivot items will be sorted together.

If there are fields to the left of the field that you are sorting, the items will be sorted within each item of the next field to the left.

How to Sort by Lables

Step 1: Click on a cell containing a pivot item you want to sort.

Step 2: Go to the “Data” tab in Excel.

Step 3: Select the Sort Option

Click either the “A-Z” button to sort items in ascending order or the “Z-A” button to sort items in descending order

How to Sort by Values

Sort Pivot Table Items by Values

Step 1: Click on a value associated with a pivot item that you want to sort

Step 2: Go to the Data tab in Excel.

Step 3: Select the Sort Option

Click either the “A-Z” button to sort values in ascending order or the “Z-A” button to sort values in descending order.

FAQs

What is Autosort in Excel?

 This feature in Excel is used to sort the data automatically whenever the data is updated.

What are the points to remember while sorting the data?

• There should be no leading space in the data.
• Sorting doesn’t take place in case-sensitive entities.
• Data can not be sorted by specific cell or font color formats.
• You can not perform sorting through Conditional formatting indicators, such as icon sets.

What is Pivot Table in Excel?

A pivot table is one of the most useful features in Excel which enables the summarization of data for easy analysis and to perform other operations quickly. It gives the data a different perspective. 

Why is sorting pivot tables in Excel important?

Sorting in a pivot table is used to arrange the data in such a format that is Ascending or Descending order or any other order according to the user’s choice so that it will be useful to perform any operation or to analyze the data easily and quickly.