Microsoft’s latest update to Excel now allows you to set defaults for how you want your PivotTables formatted. As someone who builds PivotTables all the time & 98% of the time want them setup the same way – this is a huge help!
To access the default options, open Excel and go to: File > Options > Data > & click on the Edit Default Layout button.
If you have an existing PivotTable that’s setup the way you want, simply use the Layout Import option at the top of the window to select the existing PivotTable and import the options all ready set.
The options I have set are:
- Subtotals: Do Not Show Subtotals
- Grand Totals: On for Rows and Columns
- Report Layout: Show in Tabular Form
- repeat All Item Labels: Checked box
In addition, click the PivotTable Options button and you can optimize all your PivotTables moving forward (huge file size saver!).
Select the Data tab and select/unselect the following options:
- Save source data with file: Unchecked
- Enable show details: Checked
- Refresh data when opening the file: Checked
- Number of items to retain per field: Select the None option
This will force excel to refresh the PivotTable every time the file is opened but it will also not save the source data (calculations behind the PivotTable) which makes the file size much smaller & less susceptible to file corruption.
Enjoy!
~Yosef