Table of Contents
Sorting a Pivot Table in Excel
Sorting means arranging data or certain items in an order however desired. It can be ascending order descending order, sort by any values or range. Whereas a Pivot table is a unique tool to summarize data to form a report. In a pivot table we can also sort the data, thus today’s topic Excel Pivot Table Sort.
Whenever we build our data, it doesn’t generally means we might look at the report in the same way a pivot table shows us. In fact, maybe we want our data to be in ascending or descending order. Like in any normal cell range we can use auto filter tool to sort our data. In Pivot table we can also sort our data in ascending or descending order.
How to Sort Pivot Table Data in Excel?
- First Create a Pivot Table based on a data.
- In the data right click on the value to be sorted and select the desired sorting command.
Now let us learn how to sort our pivot table by few examples.
Excel Pivot Table Sort Example #1
We have a data where quality check department has marked a product “OK” for use and “ NOT OK” and “AVERAGE” for use for certain product id’s. We will build a pivot table for the data and then find out the highest number of each proportion.
Consider the following data,
- Now the first step is to insert a pivot table to the data. In the Insert tab under the tables section click on the pivot table and a dialog box appears.
- It asks for the data range and we will select the whole data in this process, Click on OK .
We can add pivot table either in a new worksheet or in the same worksheet.
- In the new worksheet where the excel takes us, we can see the fields section we discussed earlier. Drag Condition in Rows Fields and Product Id’s in Values.
- We can see on the left that the report has been created for the pivot table.
- Now to sort the data, for the current example we will sort the data in ascending order. In the count of product Id column right click on it and a dialog box appears.
- When we click on sort another section appears and we will click on smallest to largest.
- We can see that our data has been sorted in ascending order.
Excel Pivot Table Sort Example #2
We have data for a company for sales are done in each quarter done by certain products for the year 2018. We will build a pivot table over the data and sort the data with respect to quarters and the highest number of sales done in each quarter.
Have a look at the data below,
- Now the first step is same we need to insert a pivot table to the data. In the Insert tab under the tables section click on the pivot table and a dialog box appears.
- Same as earlier we need to give it a range and we will select our total data in the process.
- When we click on OK we will see the pivot table fields, Now drag quarters in columns, Product in rows and sales in values,
- We have built up our pivot table for the current data,
- Now we will first sort the quarters we will click on the auto filter in the column label,
- A dialog box appears in which we can see the option to sort the quarter from A to Z or Z to A,
- We can choose either one of them as how we want to display our data.
- Now right click on sales and another dialog box appears,
- Whenever our mouse is on sort option we can see another section appears where we will select largest to smallest.
- Now we have sorted our data from largest to smallest in terms of sales in our pivot table.
Explanation of Excel Pivot Table Sort
Where the pivot table is in itself a very powerful tool to build up reports to summarize a lot of chunks of data, pivot table sort helps us to visualize the data as we desire. Just by seeing at a raw report we cannot conclude easily. We saw this in example 2 if the boss wanted to check which product did more sales? In a raw report, he might have to look to find out the highest value of sales done by the product. But since we sorted the data we can see that Product 2 did most of the sales with 2324 Cr in sales. He did not had to go through the whole report to find out that certain thing.
Things to Remember About Excel Pivot Table Sort
- Excel Pivot table sort is done on a pivot table so we need to build a pivot table first.
- Sorting depends on the data. Which means if the data is numerical it can be sorted to Highest to smallest or vice versa or if the data is in string format it will be sorted in A to Z or Z to A.
You can download this Pivot Table Sort Excel Template here – Pivot Table Sort Excel Template
This has been a guide to Excel Pivot Table Sort. Here we discuss how to sort pivot table data values in excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –