Sorting a Pivot Table in Excel
While we have sorting option available in the tabs section, but we can also sort the data in the pivot tables, on the pivot tables 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 is not applicable to pivot tables as pivot tables are not the normal tables, the sorting done from the pivot table itself is known as pivot table sort.
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. At the same time, a Pivot table is a unique tool to summarize data to form a report.
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 the auto filter tool to sort our data. In the Pivot table, we can also sort our data in ascending or descending order.
How to Sort Pivot Table Data in Excel?
Following are the steps used for sorting pivot table data in excel –
- First, create a Pivot Table based on data.
- In the data, right-click on the value to be sorted and select the desired sorting command.
We have data where the 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 into 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 a pivot table 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., 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.
- For the current example, we will sort the data in ascending order. In the count of product, Id column right-clicks 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.
We have data for a company for sales that 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 the 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 to how we want to display our data.
- Now right click on sales, and another dialog box appears,
- Whenever our mouse is on the 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.
Things to Remember
- Excel Pivot table sort is done on a pivot table, so we need to build a pivot table first.
- Sorting depends on the data. This means if the data is numerical, it can be sorted from 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.
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 –