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.

pivotsortleftright01

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.

pivotsortleftright02

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)

pivotsortleftright03

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

pivotsortleftright04

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.

pivotsortleftright05

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.

pivotsortleftright06

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

__________________

This entry was posted in Sort and Filter. Bookmark the permalink.

One Response to Sort Pivot Table Left to Right

  1. Maxim Manuel says:

    Thank you again for sharing great tips. I appreciate.

Leave a Reply

Your email address will not be published. Required fields are marked *