Set Default Layout for Pivot Tables

Let me summarize the problem for you. Every Pivot Table junkie would agree that it’s quite painful to reformat the pivot table, each time you create a new one. I mean it involves quite a few tasks eg.

1. Add / Remove Subtotals
2. Applying Classic or Tabular Layout
3. Don’t auto-fit the width of the column on update

And there could be many little changes to customize the layout of the pivot table your way. And you gotta do them each time, every time. Painful?.. I know

Before I tell you the solution to this, let’s say thanks to Bill Jelen aka Mr. Excel who initiated this over excel’s user voice forum to include this feature in excel and got more than 500 votes for it. (He is a prodigy)

Without further ado.. here is how it works

 

Imagine this Pivot…

This is just a regular pivot and to give it a custom look, you’ll have to format let’s say.. 3 things

1. Remove the Subtotals (from Design Tab)
2. Remove +/- Signs (from Analyse Tab)
3. Tabular or Classic Layout (again from Design Tab)

and your pivot now looks like this..

 

And to make this format style as the Default

Go to File >> Options >> Data >> Edit Default Layout

Note: You’ll only see this in case you have updated your excel

In the Default settings box you’ll see options to customize Sub Totals, Grand Totals, Layout and even Standard Pivot Table Options or you may even click on Import, this will import the all the settings of the pivot that you just formatted

And guess what.. next time you make a pivot it will be pre-formatted the way you always wanted

 

Also set default Pivot Table Style

You can set the style (look and feel) of the pivot table as a default one.

1. Just go to the Design Tab
2. Choose the Style that you want
3. Right click and Set As Default
4. Bingo!

 

More of a Video Lover? I got you covered.. Watch this 😀

Let me know in the comments, if you have other tactics to save formatting time in pivot tables

 

Some more Pivot Table tactics you’ll love

1. Pivot Table Formatting Tricks
2. 16 Pivot Table Life Saving Tricks
3. 3 Ways to Filter Pivot Tables
4. Change Pivot Table Value Field Calculation using VBA
5. Top Selling Product Analysis in Power Pivot