Grouping and Sorting

Protected: A Finance Team’s Guide to Pivot tables Grouping and Sorting

Grouping and Sorting Values

Previous Session Summary

In the previous lesson, we reviewed how to add filters into our pivot tables, but also then looked at how to add in slicers. Slicers are buttons that can be added into a worksheet that can also filter a pivot table (or multiple pivot tables). They can look a bit better as they can be formatted and they’re more intuitive for reviewers to use if they don’t know how to use Pivot Tables.

Lesson Synopsis

All the information from a Pivot Table comes from a connected Dataset and the great power of a Pivot Table is the ability to group information based on specific values in specific fields. That means that they can be great for analysis.

However sometimes, you may want to do some analysis based on some fields or values that don’t exist in your dataset. Most people generally go back to the connected dataset and add in a new column or field perhaps using an IF statement or a nested IF statement. However you can add new groups into a pivot table without having to go back to the base dataset. That can be faster, or better for those that aren’t keen on nested IFs. We also look at how you can sort data in the Pivot Tables high to low and low to high which can help with ranking.

Resource Listing

The Video

Play Video

00:20 - What is Grouping?
00:50 - How can you put in new Groups?
01:30 - What information comes up when you create groups?
02:33 - How can I move the new Groups in and out of the Quadrants?
03:25 - Why is Grouping useful?
05:47 - Do I have to keep the fields that I created the Groups from in the Pivot Table?

Lesson Notes

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.

.... and a little more!

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

Previous Lesson

Back to Course

Next Lesson