EZ guide to PivotTables – groupingby
The first four parts of this series demonstrated the point of PivotTables and showed how to use PivotTables with Slicers as the basis of creating an interactive dashboard. We will now look at a few specific techniques that can be useful when working with PivotTables.
We are continuing our analysis of house sales values in England, Scotland and Wales using publicly available data.
We are using the first CSV (comma separated value) file in the list - Average price.csv - and have downloaded it straight into an Excel workbook before turning it into the PivotTables we have created in the previous parts of the series.
Grouping by date
We did see an example of grouping in Part two of the series when we added a date field to our PivotTable.
In Excel 2016 date fields are grouped automatically when they are added to a PivotTable area. For a date field, the groupings are fairly obvious: Days, Months, Quarters and Years. For more immediate data, fields with a time element can be grouped by Second, Minute and Hour. To group a Date/Time field from scratch or change the groupings that have been applied automatically, you can right-click on any of the items in the field and choose Group.
If Excel displays a message saying, 'cannot group that selection' it is usually a sign that the field contains at least one value that Excel does not recognise as a date.
You might have noticed that the available groupings omit weeks. If you want to group your data by week, you need to choose the Days grouping and then set the ‘Number of days:’ value to 7:
By default, the first period of seven days will start at the date of the earliest item in the field. Of course, 1/1/1995 was a Sunday, so if we wanted each of our weeks to start on a Monday we would have to change the ‘Starting at:’ date to the previous Monday: 26/12/1994:
Grouping by value
It is not only Date and Time fields that can be grouped. It might at first seem a strange thing to do, but we could add our Average_Price field as a row label. This will list all the different average sales values in our data set. Just as for a date, right-click on any of our values in the row area and choose to Group. In this case we have set a grouping level of 100,000 and set our starting value to 0. We have also changed the way we summarise our Average-Prices so we can see the overall value of sales within each value grouping:
We've seen that fields that contain date values or 'normal' values can be grouped by a level or numeric interval. Text fields cannot be grouped in the same way but, if you select more than one individual item by using Control+Click or Shift+Click, you can then right-click on any of the selected items and choose Group.
This will create a new group that will be called Group1 by default. Here we have found and selected East Sussex, Mid-Sussex and West Sussex and then used right-click, Group to create our new Group1 containing just those areas:
We could click on Group1 and change the text to something more descriptive such as Sussex. Here we have dragged our new Sussex group to the top of our list and then used the PivotTable Tools, Design Ribbon tab, Report Layout option to change to Tabular layout. We can now see the headings for our existing Region_Name field and the new field that our additional grouping has created. By default, this will be named Region_Name2 but, just like our Group1 heading, we can click on it to rename it, in this case to Area:
Once you have grouped some fields, you need to be careful how you group others. To group other regions in the Area group we need to select them in the Region_Name column. So if we wanted to add Adur to our Sussex group we would select from our Region_Name column each of our existing Sussex regions individually and then Adur, all while holding down the Control key.
Having selected all four, we right click on any one of them and choose Group. This would just add Adur to our existing Sussex grouping.
Were we instead to select Sussex and Adur from the Area column, and then right-click and Group, we would end up with an additional grouping level:
Although creating ad-hoc groupings in this way can sometimes be useful, wherever possible it's better to ensure the appropriate groupings exist in the underlying data. This avoids the need to create the groupings manually in the first place, as well as making it easier to deal with changes and updates.
Layouts and outlines
When the row or column area contains grouped items, small expand/collapse icons will appear beside upper level group items. Clicking on the buttons individually will show or hide the detail of the next grouping level down.
In the example below, having previously changed from the Compact to Tabular layout, we have used the third of the three layouts: Outline. The PivotTable Tools, Analyse Ribbon tab, Active Field group includes Expand Field and Collapse Field buttons that will expand or collapse all the items in the active field. Here, we have selected any cell in the Years column to make Years the Active Field and then clicked the Collapse Field button to collapse all the levels below Years in one go:
As well as in PivotTables, expand and collapse buttons have also been included in PivotCharts since Excel 2016. These enable the level of detail displayed in a chart to be controlled directly from the chart with each click of the plus/minus button displaying/hiding the next level of grouping detail:
We will continue our examination of different PivotTable features and techniques by considering the different ways that we can calculate the figures shown in the Values areas, from just showing the sum of the individual items to working with percentages, statistical measures and running totals.