Pivot Table Tutorial Part 5 - How to Sort a Pivot Table in Excel
Sort a Pivot Table in Excel
You can sort a Pivot Table in Excel horizontally or vertically. This allows you to see, at a glance, the rows or columns containing the greatest or the smallest values.
The easiest way to sort a Pivot Table is to select a cell in the row or column that you want to order by and then select either Sort Ascending or Sort Descending, which are represented by the following symbols in the Excel menu:
The 'Sort Ascending' and 'Sort Descending' commands are found:
| In Current Versions of Excel: | Within the 'Sort & Filter' group on the 'Data' tab of the ribbon
or By right-clicking on the Pivot Table and selecting the 'Sort' option |
| In Excel 2003: | In the 'Standard' toolbar or in the 'Data' menu, within the 'Sort...' option |
Examples
The following examples use the above pivot table on the right, which shows a company's sales figures, broken down by month and sales representative.
The examples show how to sort the pivot table so that it is ordered by the values in a column or by the values in a row.
Sort a Pivot Table by a Column
Imagine you want to order the months of the example pivot table, so that the month that recorded the greatest total yearly sales is listed first.
To do this:
- Click on any value inside the 'Grand Total' column;
- Select the 'Sort Descending' command.
The resulting sorted Pivot Table is shown on the rightabove. This enables you to easily see that the month with the highest sales was June, followed by January.
Sort a Pivot Table by a Row
Imagine you want to order the yearly sales totals, so that the person with the greatest total yearly sales is listed first.
To do this:
- Click on any value inside the 'Grand Total' row (i.e. cell B17 or C17 of the example spreadsheet);
- Select the 'Sort Descending' command.
The resulting sorted Pivot Table is shown on the rightabove. As required, the individuals have been ordered, so that the highest total sales (Smith - Total Sales: $345,908) appears first.
Note that you could also sort the table alphabetically, according to the sales peoples' names. This is done by clicking on one of the names (either cell B4 or C4) and sorting either in ascending or descending order.