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!
Please also include this setting to 2016 standalone version
Thank you for making this clear. I will just have to feel Excel envy until I can get my hands on Office 2018!
Ed Hansberry commented
When you buy the perpetual license, you pretty much get what you get plus security and bug fixes. All of the new features in the Office 365 versions will be in office 2018.
That is how their subscription model works.
When will this joyous development be made available to all? I have Office Professional Plus 2016 and our IT manager says this new feature is available to Office365 users only.
When will this be updated in Excel 2016?
Marshall Dixon commented
When will this feature be available on Excel for Mac 2016?
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,.