Grouping
With Grouping, the User can define groups and put whatever criteria they like into them. We could for example group countries by continents, Employees by Entity and so on. We can then use those groupings in our pivot table.
Creating a grouping is quite straightforward, but perhaps works best when you have fewer values. To create a group, first I’ll highlight the values in the Rows or Columns cells in the pivot table that we want to group.
I’ll then click the PivotTable Analyze tab, and then in the Group Section of the ribbon, I’ll select Group Selection.
This brings up a new field in either the Rows or Columns quadrant (depending on the values that you highlighted) as well as a new group in the Rows or Columns data in the Pivot Table.
We can then change the name of the Field in the Field List and the Group in the Pivot Table. To change the name of the Group in the Pivot Table, select the title in the pivot table and then change the name in the formula bar. We can also change the name of the Field in the Field List, by clicking on the new Field in the Field List in either the Rows or Columns pane, and then changing its name in the formula bar.
We also don’t need to keep our original Pivot Table fields for these new grouping fields to work. The The new grouping that we’ve added in, while it doesn’t exist in the connected dataset, exists in the Pivot Table and can be added in or taken out as required.
Sorting
We have the option to sort the values in a Pivot Table from high to low and vice versa.
To do this, click on the column of values in the Pivot Table that you want to sort, then right click and select Sort.
From there you can select either Sort Largest to Smallest, Smallest to Largest or More Sort Options. If Largest to Smallest is selected then the selected column will rearrange its values from largest to smallest.
Let’s just point out though that only the column that is clicked on has been sorted Largest to Smallest. The other columns are not sorted largest to smallest but have to follow the same arrangement as the selected column.
More Sorting – Aside from sorting columns from high to low values and low to high values, you can also sort rows. To do this, select a cell in the row that you want to sort, then right click and select Sort. Choose More Sort Options, then from the resulting menu, you can choose to sort Right to Left or Left to Right.
You can also use the filter drop down arrows in the Pivot Table for fields that are in the Rows or Columns quadrants. Select the drop down arrow and at the top of the pop up menu, there are options to select Sort A to Z, Sort Z to A and More Sort Options