MrExcel.com - Your One Stop for Excel Tips & Solutions

Set Defaults For All Future Pivot Tables

March 13, 2018 - by

Set Defaults For All Future Pivot Tables

I was horrified when I saw my first Excel 2007 pivot table. For some inconceivable reason, Excel was putting three different row fields in column A. Why would anyone think this was a good idea?

For the next 10 years, I would change every pivot table. First, go to Design, Report Layout, Show in Tabular Form. That would get each row field in to it's own column. Then, Design, Report Layout, Repeat All Item Labels to fill in the blanks shown in D8:D11.

It took five extra clicks to fix each pivot table
It took five extra clicks to fix each pivot table

From the MVP Summit in 2007, I kept asking the Excel team if they would consider a setting that would let me say all future pivot tables would be in Tabular Form. That would save me three clicks per pivot table. Finally, Ash Sharma suggested that I write up my idea at Excel.UserVoice.Com and get some votes. I came back with hundreds of votes and the feature is now in Excel.

People with Office 365 already have the feature. People with the perpetual version of 2016 will have to wait for Excel 2019 in the second half of 2018. To find the feature, go to File, Options. Look for the new Data category on the left. Choose Edit Default Layout.

The data category is new
The data category is new

There are at least three different ways to set the defaults.

  • You can use the drop-downs in the center of the dialog to say all future pivot tables are in Tabular Form and Repeat All Item Labels. That is enough to make me happy.
  • You can click PivotTable Options button and set any particular setting. If you happen to love the old drag-and-drop pivot tables, click PivotTable Options, Go to the Display tab, and choose Classic Pivot Table Layout. Or: Whatever setting in options is your favorite, you can choose it and all future pivot tables will inherit the setting.
  • The third way: Find a pivot table that is perfectly formatted. Use the Import button and point to that pivot table
Three different ways to specify defaults
Three different ways to specify defaults

This is a great improvement. If you have something driving you crazy in Excel, submit your ideas to Excel.UserVoice.com and get your friends and co-workers to vote for your idea.

Every Tuesday, I will look at one of the Office 365 features that will be debuting in Excel 2019.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"If data is the new oil, Excel is still the best refinery."

Title Photo: rawpixel.com / Unsplash