New in Excel 2016 for Office 365* users is the ability to set a default layout for your PivotTables. Finally!
If you’re like many PivotTable users the first thing you do after inserting a PivotTable is waste a minute or so fixing the layout so it’s just how you want. Waste no more, because now you can set the default for any new PivotTables in any workbook.
Setting a default PivotTable layout doesn’t change or update existing PivotTables though.
*If you have Excel 2016 with Office 365 but can’t see the new PivotTable Default Layout option, then you may be on the Deferred update channel. This post explains how to switch to the Current channel. And this post explains the update cycle.
How to Set Excel PivotTable Default Layout
You’ll find the settings for the default PivotTable layout in the Options: File tab > Options > Data > Edit Default Layout:
You can import a layout from an existing PivotTable; just select a cell in the PivotTable and click ‘Import’:
Or you can specify the layout for the Subtotals:
The Grand Totals:
The Report Layout:
Even the PivotTable Options:
And if you get tired of your default layout, you can restore the Excel default:
Bonus for OLAP PivotTables
Tip: a recent performance improvement to Excel 2016 means that disabling Subtotals and Grand Totals can make OLAP PivotTables faster.
Non Office 365 Users
If you don’t have the latest version of Excel then there are still some one-click shortcuts to making some of the more common changes to PivotTable layouts. Namely, removing all subtotals and reinstating the ‘classic PivotTable’ layout, which is tabular, as described in this post.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.