How to Change Pivot Defaults in Excel

Yes. This long-awaited feature is finally here with Office 365 Pro Plus (May 17). This is a short article showing the steps. Detailed video coming soon. Ability to Change Pivot defaults is very useful. You no longer need to customize each Pivot manually.
Change Pivot defaults dialog
You many not see this feature for a couple of months depending upon whether you get immediate or delayed updates. This is decided by the IT team usually.

How to Change Pivot defaults

A “save as template” would have been a simpler approach. However, it has been implemented differently. Open File menu – Options. Choose the Data tab. It has many options related to Pivot table performance. For now, click on Edit Default Layout.

Two ways to use this dialog. If you already have a pivot table which is customized to your requirements, open the pivot, click anywhere inside it and from this dialog, choose Import and click Ok. That’s it. From now onwards, all pivots across all files will look like this pivot.

Another method is to customize the options given below manually. The dialog shows the more commonly used options. However, ALL options are available when you click PivotTable Options button. Customize these as required and click Ok.

Needless to say, there can be only ONE default active at a time. If you need lots of variations of Pivots, create a dummy file containing all the variations, open it on demand and Import to Change Pivot defaults.

Join my Udemy course on Pivot Tables to master pivot tables.

The detailed video about this feature is available as a free preview on the Udemy course. You can also view the video on this blog post.

Queries | Comments | Suggestions | Wish list