Sorting values in Excel Pivot Table

A pivot tables has data rows, columns, values and grand totals. Here’s how to sort the data based on the different parts.

In the example below we have a pivot table with average marks per student for 3 subjects (Science, English and Maths).

To sort data in a pivot table, click anywhere in the pivot table. Excel displays a new ribbon item called Option.  Use the sort options in there to sort the data.

The part of the pivot that gets sorted depends on where you click in the table. Here are a few options

 

1) To sort by Student (row group), simply use the filter button next to the row heading

2) To sort by a specific marks, select one of the cells that contains the actual marks and the use the sort options. N.B. Do not click on the column heading (Maths) as that produces a different result.

3) If you select one the column heading and click sort, it will rearrange the sequence of the columns in ascending order of the column names. So, the sequence Science,English,Maths changes to English, Maths,Science

4) If there is only one value column and you select the column heading, the sort option will get disabled as there is no other column to swap the value with. However, if you select the actual data in that column, it will sort the data in the column

 

5) If you select one of the grand total values and click sort, it will rearrange the sequence of the columns in ascending order of the grand total values. So, the sequence of the columns changes to Maths,Science,English based on the order of grand total 42, 51,57

Leave a Reply

Your email address will not be published. Required fields are marked *