PivotTables Just Got Personal! Now available to Insiders Slow

Alexander L [MSFT] started on

Update 5/30/17: Thanks for your feedback, Insiders! We rolled this feature out to everyone with Office 365 subscription on Excel 2016, available with latest build 8067.2115. 


For users who are part of the Insiders Slow program, there is new feature in Excel that gives you the ability to personalize the default layout for your PivotTables. Now, when you're building complex reports or performing one-off analyses, you can quickly get started with your favorite PivotTable layout.

Jump in and start using the feature! Post any feedback you have in the discussion space below. 

This feature is available to Excel 2016 users on Windows as part of an Office 365 subscription, in builds 16.0.8067.2018 or later. If you are already an Office 365 subscriber, find out how to get the latest update. You'll also need to join the Insiders Slow program. If you're not yet an Insider, we'd love to have you as part of the community. Users on a commercial SKU of Office, follow the instructions located here. If you're on a Consumer SKU, and are not yet an Insider, click File> Account> Office Insider> Join Office Insider.

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. We've 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 customize 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 favorite layout!

More Information

Enabling users to have personalized PivotTable defaults started as a feature requested in our Excel User Voice forum by MVP Bill Jelen. It quickly grew to be our second-most voted item and is still getting votes and comments. If you have any suggestions for a new feature you would like to see in Excel, head over to the User Voice forum  and become a part of the conversation!

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 feature in action

8 people have recommended this discussion

Abuse history