Excel PivotTables get better default options

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Give it a try. You can unsubscribe at any time.

It’s now possible to change the default layout for a new Excel PivotTable.

PivotTable default configuration has been on the ‘wish list’ for many years. It’s now in Excel 2016 for Windows, Office 365 customers only as part of the May 2017 update.

The changed defaults apply to all new PivotTables made on that computer.  Presumably saved in the Registry?

Change PivotTable default

To setup from an existing PivotTable, open that PivotTable first.

Then go to File | Options | Data | Make changes to the default layout of PivotTables.

Click on Edit Default Layout …

Layout Import – choose a cell in an open PivotTable then click ‘Import’.  That will copy those settings into the default layout.

SubTotals put subtotals at top or bottom or none.

Grand Totals can be on/off for either rows or columns

Report Layout Compact, Outline or Tabular.

Blank Rows can be added after each item.

PivotTable Options opens the standard option dialog.

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.