Sort data in a pivot table
The following sorting options are available for pivot tables:
–By row and column names
–By row and column totals
–By values in the selected row and column
The order of sorting by name depends on the type of data in the rows or columns, similar to sorting by values in regular ranges.
You can sort by name in a separate window or by using the context menu of a cell in the pivot table.
To sort a row or column by name using the Sort and filter window, follow these steps:
1.Click the 
/ 
Sort and filter button in the row or column heading.
2.If you are using the Compact layout for the pivot table and have added multiple fields to the row or column area, in the Sort and filter window, in the Row / Column drop-down list, select the required row or column name.
3.If you have added fields to the Values area on the Constructor tab of the pivot table options pane, in the Field to sort by drop-down list, select the name of the row or column you want to sort by.
4.In the Sort section, select the sort order: Ascending or Descending.
5.Click Apply.
The appearance of the Sort and filter button may differ from the one shown here, as it depends on the sort order (ascending or descending) and whether a filter is applied to a row or column:
–: Sorted in ascending order with no filter applied.
–: Sorted in descending order with no filter applied.
–
: Sorted in ascending order with filter applied.
–
: Sorted in descending order with filter applied.
If you're using the Compact layout for the pivot table and you've added multiple fields to the row or column area, the appearance of the button will match the sort order of the top field in the list. If the upper field does not support sorting, the button view will correspond to the sorting order of the lower field that supports sorting.
If you hover the cursor over the Sort and filter button, a tooltip with the current sorting option and applied filters will be displayed.
To sort a row or column by name using the cell context menu, follow the steps below:
1.Open the context menu by right-clicking:
•The cell with the row name
•The cell in the column with parent row name (for Tabular and Outline layouts)
•The cell with the column name
•The cell in the row with the parent column name (for Tabular and Outline layouts)
2.In the context menu, select Sort ascending / Sort descending > <Row or column name>.
You can sort data by totals in a separate window or by using the context menu of a cell in the pivot table.
To sort a row or column by totals using the Sort and filter window, do the following:
1.Click the / Sort and filter button in the row or column heading.
2.If you are using the Compact layout for the pivot table and have added multiple fields to the row or column area, in the Sort and filter window, in the Row / Column drop-down list, select the required row or column name.
3.In the Field to sort by drop-down list, select a field from the Values area.
4.In the Sort section, select the sort order: Ascending or Descending.
5.Click Apply.
To sort a row or column by totals using the cell context menu, follow these steps:
1.Open the context menu by right-clicking:
•The cell with the row name
•The cell in the column with parent row name (for Tabular and Outline layouts)
•The cell with the column name
•The cell in the row with the parent column name (for Tabular and Outline layouts)
2.In the context menu, select Sort ascending / Sort descending > <Field name from value area>.
Sort by totals is performed even if you have disabled the display of totals in the pivot table settings.
When you remove a field from the Values area, the sorting of the rows and columns that used that field will change to sorting by name in ascending order. Changing the order of fields in the Values area does not affect sorting by value.
The additional calculations do not affect the sorting of rows and columns by totals. At the same time, sorting rows and columns by value affects the result of additional calculations depending on the order of elements: “Difference”, “Difference in %”.
Sort by values in the selected row or column
This type of sorting can only be performed by using the context menu of a cell in the values area of the pivot table. The row and column for sorting are determined by the cell for which the context menu was invoked.
To sort by the values in the selected row or column, follow these steps:
1.Open the context menu by right-clicking a cell in the values area.
2.In the context menu, select Sort ascending / Sort descending > Top to bottom / Left to right. If you select the Top to bottom option, the rows in the selected column are sorted by values, if you select the Left to right option, the columns in the selected row are sorted.
It is not possible to set sorting by value in the selected row or column from the Sort and filter window. However, if such sorting is already applied, the user can reset it directly from the window, using the Reset button in the tooltip.
The tooltip is displayed for the row or column to which this sorting is applied. For example, if sorting by name is applied to the “Manager” row and sorting by value is applied to the “Category” row, this tooltip will be displayed when you switch to the ‘Category’ row, but will not be displayed on the “Manager” row.
