Show All

Hide All

Microsoft Excel uses the following order for ascending sort: numbers, text, logical values, error values such as #REF! and #VALUE!, and then blank cells. Descending order is the reverse, except for blank cells, which are always sorted last.

1. Click the field (field: In a PivotTable or PivotChart report, a category of data that's derived from a field in the source data. PivotTable reports have row, column, page, and data fields. PivotChart reports have series, category, page, and data fields.) with the items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) you want to sort.

   For a PivotChart report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.), click the field in the associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.). To sort items in a series field, click the corresponding column field (column field: A field that's assigned a column orientation in a PivotTable report. Items associated with a column field are displayed as column labels.). To sort items in a category field, click the corresponding row field (row field: A field that's assigned a row orientation in a PivotTable report. Items associated with a row field are displayed as row labels.).

2. Do one of the following:

   Sort the items ascending or descending

   1. On the PivotTable toolbar, click PivotTable, and then click Sort and Top 10.
   2. Under AutoSort options, click Ascending or Descending.
   3. In the Using field list, do one of the following:

      To sort the items by their labels, click the same field that you're sorting.

      To sort the items by their values in the data area (data area: The part of a PivotTable report that contains summary data. Values in each cell of the data area represent a summary of data from the source records or rows.), click the data field (data field: A field from a source list, table, or database that contains data that is summarized in a PivotTable report or PivotChart report. A data field usually contains numeric data, such as statistics or sales amounts.) that supplies the values you want to sort on.

   Note  When you refresh (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) the report or change its layout, Excel resorts the field in the order you specified.

   Sort the items by a particular data value

   1. Click the cell in the data area (data area: The part of a PivotTable report that contains summary data. Values in each cell of the data area represent a summary of data from the source records or rows.) that contains the value you want to sort by. For example, if you want to sort your products by a particular month's sales value, click the value for that month in the data area.
   2. On the Data menu, click Sort.
   3. Select any options you want, and then click OK.

   Note  You'll need to repeat the sort if you refresh (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) or update the report.

   Sort the items in a custom order

   The custom order is not retained if you refresh (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.) the report.

   1. On the Data menu, click Sort.
   2. Click Options.
   3. In the First key sort order box, click an option.

      If you don't see the option you want, you can create your own custom sort order by clicking Options on the Tools menu and using the Custom Lists tab.

   Note  You can organize items in a custom order manually by selecting and dragging them.

   Stop sorting or return the items to their original order

   1. On the PivotTable toolbar, click PivotTable, and then click Sort and Top 10.
   2. Under AutoSort options, click Manual to stop automatic sorting or Data source order to return the items to their original order.

Note  After you sort a PivotChart report or its associated PivotTable report, some chart formatting may be lost.

Tip