Show All

Hide All

You can sort records in ascending or descending order in a PivotTable view (PivotTable view: A view that summarizes and analyzes data in a datasheet or form. You can use different levels of detail or organize data by dragging the fields and items or by showing and hiding items in the drop-down lists for the fields.) and a PivotChart view (PivotChart view: A view that shows a graphical analysis of data in a datasheet or form. You can see different levels of detail or specify the layout by dragging fields and items or by showing and hiding items in the drop-down lists for the fields.). In addition, you can specify a custom sort order in a PivotTable view.

Sort in ascending or descending order

As an example, you can sort a sales column in ascending order or an employee name column in descending order.

You can also sort data separately for each column. The order in which you sort the columns determines how the data will be organized.

In this example, data is organized by salesperson. The Product column was sorted first so that the products would be displayed in alphabetical order; the Salesperson column was sorted second.

When sorting multiple columns, first determine the order in which you want to sort the columns, and then work backward, sorting the outermost column last. For example, if you're sorting two columns, determine which is the outer column and which is the inner column. Sort the inner column first, and then sort the outer column. To list the products in alphabetical order for each salesperson, you would first sort the inner Product column and then sort the outer Salesperson column.

In a PivotChart view, you can sort numerical data or alphabetical data in ascending or descending order. For example, you can sort a series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern. You can plot one or more data series in a chart.) that contains numerical sales data to show the highest to lowest sales amounts, or you can sort a category field (category field: A field that is displayed in the category area of PivotChart view. Items in a category field appear as labels on the category axis.) that contains company names to display in ascending order (A–Z).

In the following example, the series field (series field: A field that is displayed in the series area of a chart and that contains series items. A series is a group of related data points.), which consists of sales amounts, is sorted in ascending order to show lowest to highest sales. If the category field had been sorted in ascending order, the salespeople's names would instead be displayed on the category axis in alphabetical order.

If you have a stacked bar, area, or column chart, you can sort numerically based on the height of the entire stacked value, rather than on just one series.

1. Open a datasheet or form in PivotTable view or PivotChart view.
2. Do one of the following:

   In PivotTable view

   • To sort the data in a field based on the item (item: A unique element of data within a field. When a lower level of items is available for display in a PivotTable list or the field list, an expand indicator (+) appears beside the item.) labels, click the label of the field that contains the data you want to sort.

   • To sort the data in a field based on the summary values in a column, click a cell in the column that contains the values that you want to sort on.

   • To sort the data in a field based on subtotal or grand total values, click a subtotal or grand total value for the field that contains the data you want to sort.

   In PivotChart view

   • To sort numeric data in a series, click the series (data series: Related data points that are plotted in a chart. Each data series in a chart has a unique color or pattern. You can plot one or more data series in a chart.) or a series point (series point: An individual data value that is plotted in a chart and represented by a column, bar, line, pie or doughnut slice, or other type of data marker.) within the series you want to sort.
   • To sort items in a field alphabetically, click the category field (category field: A field that is displayed in the category area of PivotChart view. Items in a category field appear as labels on the category axis.) or series field (series field: A field that is displayed in the series area of a chart and that contains series items. A series is a group of related data points.), or click a category axis label.

   ---

3. On the PivotTable or PivotChart toolbar, click Sort Ascending or Sort Descending .

Notes

• To sort on multiple fields, start by sorting the innermost field first and work backward, sorting the outermost field last.

Specify a custom sort order in PivotTable view

You can define a custom order for a row (row field: A field in the row area of PivotTable view. Items in row fields are listed down the left side of the view. Inner row fields are closest to the detail area; outer row fields are to the left of the inner row fields.) or column field (column field: A field in the column area of PivotTable view. Items in column fields are listed across the top of a PivotTable list. Inner column fields are closest to the detail area; outer column fields are above the inner column fields.) when sorting in ascending or descending order does not meet your requirements. For example, you might want to show the values in the Title column ordered on the basis of the seniority of the title. If the column had three values — Vice President, General Manager, and Manager — sorting in ascending or descending order would not show the data in the order you want. In this case, you might want to explicitly define the order of the items for the Title column.

If items are added to a field after you define a custom sort order, the new items will appear at the bottom of the field until you rearrange them.

1. Open a datasheet or form in PivotTable view.
2. Select the item you want to move.
3. Drag the selected item and drop it above the item you want it to precede.
4. Repeat steps 2 and 3 until you have the items in the order you want.

Notes

• To switch from ascending or descending sort to custom sort, click Sort Ascending or Sort Descending on the PivotTable toolbar so that the button is not selected.
• If a field with custom sorting has a parent field, each parent member will display the lower-level members in custom order.

Note  The order in which numbers, text, and special characters are sorted depends on the source data and your regional settings in Control Panel.