What’s new in Excel 2019: PivotTable updates

Liam Bastick looks at the new PivotTable enhancements and updates in Office 2019.

PivotTables were enhanced beyond recognition with the advent of Excel 2010 and Excel 2013, and the introduction of Power Pivot and the Data Model, bringing the ability to easily build sophisticated models across your data, augment them with measures and KPIs and then calculate over millions of rows with high speed. But it’s not stopped there:

• Personalise the default PivotTable layout: you may now set up a PivotTable the way you like. You can choose how you want to display subtotals, grand totals and the report layout, then save it as your default. This means that the next time you create a PivotTable, you will start with that layout.

• Automatic relationship detection: this feature discovers and creates relationships among the tables used for your workbook’s data model, so you don’t have to. Excel knows when your analysis requires two or more tables to be linked together and notifies you. With one click, it does the work to build the relationships, so you can take advantage of them immediately.
• Creating, editing, and deleting custom measures: this may now be done directly from the PivotTable fields list, saving you a lot of time when you need to add additional calculations for your analysis.

• Automatic time grouping: this helps you to use time-related fields (year, quarter, month) in your PivotTable more powerfully, by auto-detecting and grouping them on your behalf. Once grouped together, simply drag the group to your PivotTable in one action and immediately begin your analysis across the different levels of time with drill-down capabilities.
• PivotChart drill-down buttons: this allows you to zoom in and out across groupings of time, and other hierarchical structures within your data.
• Search in the PivotTable: the ‘Field list’ helps you get to the fields that are important to you across your entire data set.
• Smart renaming: this gives you the ability to rename tables and columns in your workbook’s data model. With each change, Excel automatically updates any related tables, and calculations across your workbook, including all worksheets and DAX formulae. This is quite a welcome improvement as this caused many problems in previous versions of Excel.
• Multi-select Slicer: you may now select multiple items in an Excel Slicer on a touch device. This is a change from prior versions of Excel, where only one item in a Slicer could be selected at a time using touch input. You can enter Slicer multi-select mode by using the new button located in the Slicer’s label.

• Faster OLAP PivotTables: if you work with connections to online analytical processing (OLAP) servers, your PivotTables are now faster. Excel 2019 contains query and cache improvements in this powerful feature’s performance. You could benefit from this work, whether you use PivotTables to answer one-off questions, or build complicated workbooks with dozens of PivotTables. It doesn’t matter if your PivotTables are connected to a tabular or multi-dimensional model. Any PivotTable connected to Microsoft SQL Server Analysis Services, third party OLAP providers or the Power Pivot Data Model will likely give you fresh data, faster. Additionally, now if you disable Subtotals and Grand Totals, PivotTables can be much faster when refreshing, expanding, collapsing, and drilling into your data. The bigger the PivotTable, the bigger the potential improvement. Specifically, Excel 2019 offers improvements in three major areas while querying OLAP servers:
• Improved query efficiency: Excel will now query for Subtotals and Grand Totals only if they’re required to render the PivotTable results. This means you wait less for the OLAP server to finish processing the query and you wait less while waiting for the results to transfer over your network connection. You simply ‘disable Subtotals and Grand Totals’ from the ‘PivotTable Design’ tab just like you would normally.
• Reduced the number of queries: Excel is smarter when refreshing your data. Queries will now only refresh when they’ve actually changed and need to be refreshed.
• Smarter caches: when the PivotTable schema is retrieved, it is now shared across all of the PivotTables on that connection, further reducing the number of queries.

Power pivot updates

Power Pivot gets a dusting down in Excel 2019 too:

• Save relationship diagram view as picture: you may now save the data model diagram view as a high resolution image file that can then be used for sharing, printing or analysing the data model. To create the image file, in the ‘Power Pivot’ pane, click File > Save View as Picture
• Multiple usability improvements: these have also been made. For example, delayed updating allows you to perform multiple changes in Power Pivot without the need to wait until each is propagated across the workbook. The changes will be propagated at one time, once the Power Pivot window is closed.

  

• Enhanced ‘Edit Relationship’ dialog creates faster and more accurate data relationships: Power Pivot users can manually add or edit a table relationship while exploring a sample of the data, up to five rows of data in a selected table. This helps create faster and more accurate relationships, without the need to go back and forth to the data view every time you wish to create or edit a table relationship.

• Table selection using keyboard navigation: in the ‘Edit Relationship’ dialog, type the first letter of a table name to move the first column name starting with the selected letter
• Column selection using column navigation: in the ‘Edit Relationship’ dialog, type the first letter of a column name to move the first column starting with the selected letter. Retype the same letter moves to the next column starting with the selected letter
• Auto column suggestion for same column name in both tables: after selecting the first table and column, on the selection of the second table, if a column with the same name exists, it is auto-selected (works both ways)
• Fixes that improve your overall modelling user experience:
  1. The Power Pivot data model is no longer lost when working with hidden workbooks
  2. You can now upgrade an earlier workbook with a data model to Excel 2016 and later
  3. You can add a calculated column in Power Pivot, unless it contains a formula.

Get & Transform (Power Query)

Improvements have also been made to Get & Transform (also known as Power Query):

• New and improved connectors: there are now new connectors in Excel 2019. For example, there's the new SAP HANA connector. Excel 2019 has also improved many of the existing connectors so that you can import data from a variety of sources with efficiency and ease

​

• Improved transformations: in Excel 2019, Microsoft has significantly improved many of the data transformation features in the Power Query Editor. For example, splitting columns, inserting custom columns, adding columns from an example, merge and append operations, and filtering transforms have been improved/enhanced
  
• General improvements: Excel 2019 also has some general improvements across the Get & Transform area in Excel 2019. One notable improvement is the new ‘Queries & Connections’ side pane, which lets you manage queries and connections easily. There are also many improvements to the Power Query Editor as well, including “select-as-you-type” drop-down menus, date picker support for date filters and conditional columns, the ability to reorder query steps via drag-and-drop and the ability to keep the layout in Excel when refreshing.

Publish to Power BI

If you have a Power BI subscription, you can now publish files that are stored locally to Power BI. To get started, first save your file to your computer. Then click File > Publish > Publish to Power BI. After you upload, you can click the ‘Go To Power BI button’ to see the file in your web browser.

Finally, one thing to stress. Excel 2019 is what is known as a “perpetual licence”. Microsoft will update for security features and so forth but new features – described here in past newsletters – such as Dynamic Array functions will not be included down the road. It’s likely you will have to wait until Office 2022 (assuming such a thing will exist) as Microsoft tries to convert everyone to the annual subscription model.