Sort data in a PivotTable

To help you locate data that you want to analyze in a PivotTable report more easily, you can sort text entries (from A to Z or Z to A), numbers (from smallest to largest or largest to smallest), and dates and times (from oldest to newest or newest to oldest).

When you sort data in a PivotTable report, be aware of the following:

• Data such as text entries may have leading spaces that affect the sort results. For optimal sort results, you should remove any spaces before you sort the data.

• Unlike sorting data in a range of cells on a worksheet or in an Excel for Mac table, you cannot sort case-sensitive text entries.

• You cannot sort data by format, such as cell or font color, or by conditional formatting indicators, such as icon sets.

• Sort orders vary by locale setting. Make sure that you have the correct locale setting in Language and Text in System Preferences on your computer. For information about changing the locale setting, see the Mac Help system.

Do any of the following:

Sort row or column label data in a PivotTable report

1. In the PivotTable report, click any field in the column that contains the items that you want to sort.

2. On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click the sort order that you want.

   Note: You can also quickly sort data in ascending or descending order by clicking A to Z or Z to A. When you do this, text entries are sorted from A to Z or from Z to A, numbers are sorted from smallest to largest or from largest to smallest, and dates or times are sorted from oldest to newest or newest to oldest.

Customize the sort operation

1. Select a cell in the column that you want to sort by.

2. On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Custom Sort.

3. Configure the type of sort that you want.

Sort by using a custom list

You can sort in a user-defined sort order by using a custom list.

1. Select a cell in the column that you want to sort by.

2. On the Data tab, under Sort & Filter, click the arrow next to Sort, and then click Custom Sort.

3. Click Options.

4. Under First key sort order, select the custom list that you want to sort by.

   Excel provides built-in day-of-the-week and month-of-the year custom lists. You can also create your own custom list. A custom list sort order is not retained when you refresh the PivotTable report.

   Note: Custom lists are enabled by default. Disabling custom lists may improve performance when you sort large amounts of data. To disable custom lists, follow these steps:

   1. On the PivotTable tab, click Options, click PivotTable Options, and then click Layout.

   2. In the Sort area, clear the Use custom lists when sorting check box.

Sort data in the values area

1. In a PivotTable report, select a value field.

2. On the Data tab, under Sort & Filter, do one or both of the following:

   • To quickly sort in ascending or descending order, click A to Z or Z to A. Numbers are sorted smallest to largest or largest to smallest.

   • To customize the sort operation, click the arrow next to Sort, click Custom Sort, and then configure the type of sort that you want.