As part of this month’s update, a new Excel feature gives you the ability to personalize the default layout for your PivotTables. Enabling users to personalize the PivotTable defaults started as a feature request in our Excel UserVoice forum. Now, when you’re building complex reports or performing one-off analyses, you can quickly get started with your favorite PivotTable layout. This feature is available for Excel 2016 on Windows as part of an Office 365 subscription. If you are already an Office 365 subscriber, find out how to get the latest update.
This month, we also included a tip when using OLAP connections to make your PivotTables much faster. Read on to learn more.
Personalize your PivotTable layout
There are two ways you can adjust the layout settings for the PivotTable defaults. One way is to simply click the newly added Edit Default Layout button under the File -> Options menu to display the Edit Default Layout dialog. Here you can make changes to many of your favorite layout options. Included are all the settings in the “Layout” chunk of the PivotTable Design contextual ribbon. We also included all the settings in the PivotTable Options dialog.
You can also import a layout from a PivotTable already in your workbook and customize the layout. This is a great way to start if you have a PivotTable in your workbook that has a layout you’d like to use all the time. Simply open the Edit Default Layout dialog, click anywhere within a PivotTable in your workbook and then click the Import button.
Either way, all new PivotTables you insert will have your favorite layout!
To learn more about how you can use this new feature, visit our support page.
Tip for OLAP PivotTables
If you use OLAP connections, making a change to your default layout could make the PivotTables you create much faster! Disabling the Subtotals and Grand Totals will help you take advantage of the performance improvements delivered in a previous update to Excel 2016.
In the Edit Default Layout dialog, simply set the Subtotals option to Do Not Show Subtotals and set the Grand Totals option to Off for Rows and Columns. This tip can work alongside changes to all the other options in the Edit Default Layout dialog. So feel free to keep toggling!
Disabling Subtotals and Grand Totals can lead to faster OLAP PivotTables.
If you have any suggestions for a new feature you would like to see in Excel, head over to the UserVoice forum and become a part of the conversation!
—Alexander Lahuerta, program manager for the Excel team