Search Contextures Sites
Excel Pivot Table Sorting
Also see: Manually Move Pivot Items
When you add new records to your pivot table source data, and refresh the pivot table, new items might appear in the drop down lists.
Sometimes, those new items appear at the end of the list, instead of in alphabetical order. For example, in the screen shot below, binders were just added to this pivot table’s source data.
When the pivot table was refreshed, Binders appeared at the bottom of the Product list, instead of the top.
Items in the pivot table drop down lists can get out of order, if you have manually rearranged the items in the Row Labels area. If a field is set for Manual sort, new items will appear at the end of the drop-down list.
Follow these steps to sort the field in ascending order:
- Right-click a cell in the Product field. For example, right-click the Envelopes cell.
- Click Sort, and then click Sort A to Z.
When you sort the Product field, its sort setting changes from Manual to Sort Ascending or Sort Descending.
Usually you sort a pivot table by the values in a column, such as the Grand Total column.
By sorting, you can highlight the highest or lowest values, by moving them to the top of the pivot table.
To sort a pivot table column:
- Right-click on a value cell, and click Sort.
- Then, click Sort Smallest to Largest or Sort Largest to Smallest
Sort a Pivot Table Row
You can also sort a pivot table row by its values, left to right. This moves the largest or smallest amounts to the left of the pivot table, so you can focus on them.
For example, in the pivot table shown below, the cities columns are in alphabetical order, with Boston at the left.
To hightlight the city with the highest sales, you can sort the Chocolate Chip row, so the sales per city are sorted in descending order.
To sort the pivot table row:
- In the pivot table, right-click a value cell in the Chocolate Chip row.
- Click Sort, and then click More Sort Options
- In the Sort By Value dialog box, under Sort Options, select Largest to Smallest.
- Under Sort direction, select Left to Right. In the Summary section, you can see a description of the sort settings.
- Click OK to close the dialog box.
After you sort the Chocolate Chip row, left to right, the values in the Chocolate Chip row are sorted largest to smallest, from left to right.
The City column order has changed, and Chicago, which has the highest Chocolate Chip sales, is at the left.
NOTE: Rows for other products may not be in descending order, because the column order has been set by the values in the Chocolate Chip row.
Most of the time, you'll sort a pivot table
- alphabetically, based on the row labels,
- or by values.
You can also create custom lists, such as department names or cities, and specify a unique sort order for those lists.
Then, in your pivot table, you can use those lists as the sort order, instead of sorting A-Z.
To create a custom list, follow the instructions here: Create a Custom List
Applying the Custom Sort Order
After you create a custom list, follow these steps to apply a custom sort order to a pivot table field:
- Refresh the pivot table.
- If the field which has items from a custom list is set for Automatic sort, it should change to the custom list's sort order.
- If the field is currently set for manual sorting, it won't sort according to the custom list order. To change it to automatic sorting, right-click a city label, click Sort, and then click Sort A to Z.
Prevent Custom Order Sorting in a Pivot Table
In some cases, you might not want the custom list sort order used in a pivot table. For example, if a salesperson's name is Jan or May, that name would appear at the top of a list, before other names, such as Ann or Bob.
To prevent the custom lists from taking precedence when sorting a pivot table, follow these steps to change the setting:
- Right-click a cell in the pivot table, and click PivotTable Options.
- In the PivotTable Options dialog box, click the Totals & Filters tab.
- In the Sorting section, remove the check mark from "Use Custom Lists When Sorting"
NOTE: This setting will apply to all the fields in the pivot table.
Pivot Table Introduction
Clear Old Items in Pivot Table
Create a Pivot Table in Excel 2007
Data Field Layout
Dynamic Data Source
FAQs - Pivot Tables
Filter Source Data
Filters, Top 10
Filters, Report Filters
Layout, Excel 2007
Multiple Consolidation Ranges
Printing a Pivot Table
Show and Hide Items
Unique Item Count
Pivot Table Books
Pivot Table Add-Ins
Pivot Table Videos
Clear Old Items
Copy a Custom PivotTable Style
Create Pivot Table in Excel 2007
Create Pivot Table from Multiple Sheets
Data Field Layout
Date Filters, Add
Layout, Excel 2007
Report Filters, Add
Subtotals, Create Multiple
Top 10 Filters
Contextures Inc., Copyright ©2013
All rights reserved.
Last updated: May 21, 2013