A pivot table in Excel is an excellent tool for analyzing data. It helps you to aggregate, summarize, finding insights and present a large amount of data in a few clicks. It is also very flexible and can be changed without much effort. You can sort a large amount of data very easily using pivot tables. In this tutorial, we will see how to sort a pivot table based on two or more columns.
Sorting a Pivot Table by Two Columns
In this example, you will use the quarterly sales data of Jingo Juice corner. The data consists of quantity and sales for beverages during the 2nd quarter of the year through four states.
The pivot table aggregates the items based on months and shows the sales. The original data had 133 entries which are summarized very efficiently with the pivot table.
Sorting a Pivot Table
You can sort a pivot table in ascending or descending order like any other tables. You can sort the labels and the fields. To sort the labels, you click on the filter icon beside it. From there, select “sort A to Z” or “sort Z to A” to sort in ascending or descending order. To sort any pivot table field, you need to click anywhere in the column and click sort in the Data tab in the ribbon and select how you want to sort.
If you wanted to sort the labels in descending order:
- Click the filter icon beside “Row labels”.
- Select “sort Z to A”.
This will show the items in descending order. You can also select “More sort options” to fine-tune your sorting.
To sort the table based on the sales in May in descending order, you would need to perform the following steps:
- Click on cell E6.
- Click on Data > Sort.
- Select “Largest to Smallest” as sort option and “Top to Bottom” as Sort direction.
- Click OK.
This would rearrange the table based on the sales in May in descending order.
Sort Two columns in Pivot Table
Normally, it is not possible to sort a pivot table based on two columns. However, with a workaround adding a calculated field, it is possible to sort two columns in a pivot table.
For this example, you will use the order data for the beverages from the previous example. The data contains the beverage ID, name and total orders.
The pivot table shows the Item names, order count and total orders.
This is the pivot table. If you wanted to sort it by “Count of Orders” first and then by “Sum of Orders” in descending order, you would need to perform the following steps:
- At first, you would need to add an extra column to your data table. Name it “Count.” Set the value of cell E2 to 1 and drag it down to the cells below.
- Click anywhere on the pivot table.
- Go to Analyze > Fields, Items and Sets > Calculated Fields.
- On the name field Type “Rank,”, on the formula field insert the formula using the “Insert field” button.
“=(Count * 1000) + (Orders/1000)”
- Click OK.
- Now the calculated fields will have the desired ranks. To sort it select the cells D4:D9. Go to Data > (Sort) Z – A. This will sort the pivot table in the desired way. Now to hide the rank column right click on the column name on top and select Hide.
Sorting Excel tables with Power Pivot
Power pivot is an add-in for Excel used to perform powerful data analysis, data modeling and gather business insights. It can handle large volumes of data from different sources in a single Excel file.
Power pivot is built into certain versions of Office, but to use it you have to enable it at first. To do that:
- Go to File > Options > Add-Ins.
- Click COM Add-ins in the Manage box > Click Go.
- Check the box for Microsoft Office Power Pivot > click OK. Select the Power Pivot add-in for Excel if you have other versions of Power pivot installed.
Power pivot has a great option to sort: Sort by column. In order to use Power pivot, at first you need to add your data to the data model. To do that, from your worksheet,
Click on Power pivot > Add to Data Model. Check if the range matches your data and click “My table has headers.”
For this example, we will use the popular ranked beverages from the previous example. It has the beverage names and their rank based on popularity. You will sort the beverages based on the rank. To do this:
- On the power pivot window click PivotTable. Check New worksheet and click OK. Now in the pivot table, set the “Items” as row and “Rank” as values.
- Go back to the power pivot window. Select cells 1:11 having the item names and go to Home > Sort by Column.
- Set “Items” as the sort column and “Rank” as the By column.
- Click Ok.
This will sort the items based on ranks in the pivot table.
Power Pivot can sort multiple columns as well which is a lot simpler than sorting in pivot tables. Pivot tables are a great way to summarize and aggregate data to model and present it. Power Pivot is also another great tool to gain business insights.
There are many interesting features of Pivot Table and Power Pivot that could help you gain insights into your data. If you want to save hours of researching and frustration and get to the solution quickly, try our Excel Live Chat service! Our Excel experts are available 24/7 to answer any Excel question you have on the spot. The first question is free.