See all How-To Articles

Pivot Table Sorting Guide (New for 2023!) Excel & Google Sheets

This tutorial demonstrates various ways to sort data in a pivot table in Excel and Google Sheets.

 

pivot sort intro

 

When you create a pivot table, you may wish to arrange the data – in alphabetical order for text fields or numerical order for value fields. You can sort both labels and fields within a pivot table by using drop-down filter buttons within the pivot table, or by using Sort through the Ribbon.

Sort by Row Labels

Consider the pivot table below.

 

pivot sort table

 

  1. To sort the row labels from A–Z (ascending alphabetical order), first click anywhere in the list under the Row Labels heading.

 

pivot sort filter select region

 

  1. Then in the Ribbon, go to Home > Editing > Sort & Filter > Sort A to Z.

 

pivot sort ribbon

 

Alternatively, click the little arrow to the right of the Row Labels heading.

 

 

pivot sort dropdown

 

  1. Select Sort A to Z.

pivot sort drop down select
The item list in the selected field is automatically sorted in alphabetical order.

 

pivot sort sorted

 

You can, of course, also use Z to A and sort in the reverse order.

More Sort Options

  1. For more sorting options, click the arrow on the right side of the Row Labels cell, and then choose More Sort Options…

 

pivot sort more options

 

Alternatively, in the Ribbon, go to Home > Editing > Sort & Filter > Custom Sort…

 

pivot sort custom sort

 

  1. This brings up the Sort options dialog box where you can choose to sort Manually, Ascending, or Descending.

 

pivot sort options

 

Manual Sort

  1. Click Manual, and then click OK.
  2. Then drag individual rows of data up or down on the pivot table.

 

pivot sort drag field

 

Ascending / Descending

  1. From within the Sort options dialog box, use Ascending or Descending. While this is the same as sorting A–Z or Z–A as described previously, it lets you to choose the field to sort on.

 

pivot sort select field

  1. For example, choose Sum of Wheat instead of Salesperson.

 

pivot sort change field

 

As Sum of Wheat is a value column, it’s sorted from smallest to largest rather than from A–Z.

pivot sort value sorted

 

Custom List

  1. While in the Sort options dialog box, click More Options…

 

pivot sort more sort options

 

  1. AutoSort (Sort automatically every time the report is updated) is automatically checked.

 

pivot sort automatic sort

 

  1. To sort by a specific list, uncheck AutoSort and choose another list from the First key sort order drop down.

 

pivot sort custom list sort

 

  1. Click OK, and then OK again, to sort by your custom list.
    Note: To learn how to create a custom list, click here.

Sort by Field Values

You can also choose to sort by a field’s values.

  1. Click in the column of the field whose values you wish to sort (e.g., Sum of Wheat).
  2. In the Ribbon, go to Home > Editing > Sort & Filter > Sort Smallest to Largest.

 

pivot sort smallest to largest

 

Or use Sort Largest to Smallest to reverse the sort order.

 

pivot sort largest to smallest

 

Sort Directions

  1. In the Ribbon, go to Home > Editing > Sort & Filter > Custom Sort…

 

pivot sort values custom sort

 

Using this option, as well as sorting from smallest to largest or largest to smallest, you can also sort from top to bottom or left to right.

 

pivot sort by value

 

  1. Choose Left to Right, and then click OK.

 

pivot sort values left to right

 

The columns of the value fields are rearranged into alphabetical order from left to right.

Sort Order – Add New Data

If you add new data to the bottom of your source data, you many need to change the source data for your pivot table to make sure everything is included. But when you go to choose an item from the filter drop down, the list is not necessarily in alphabetical order anymore.

 

pivot sort values adding data

 

  1. To correct the sort order, drag the field down from the Filters area to the Rows area.

 

pivot sort move filter

 

  1. Then click on the field (e.g., Region) and sort from A to Z.

 

pivot sort region

 

  1. Next, move the field back to the Filters area from the Rows area. The list if items is then back in alphabetical order.

 

pivot sort filter sorted

 

Sort Multiple Row Fields

You can add multiple row fields to a pivot table. The example below has both Region and Salesperson added as row fields.

 

pivot sort row fields

 

  1. If you click in the Region field and click Sort A to Z, the regions are sorted in alphabetical order, but the names within each region may not be.
  2. Click within the nested field (e.g., Salesperson), and then click Sort A to Z.

 

pivot sort double sort

 

Tip: Try using some shortcuts when you’re working with pivot tables.

See all How-To Articles