Allow users to set/choose defaults (via settings) for PivotTables - Layout (compact, tabular, classic, etc.), PT Options, etc.
Excel 2007 introduced the new Compact Form and made it be the default for all future pivot tables. With this layout, multiple row fields are compressed into column 1 of the pivot table. This makes the data very difficult to later re-use.
My request is to add a setting in Excel Options where each person can choose their default pivot table layout. All future pivot tables will use Tabular, Outline, Compact, or Flat. (Flat is a new layout introduced by Power Pivot... it is Tabular with no Subtotals).
Additionally, the same settings area shouls allow the user to save other PivotTable options that she might find useful for all PivotTables (like not Autofitting columns on refresh, etc.)
Thanks for considering this idea.
(2016-02-18 Dan [MS]: edited idea a bit - broadened it to cover more than just PivotTable layouts - now also covers other PT settings/options)
On behalf of the Excel team & with great pleasure, I announce this feature has now been released to Office 365 subscription customers. More information is available at https://blogs.office.com/2017/05/15/pivottables-just-got-personal/.
Thank you to Mr. Excel for suggesting this idea and to the community for rallying on it!
Chris Alvarez commented
When will this hit DoD? Right now DoD does not use O365, so will this be a patch available to apply to Office 2016?
If you have joined the Insiders Fast program, you'll find the "Edit Default Layout" button in the File > Options > Data section.
If you still don't see the button, visit us at the Microsoft Answers community link in status above and add a comment in the Discussion. We'll work with you to troubleshoot any issues you might be running into.
Thank you for your help in trying out this new feature.
Some folks on the latest Insiders Fast build no longer have the ability to set the default. We're currently looking into this.
In the build 8001.1000 the ability to configure the pivot table by default has again disappeared. Where to look?
I totally endorse the need for sheet/workbook/global level pivot table defaults.
My particular bugbears are:
Not to default to compact layout
Preserve source data formatting on both creation and refresh
Preserve multi-line (not multi-row) column headings on creation
Paul Lucassen commented
Would be nice, currently I have added the relevant buttons to the QAT, 6 clicks and the pivot table is the way I like it, a default setting would be great.
I like flat layout too.
Yes this way only need to touch settings once, bingo
Mike Girvin commented
I just added 3 votes!!!!!
This will be a good feature go add
There should be an option in the settings where you can check a box so that your pivot tables always default to Classic Pivot Table View. Right now, you have to manually change it to this setting each time you do a pivot table.
Christine Alan commented
I ended up writing vba code and assigning it to an icon I switch to the old format so often.
Amen to this suggestion! First thing I do with EVERY pivot table I create is change the layout to tabular. Very cumbersome.
Ed Hansberry commented
This, 1,000 times this! I think every option in Pivot tables should be able to be set and saved as "my pivot table defaults."
Every table I create from an external source, I have to change it to put it in source order, not alphabetical. And Table format,.
This is what Pivot Tables need the most. We should ask for a discount on 365 subscriptions equal to the time we waste choosing the table options we want everyday.
Linda W commented
I'd like to add my input which is similar to all - classic view, subtotals off, tabular layout. I think a set as default option would be great.
Neil Rothbart commented
In full support of Classic View as the default and being able to save preferred defaults such as subtotals off, etc.
This would be awesome, it is a waste of time having to change the pivot table to classic, everytime you utilize a pivot table. Having to change the type, also adds confusion when training others to use the pivot table.
Scott S commented
I cannot agree more! You should have the option to change the default setting for the Pivot Tables. It is very tedious to change this setting every time you create a Pivot Table. I can understand why they may have wanted this introduced but making this the default is not desirable for many users.
Pat A commented
Yes, yes, yes to allowing users to set their own defaults for Pivot Tables! Personally, this would save me a little bit of time with almost every Pivot Table I build.