Sorting a Pivot Table in Excel

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.

How to Sort Pivot Table Data in Excel?

Examples

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.

  • First, create a Pivot Table based on data. Right-click the value to be sorted in the data and select the desired sorting command.

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 usNew Worksheet Where The Excel Takes UsTo add a new worksheet, go to the bottom of the screen and press the ‘+’ button. You can also navigate to ‘Home’, ‘Insert,’ and then ‘Insert Sheet’ to add a new sheet.read more, 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.

Things to Remember

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

This article is a guide to Excel Pivot Table Sort. Here, we discuss sorting PivotTable data values in Excel, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –