A recent Any Answers question helped highlight a significant change in the way that dates are treated in PivotTables.
The last episode of our series on the Excel 2016 changes showed how dates are now grouped by default when added to PivotTables and covered the use of the right-click menu Group/Ungroup options to wrest control of grouping back from the new default. After a quick revisit to the relevant section of the Excel 2016 changes article, we'll look in more detail at what else you can do with groups in PivotTables and, in particular, consider how basing a PivotTable on different forms of data can affect the grouping options available.
In Excel 2016, date fields are grouped by default into Months, Quarters and Years when they are added to the PivotTable. The top level grouping is displayed together with drill down buttons allowing you to expand the different date levels:
It is still possible to right-click on an individual date and use the Group or Ungroup option to change the grouping periods or to remove the grouping altogether.
How different types of PivotTables affect grouping options
This isn't the only recent change in PivotTables and grouping. PivotTables are not all created equal. Those created directly from data ranges, tables and external data differ from those based on OLAP (OnLine Analytical Processing) cubes.
As well as specific OLAP data sources, 'standard' data could be converted into an OLAP cube by using the Power Pivot Add-in and, from Excel 2013 onwards, by adding data to the Workbook Data Model.
Starting with a Table of data in Excel, choosing to Insert a PivotTable or using the Table Tools, Design, Summarize with PivotTable option, allows you to choose whether to add the data to the Data Model at the same time. If you do so, the resulting PivotTable will be based on an OLAP cube and the PivotTable Tools, Analyze Ribbon tab, Calculations group will include an active, rather than greyed-out, OLAP Tools command:
Although OLAP-based PivotTables include a range of features additional to those available in a standard PivotTable, such as the ability to convert the PivotTable to a set of individual formula cells, there are some things that you can't do with them.
Until Excel 2016, one of those missing functions was the ability to group PivotTable fields. Excel 2016 now allows date fields to be grouped in both types of PivotTable, but other types of field, which standard PivotTables allow to be grouped, can still not be grouped in OLAP-based PivotTables.
Here we have added a field containing a value as a column field to a standard PivotTable. We can right-click on any item in the field and choose to Group:
As this is a number field, we can choose to group by a value – for example every 10,000. The Starting at: and Ending at: values will default to the lowest and highest values in the field respectively, but one or both of these can be changed to create more appropriate groups. In this example we have set the Starting at: to 0 rather than leaving it at the default of 4.8. Our data is grouped accordingly, in our case showing us that we have no invoices in the range 20,000 to 30,000, and a single invoice for 40,000:
Although text fields can't be grouped using the right-click, Group option in either type of PivotTable, in standard PivotTables text fields can be grouped manually.
In this example, we want to group our Salesperson field by region. We can select the Salesperson items that belong to a particular region and right-click any one of them and choose Group:
Here we have selected Andrew Fuller, Anne Dodsworth and Laura Callahan. When we click Group we can see our three items forming part of Group1, with each of the other items being in their own groups:
We have completed our manual grouping by selecting each of the other Salesperson items (being careful to select the items themselves, not the group headings). We have also overtyped the resulting Group1 and Group2 headings with North and South:
The new group field can be moved to any of the PivotTable areas in the same way as any other field.
Although this method can be useful, it isn't that straightforward to add new items in the future and it can't be used with an OLAP PivotTable. Ideally, the required grouping would exist in the underlying data but, if not, an Excel Table could be set up to hold the grouping and incorporated into the Workbook Data Model:
We have set up an Excel Table that includes all of the items in the Salesperson field and added a column to hold the Region information. From Excel 2013 onward, the Data Ribbon tab, Data Tools group includes a Relationships command. This allows relationships to be created between Tables that form part of the Workbook Data Model. If we Insert a PivotTable we can choose to 'Use an external data source' and 'Choose Connection…'. The Tables tab then allows us to choose 'This Workbook Data Model':
Our Tables will then be available to our new PivotTable and, because we have established the relationship between them, we can include our Region field with our Invoice fields:
Grouping by week
To finish, we'll return to grouping by date and look at grouping by week. If we look at the options available when we right-click on a date and choose Group we can see Year, Quarter, Month and Day (as well as Hours, Minutes and Seconds). There is no 'weeks' option. However, a week is just a group of 7 days, so, if you are using a standard PivotTable (OLAP PivotTables do not include the ability to change the number of days in the grouping dialog) and need to group into weeks you can choose to group by days and set the Number of Days to 7:
21/04/2010 is, of course, a Wednesday, so you might need to change the Starting at: date to the first day of that week to make sure that all your week groups cover the required dates.
AccountingWEB spoke to Microsoft's head of technology James Akrigg at Accountex this month about the future of Excel:
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.