The UK's Leading Professional Training & Business Solutions Provider

Email or CALL NOW on 01952 581550

Excel – Pivot Table new Set Defaults feature for advanced users

This is cool for those of you who are serious PivotTable users and want to set defaults so that you can quickly format your PivotTables as you usually do, instantly.

Every pivot table row field is now getting its own column thanks to Tabular Layout and Repeat All Item Labels being turned on by default!

This blog is for more advanced users, we offer training to create PivotTables on our Excel Ninjas course, or we do a Live Online 2 hour session if you are unable to attend our one day classroom training.

Editing Default Layouts

Clicking the newly added “Edit Default Layout” button in File > Options pops up the new dialog where you can customize your defaults.

In this new dialog, 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. Microsoft has also included all the settings in the PivotTable Options dialog.

There are two ways you can adjust the settings for the PivotTable Defaults. One way is by making changes to any of the available options within the dialog. This is a great way to start if you already have custom default layout and you’d like to make minor tweaks.

The other way to customise the defaults is to import a layout from a PivotTable already in your workbook. This is the easiest way to get started. Simply open the dialog, click anywhere within a PivotTable in your workbook, and press the ‘Import’ button.

When you’re done making your changes, press ‘OK’ to return to your workbook.

All new PivotTables you insert will have your favourite layout!


To reach the new Edit Default Layout dialog…

  1. Press ”File” in the ribbon
  2. Choose “Options” in the menu
  3. Navigate to the “Data” section
  4. Click the “Edit Default Layout…” button

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 subtotals and grand totals will help you take advantage of the performance improvements delivered in a previous update to Excel 2016.

Inside the “Edit Default Layout” dialog, set the option for Subtotals to “Do Not Show Subtotals” and the option for Grand Totals to “Off for Rows and Columns”.

This tip works 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

Watch this short ‘how to’ video below to see this in action!