Search
Contextures Sites
| |||
Excel Pivot Table Sorting
|
|
|
|||
Contextures Inc., Copyright ©2013
All rights reserved.
Search Contextures Sites
Excel Pivot Table Sorting
New Pivot Table Items Out of Order
Sort a Pivot Table Field Left to Right
Sort Pivot Field in Custom OrderAlso see: Manually Move Pivot Items
More Pivot Table Tutorials and Videos
New Pivot Table Items Out of Order
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.
Fix the Sort Order
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.
This also sorts the drop-down list, and makes it easier for users to find the items they need.
Sort a Pivot Table Field Left to Right
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.
Sort Pivot Field in Custom Order
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.
Contextures Inc., Copyright ©2013
All rights reserved.
Last updated: May 21, 2013
