You can sort horizontally and vertically in an Excel PivotTable. This way you can clearly see the rows or columns containing the maximum or minimum values.
We will show how sorting is done using the example of a pivot table containing sales data for a company by month, broken down by salesperson.
To sort data in a column or row of a pivot table, you must use the commands Sort Ascending (Sort Ascending) и Descending sort (Sort Descending), which on the Ribbon of the Excel menu are indicated by the following icons:
These commands are:
- In modern versions of Excel. On the Advanced tab Data (Data) in section Sort and filter (Sort & Filter) or use item Sorting (Sort) context menu, which can be called by right-clicking on the pivot table.
- In Excel 2003. On the toolbar Standard (Standard) or menu Data > Sorting (Data > Sort).
Sorting in a pivot table by column
Suppose we want to understand which months have shown the highest sales results.
If you sort the pivot table by column Grand Total in descending order, the month with the highest sales result will be located at the top of the table, the next one will be the second largest, and so on.
To sort by value in a column Grand TotalYou need:
- Click on any value in a column Grand Total;
- Push command Descending sort (Sort Descending).
The picture shows how our pivot table will look after sorting. Now you can easily understand that the maximum sales were in June (Jun), and January is in second place (Jan).
Sorting in a pivot table by row
Suppose you want to arrange the sales results for the year in such a way that the owner of the largest result is listed first in the table.
To do this:
- Click on any value in a row Grand Total (in our table, these will be cells B17 or C17);
- Push command Descending sort (Sort Descending).
The result is shown in the picture above. As required, the sellers are arranged in such an order that the maximum value of sales (Smith with total result $ 345,908) is listed first in the table.
Please note that the data in the table can also be sorted alphabetically by ordering the names of sellers. To do this, click on one of the names (cells B4 or C4) and sort in ascending or descending order.