Sort Pivot Table Left to Right

When you create a pivot table, the items in each field are sorted alphabetically, in ascending order, like the products and cities shown below.

The only exception to this is for items that appear in a Custom List, such as month and weekday names, as you can see in the next pivot table. If you have the Custom Lists setting turned on, this sort order will take precedence over the alphabetical order.

Change the Sort Order

Instead of alphabetical order, you might need to show the results based on the values. In the products pivot table, you can quickly sort by the values in the Grand Total column or the Grand Total row.

• Select one of the Grand Total cells (row or column)
• On the Ribbon, click the Data tab
• Click A-Z (Smallest to Largest) or Z-A (Largest to Smallest)

In the next screen shot, both the Grand Total column and the Grand Total row have been sorted Largest to Smallest.

Sort an Item Row

Although you can easily sort the Grand Total row in ascending or descending order, left to right, you can’t do the same thing in the row for one of the Products.

If you select a cell in the Oatmeal Raisin row, and sort Z-A, it sorts by the values in the Grand Total column, not the Oatmeal Raisin row.

It’s possible to sort the row’s values though – it just takes a few extra steps.

To sort the Oatmeal Raisin row in descending order:

1. In the pivot table, right-click a value cell in the Oatmeal Raisin row.
2. Click Sort, and then click More Sort Options
3. In the Sort By Value dialog box, under Sort Options, select Largest to Smallest.
4. Under Sort direction, select Left to Right.
5. In the Summary section, you can see a description of the sort settings.
6. Click OK to close the dialog box.

After you sort the Oatmeal Raisin row, left to right, the values in the Oatmeal Raisin row are sorted largest to smallest, from left to right.

The City column order has changed:

• Seattle, which has the highest Oatmeal Raisin sales, is at the left.
• Dallas, which has the highest Grand Total, is in second place.

Please visit my Contextures website for more information on pivot table sorting.

__________________