Pivot Table Tutorial Part 5 - How to Sort a Pivot Table in Excel


Excel 2007 Advanced Pivot Table with page field

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.

In order to show how to sort the values in a Pivot Table, we will use the pivot table on the right, which shows a company's monthly sales.

In order to sort a column or row of the Pivot Table, we also use the Excel Sort Ascending and Sort Descending Excel commands which are represented by the following symbols in the Excel menu:

Sort Button AZ Ascending sort ascending
Sort Button ZA Ascending sort descending

These options are found:

In Excel 2007/2010: In the 'Home' tab, under the 'Sort & Filter' option or
By right-clicking on the Pivot Table column and selecting the 'Sort' option
In Excel 2003: In the 'Standard' toolbar or
in the 'Data' menu, within the 'Sort...' option

Excel Pivot Table Sorted by Column

Sort a Pivot Table by a Column

Imagine you want to find out the month of the year in which the highest sales figures were recorded. By sorting the 'Grand Total' column of the pivot table, in descending order, the month with the greatest sales figures is positioned at the top of the table.

To do this, click on any value inside the 'Grand Total' column and select the 'Sort Descending' option.

The resulting sorted Pivot Table is shown on the right. This enables you to easily see that the month with the highest sales was June, followed by October.



Excel Pivot Table Sorted by Row

Sort a Pivot Table by a Row

Imagine you want to order the yearly sales totals, so that the person with the smallest 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), and select the 'Sort Ascending' option.

The resulting sorted Pivot Table is shown on the right. As required, the individuals have been ordered, so that the lowest total sales ($576,817) 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.

Return to the Excel Pivot Table Tutorial Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net