Pivot Table Sort

Sorting a pivot table in Excel option is available in the "Data" tab, and as the name suggests, we can sort the data in the PivotTables. On the PivotTables, right-click on any data we want to sort, and we will get an option to sort the data as we want. The normal sort option does not apply to PivotTables, and PivotTables are not the normal tables. The sorting done from the PivotTable is known as PivotTable Sort.

Sorting means arranging data or certain items in an order, however desired. It can be in ascending or descending order, sorted by values or ranges. At the same time, a PivotTable is a unique tool to summarize data to form a report.

When we generate data, we can arrange it in ascending or descending order in the PivotTable, just like any other cell range where we can sort the data using the AutoFilter tool.

Following are the steps used for sorting PivotTable data in Excel: -

1. First, create a Pivot Table based on data.

2. Right-click the value to be sorted in the data and select the desired sorting command.

    

Example #1

We have data where the quality check department has marked a product "OK" for use and "NOT OK" and "AVERAGE" for use for individual "Product ID." We will build a PivotTable for the data and then find out the highest number of each proportion.

Consider the following data:

• The first step is inserting a PivotTable into the data. Then, in the "Insert" tab under the "Tables" section, click on the "PivotTable." A dialog box appears.

• It asks for the data range. We will select the whole data in this process and click on "OK."

We can add a PivotTable 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 the "Rows" and "Product ID" in the "Values" field.

• We can see on the left that the report has been created for the PivotTable.

• For the current example, we will sort the data in ascending order. Right-click on the "Count of Product ID" column. A dialog box appears.

• When we click on "Sort," another section appears. For example, we will click on " Sort Smallest to Largest."

• We can see that our data has been sorted in ascending order.

Example #2

We have data for a company for sales done each quarter by certain products for 2018. We will build a PivotTable over the data and sort the data concerning quarters and the highest number of sales done each quarter.

Have a look at the data below,

• The first step is the same. We need to insert a PivotTable into the data. In the "Insert" tab under the "Tables" section, click on the "PivotTable." A dialog box appears.

• As earlier, we need to give it a range. We will select our sales data in the process.

• When we click "OK," we may see the PivotTable fields. Now, drag "Quarters" in "Columns," "Product" in "Rows," and "Sales" in "Values."

• We have built up our PivotTable for the current data.

• Now, we will first sort the quarters. Then, we will click on the "AutoFilter" in the "Column Labels."

• A dialog box appears where we can see the option to sort the quarter from A to Z or Z to A.

• We can choose either of them as to how we want to display our data.
• Now, right-click on "Sales." Another dialog box appears.

• Whenever our mouse is on the "Sort" option, we can see another section where we select largest to smallest.

• Now, we have sorted our data from largest to smallest in terms of sales in our PivotTable.

Example #3

Consider the below table showing sales in years 2017 and 2018.

Now, we have to create a pivot table by clicking on Insert - Tables - PivotTable.

In the Create PivotTable window, we need to select the Table/Range: of the data we want to create a pivot table for. And then, click OK.

In the PivotTable fields, we need to select the sections which we want to view in our pivot table.

Now, right click on Row Labels more options and we can see Sort options, readily available.

Let’s try both the options. If we sort from oldest to newest, the resulting pivot table appears as shown in the below image.

If we sort from newest to oldest, the resulting pivot table appears as shown in the below image.

Likewise, we can sort PivotTable in Excel.

• Excel PivotTable Sort is done on a PivotTable. So, we must first generate a PivotTable.
• Sorting depends on the data. It means if the data is numerical, it can be sorted from highest to smallest or vice versa.
• If the data is in string format, it may be sorted in A to Z or Z to A.