Excel 2016: PivotTable and Power Pivot changes
This is the sixth and final part of Simon Hurst’s series looking at the changes and new features in Excel 2016.
In the first part we looked at an overview of the changes and covered those that related to the Office interface. Then in Part 2 we used the tools in the new Get & Transform group of the Data ribbon to construct a list, based on merging two other lists, that would automatically reflect changes when refreshed. Part 3 looked at the Forecast Sheet feature and Part 4 covered the first three (and easiest) of the new chart types: Treemap, Sunburst and the long-awaited Waterfall chart with Part 5 covering the remaining new chart types including the new funnel chart available to Office 365 subscribers as part of the January 2016 update.
We'll complete the series by looking at the changes to PivotTables and Power Pivot.
PivotTable Fields Search
The inclusion of a search box in the PivotTable Fields pane is a small but useful improvement. Where a PivotTable is based on a table, or series of tables, containing a large number of individual fields, it can be time-consuming scrolling through the list to find the field you want to add to the PivotTable. The new search box allows you to enter any part of the name of the field that you want to find and the list of fields will be filtered accordingly:
Excel 2013 displays an alert when a relationship between tables needs to be created. The alert includes a Create button to allow the relationship to be created manually. Excel 2016 now also includes an Auto-Detect option. This examines the tables included in the PivotTable to identify the fields that link the tables and then automatically creates the appropriate relationship. You can still create the relationship manually if required:
Calculated fields can be added to 'normal' PivotTables using the PivotTable Tools, Analyze Ribbon tab, Calculations group, 'Fields, Items, & Sets' dropdown, Calculated Field option. For PivotTables based on an OLAP (Online Analytical Processing) data source such as the Excel Data Model, calculated fields are created using a different feature that allows the use of DAX (Data Analysis Expressions). In Excel 2013, these were also known, rather confusingly, as Calculated Fields, although they were accessed from the Calculations group of the PowerPivot Ribbon tab. Excel 2016 changes the name of this feature to Measures and, as well as the option in the PowerPivot Ribbon tab, also allows their creation by right-clicking a table name in the Field List pane. In addition, existing Measures are more clearly identified in the field list and can be edited by right-clicking the measure itself in the list:
Date field grouping
PivotTable fields added as row or column labels can be grouped. One of the most common reasons for grouping is to allow date fields that contain a full date to be grouped into Months, Quarters and Years, rather than showing a row or column for each day. Before Excel 2016, this grouping had to be carried out manually by right-clicking on an item in the field and choosing the Group option. In Excel 2016, date fields are grouped by Months, Quarters and Years by default 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:
Industry insightsView more
It is still possible to right-click on an individual date and use the Group or Ungroup option to change or remove the grouping.
Chart drill down
Drill down buttons are now also available within PivotCharts to allow you to drill down, or back up, to display levels of any grouped fields added as a chart category:
Table or field names can be changed in the Data Model with those changes being automatically recognised throughout the workbook, including within any DAX formulae that use those table or field names.
PowerPivot Date Table
The PowerPivot Manage window, Design Ribbon tab includes a Calendars group containing the new Date Table dropdown. This includes several commands for creating and managing date tables within the data model.
The idea of a date table is to include all possible dates from the beginning of the year containing the first date used in the data model to the end of the year containing the last date used. Using a complete date table ensures that many of the time intelligence DAX functions work correctly. This post on the Time Intelligence DAX functions explains the potential issues of just using date fields in existing tables rather than a separate, comprehensive date table. As well as quickly creating a table containing all the required dates, the New Date Table command also includes columns in the date table to facilitate reporting, such as month number and description and other different date formats:
The Save Configuration command allows you to edit or add columns to the date table and then save the new date table as the template for future tables created using the New command. The Set Default option resets the template for new tables to the default. Finally, the Update Range command shows the existing range of dates included in the table and allows you to change the Start and End dates.
The above screenshot also shows one of the cosmetic, but nonetheless useful, enhancements: the inclusion of guide lines below each set of four rows. Also, in diagram view, the type of join and 'direction' of join between fields in different tables is now shown more clearly.
New DAX features and functions
Over 20 new DAX functions have been added including several new statistical functions, financial functions and date functions including an equivalent of the top secret Excel DATEDIFF() function.
As well as the new functions, DAX formulae can now define and use variables.
Specific change for touch screens
Slicers now include a Multi-Select button that allows multiple non-contiguous items or ranges to be selected without the need to use the keyboard:
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.