Sort data in a PivotTable or PivotChart

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

Important: When you sort data, be aware that:

• Sort orders vary by locale setting. Make sure 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, like cell or font color, or by conditional formatting indicators, such as icon sets.

2016-2007 Excel Online



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

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.

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

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.

To see the grand totals for products sorted 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.

1. 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:

  

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

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 click the sort option you want.

If you click the Column Labels arrow, pick the field you want to sort first, followed by the sort option you want.

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

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 by a specific value

1. Click the arrow on Row Labels, and pick Sort by Value.

If you click the Column Labels arrow, pick the field you want to sort first, followed by the sort option you want.

1. In the Sort by Value box, under Select value, pick the value to sort by.

1. Under 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