Microsoft Office: 9 PivotTable enhancements in Excel 2016
Q. What new PivotTable features, if any, have been added to Excel 2016?
A. Microsoft has made several improvements to Excel 2016’s PivotTables. Among them are:
1. Automatic relationship detection: In Excel 2013, pivoting two or more sets of data was a more difficult operation because you had to use Excel’s Relationship tool to define the connecting field names between the separate database tables — a process often referred to as joining the databases. However, Excel 2016 makes this process a little easier with an Auto-Detect button that can compare multiple database tables to identify common field names, and then establish the join(s) automatically.
Note 1: For the Auto-Detect tool to work, each database table must first be converted to an Excel Table, using Excel’s Insert, Table, Create Table menu option.
Note 2: The Auto-Detect joining process works only when common field names are used across the various database tables; if the field names do not match exactly (they don’t have to have the same case to match exactly), the join needs to be created manually. For example, the two database tables shown in below each contain a column of invoice numbers, but because the column headings (field names) are different in each table (Invoice_Number versus Invoice#), the new Auto-Detect feature will not create the join.
Because the field names are not identical in this example, you would need to use Excel’s Create Relationship tool (available from the PivotTable menu) to join the separate database tables together, as pictured in the example below.
2. Creating, editing, and deleting custom measures: Excel 2016 allows you to create and edit custom measures directly from the PivotTable fields list. To do this, you need to make sure you have at least three tables added to the Excel Data Model (with the proper relationships defined, as needed), and then right-click a table in the PivotTable Fields list and select the Add Measure option, as pictured below.
This action will launch the Measure dialog box where new measure formulas can be created using the DAX formula programming language. (DAX is an abbreviation for Data Analysis Expressions, which is the native formula and query language included in Microsoft PivotTables, PowerPivot, Power BI Desktop, and SQL Server Analysis Services (SSAS) Tabular models. DAX provides new functions designed to work with relational data, such as the CUBE function.) The detailed explanation of the DAX formulas is beyond the scope of this column, but a simple example of a DAX formula, which calculates a sales-to-cost ratio by product category, might appear as shown below.
(The Measure feature was originally included in Excel 2010 but was removed from Excel 2013. Now this feature has returned.)
3. Automatic date and time grouping: Excel 2016 automatically groups your date- and time-related fields (year, quarter, month) in your PivotTable. Once the fields are grouped together, you can drag the group to your PivotTable in one action to distribute your data across the different levels of time with drilldown capabilities. (In earlier editions of Excel, PivotTables data were plotted by individual dates, and users had to specify date group settings manually using the Group tool.)
4. PivotChart drilldown buttons: Excel 2016 allows you to zoom in and out of your PivotCharts across groupings of time and other hierarchical structures within your data.
5. Search your PivotTable: A new Search field (pictured below) in the PivotTable Fields box helps you search and find specific fields across your entire data set. (This feature was included in Excel 2010, was removed from Excel 2013, and is now back.)
6. Smart rename: Excel 2016 enables you to rename tables and columns in your workbook’s data model. With each edit or change, Excel 2016 now automatically updates any related tables and calculations across your workbook, including all worksheets.
7. Defer Layout Update: Similar to setting Excel’s Workbook Calculation option to Manual (by selecting File, Options, Formula) to prevent your workbook from recalculating after each edit, a new PivotTable feature called Defer Layout Update (see the screenshot below) allows you to delay updating your PivotTable calculations while you make multiple changes to your Pivot data. Once you have completed the edits, your changes can be updated throughout the workbook by pressing the Update button, unchecking the Defer Layout Update option, or closing the PivotTable or PowerPivot window.
8. Multi-Select slicer: Excel 2016 allows you to select multiple items in an Excel slicer on a touch device, as pictured below. (Previously only one item in a slicer could be selected at a time using touch input.)
9. Get & Transform: Excel 2016 provides a new Get & Transform tool (pictured below), which is an improvement over Excel’s previous Get Data tool. This tool helps you connect to data sources, and gather and import the resulting data.
As part of the data import process, the Navigator tool (pictured below) helps you clean your data before completing the data import process. For example, this tool can help you remove columns, change data types, or merge tables.
Once you’ve created your query, you can save it and, if desired, edit it using the Query Editor tool pictured below. This tool allows you to repeat the same data queries without re-creating them from scratch.
This new tool uses technology from the Excel 2010 add-in called Power BI Desktop and enables you to sort, filter, and manipulate large volumes of data much faster than a regular Excel workbook. The functionality between the Power BI Desktop and an Excel worksheet has also been enhanced to allow users to copy and paste queries between the two platforms. In addition, administrators can now set user permissions (or credentials) at the server level, so authorized end users can access the source data more efficiently, without getting bogged down in user access permissions.
About the author
J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a conference presenter, and a JofA contributing editor.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
