Sort data in a PivotTable or PivotChart

Sorting data is helpful when you have large amounts of data in a PivotTable or PivotChart. You can sort in alphabetical order, from highest to lowest values, or from lowest to highest values. Sorting is one way of organizing your data so it's easier to find specific items that need more scrutiny.

Important: Here are a few things to remember about sorting data:

• Sort orders vary by locale setting. Ensure that you have the proper locale setting in Regional Settings or Regional and Language Options in Control Panel on your computer. For information about changing the locale setting, see the Windows Help system.

• 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, such as cell color or font color. Nor can you sort by conditional formatting indicators, such as icon sets.
   

In the sections below, learn how to sort a PivotTable or PivotChart in Excel Desktop and Excel Online.

Windows Online



Follow these steps to sort in Excel Desktop:

1. In a PivotTable, click the small arrow next to Row Labels and Column Labels cells.

2. Click a field in the row or column you want to sort.

3. Click the arrow on Row Labels or Column Labels, and then click the sort option you want.
   
   

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

Text entries will sort in alphabetical order, numbers will sort from smallest to largest (or vice versa), and dates or times will sort 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, choosing Sort, and then choosing a sort method. The sort order applies 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) has an alphabetical sort from A to Z.

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

Tip: To quickly find what you need, you can group, filter, or apply conditional formatting to the PivotTable or PivotChart.

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 on Row Labels or Column Labels, and then click More Sort Options.
   
   

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

• 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.

• For additional options, click More Options, and then pick the option you want in the More Sort Options dialog box:
  
  

• In AutoSort, check or uncheck the box for Sort automatically every time the report is update— either to permit or stop automatic sorting whenever the PivotTable data updates.

• Under First key sort order, choose the custom order you want to use. This option is available only when there is no check in the box for Sort automatically every time the report is updated.

  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.

• In 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.

Here's a quick way to sort data in rows or columns:

1. Click a field in the row or column you want to sort.

2. Click the arrow on Row Labels or Column Labels, and then click the sort option you want.
   
   
   
   If you click the Column Labels arrow, choose the field you want to sort first, and then the sort option you want.

3. To sort data in ascending or descending order, click Sort Ascending or Sort Descending.
   
   

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

Sort by a specific value

Follow these steps:

1. Click the arrow on Row Labels, and pick Sort by Value.
   
   
   
   If you click the Column Labels arrow, choose the field you want to sort first, followed by the sort option you want.

2. In the Sort by Value box, in Select value, choose a value to sort by.
   
   

3. In Sort options, pick the sort order you want.

See Also

• Create a PivotTable to analyze worksheet data

• Create a PivotTable to analyze external data

• Create a PivotTable to analyze data in multiple tables

• Filter data in a PivotTable

• Group or ungroup data in a PivotTable report

• Apply conditional formatting in PivotTable