Sort data in a PivotTable

Sorting data in alphabetical order or from highest to lowest values (or vice versa) is helpful when you have large amounts of data in the PivotTable you created. Sorting lets you organize the data so it’s easier to find the items you want to analyze.

For example, the Row Labels and Column Labels cells have an arrow button next to them you can use for sorting.

  1. Click a field in the row or column you want to sort.
  2. Click the arrow Filter drop-down arrow on Row Labels or Column Labels, and then click the sort option you want.

Row Label filter

  1. To sort data in ascending or descending order, click Sort A to Z or Sort Z to A.

Sort buttons

Text entries will be sorted in alphabetical order, numbers will be sorted from smallest to largest (or vice versa), and dates or times will be sorted from oldest to newest (or vice versa).

Sort on a column that doesn't have an arrow button

You can sort on individual values or on subtotals by right-clicking a cell, picking Sort, and choosing a sort method. The sort order is applied to all the cells at the same level in the column that contains the cell.

In the example shown below, the data under the category level (Helmet, Travel bag) is sorted alphabetically, A to Z.

Default sort on Row Labels

To see the grand totals for products sorted largest to smallest, choose any number in the Grand Total column, and sort on it.

Largest to smallest sort on Grand Total column values

For more information about sorting, see About sorting in PivotTables at the end of this article.

 Tip    To quickly find what you need, you can also group your PivotTable data. Or you can find the top or bottom values in a set of data, such as top 10 or bottom 5 sales amounts by filtering your PivotTable data or applying conditional formatting.

Set custom sort options

To sort specific items manually or change the sort order, you can set your own sort options.

  1. Click a field in the row or column you want to sort.
  2. Click the arrow Filter drop-down arrow on Row Labels or Column Labels, and then click More Sort Options.

Row Label filter

  1. In the Sort dialog box, pick the type of sort you want:

Sort dialog box

  • Click Manual to rearrange items by dragging them.

You can't drag items that are shown in the Values area of the PivotTable Field List.

  • Click Ascending (A to Z) by or Descending (A to Z) by, and then choose the field you want to sort.
  1. For additional options, click More Options, and then pick the option you want in the More Sort Options dialog box:

More Sort Options dialog box

  • Under AutoSort, check or uncheck the Sort automatically every time the report is updated box to allow or stop automatic sorting whenever the PivotTable data is updated.
  • Under First key sort order, pick the custom order you want to use. This option is available only when the Sort automatically every time the report is updated box under AutoSort has been unchecked.

Excel has day-of-the-week and month-of-the year custom lists, but you can also create your own custom list for sorting.

 Note    A custom list sort order is not retained when you update (refresh) data in your PivotTable.

  • Under Sort By, click Grand Total or Values in selected columns to sort by these values. This option is not available when you set sorting to Manual.

 Tip    Click Data source order to return items to their original order. This option is available for Online Analytical Processing (OLAP) source data only.

About sorting in PivotTables

When you sort data in a PivotTable, be aware that:

  • Data that has leading spaces will affect the sort results. For optimal results, remove any leading spaces before you sort the data.
  • You can’t sort case-sensitive text entries.
  • You can’t sort data by a specific format, like cell or font color, or by conditional formatting indicators, such as icon sets.

More about PivotTables

 
 
Applies to:
Excel 2013