Posted by: Yosef B. | June 19, 2017

Setting Default PivotTable settings in Excel

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.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.


%d bloggers like this: