Set Defaults For All Future Pivot Tables
March 13, 2018 - by Bill Jelen
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.
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.
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
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