MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Specify Defaults for All Future Pivot Tables

May 13, 2019 - by Bill Jelen

Excel Specify Defaults for All Future Pivot Tables. Photo Credit: André Roma at

It took me six years, but I finally convinced the Excel team that a lot of people prefer Tabular layout for pivot tables to the Compact layout that became the default layout in Excel 2007. If you have Office 365, you now have the ability to specify pivot table defaults.

Go to File, Options, Data. Click Edit Default Layout....

In Excel Options, look for the new Data category in the left navigation bar. It should be third, right after General and Formulas. If you have the category, then the first choice is Make Changes To The Default Layout of Pivot Tables.

Change the Report Layout to Show in Tabular Form and choose the checkbox Repeat All Item Labels.

The Edit Default Layout dialog offers drop-downs for Show All Subtotals At Bottom of Group, Grand Totals On for Rows and Columns, Report Layout of Show in Tabular Form. Checkboxes offer Include Filtered Items in Totals, Repeat All Row Labels, and Insert Blank Line After Each Item. However - far more choices are available if you click the PivotTable Options button located just above OK button.


There are other settings that you can specify as the default. You can either click Pivot Table Options... and specify them or find a pivot table where you‘ve already set up your favorite settings. Select one cell in that pivot table and click Import.

If you don‘t have Office 365 and don‘t have access to pivot table defaults, you can get similar functionality by buying Pivot Power Premium from Debra Dalgleish at

Title Photo: André Roma at

Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.