Setting Default Pivot Table Options does not work (Excel 2016?)

%3CLINGO-SUB%20id%3D%22lingo-sub-323894%22%20slang%3D%22en-US%22%3ESetting%20Default%20Pivot%20Table%20Options%20does%20not%20work%20(Excel%202016%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-323894%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20recently%20discovered%20the%20new%20feature%20in%20Excel%202016%20that%20allows%20you%20to%20set%20the%20default%20options%20for%20pivot%20tables%20in%20Options%26gt%3BData%26gt%3BEdit%20Default%20Layout%26gt%3BPivotTable%20Options.%26nbsp%3B%20I%20was%20very%20excited%20because%20I%20am%20always%20changing%20the%20same%20options%20for%20every%20single%20pivot%20table%20i%20create%20(classic%20view%2C%20show%20zero%20for%20error%20or%20missing%20data%2C%20don't%20adjust%20column%20width%20etc).%26nbsp%3B%20It%20saves%20my%20settings%20in%20the%20option%20screen%20but%20when%20i%20create%20a%20new%20pivot%20table%20they%20just%20revert%20to%20the%20standard%20options.%3C%2FP%3E%3CP%3EHas%20anyone%20gotten%20this%20to%20work%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20508px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F70645i6010A8D263E2E0EB%2Fimage-dimensions%2F508x258%3Fv%3D1.0%22%20width%3D%22508%22%20height%3D%22258%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-323894%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-324536%22%20slang%3D%22en-US%22%3ERE%3A%20Setting%20Default%20Pivot%20Table%20Options%20does%20not%20work%20(Excel%202016%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-324536%22%20slang%3D%22en-US%22%3EDid%20not%20know%20you%20could%20do%20that%20either%20-%20Thank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-324521%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20Default%20Pivot%20Table%20Options%20does%20not%20work%20(Excel%202016%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-324521%22%20slang%3D%22en-US%22%3EYou%20could%20just%20right-click%20the%20button%20on%20the%20insert%20tab%20and%20select%20add%20to%20QAT.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-324377%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20Default%20Pivot%20Table%20Options%20does%20not%20work%20(Excel%202016%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-324377%22%20slang%3D%22en-US%22%3EI%20just%20did%20standard%20Insert%26gt%3BPivot%20Table%20and%20it%20DID%20work!%20is%20there%20another%20quick%20access%20button%20that%20would%20work%3F%20Small%20request%20but%20would%20be%20great%20if%20MS%20could%20fix%20that.%3CBR%20%2F%3E%3CBR%20%2F%3EAppreciate%20the%20clue%20into%20getting%20this%20working!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-324372%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20Default%20Pivot%20Table%20Options%20does%20not%20work%20(Excel%202016%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-324372%22%20slang%3D%22en-US%22%3E%3CP%3EGenerally%20I%20use%20the%20Pivot%20Table%20and%20PivotChart%20Wizard%20quick%20access%20button.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20235px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F70735i9D25C76E70246ECA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-324011%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20Default%20Pivot%20Table%20Options%20does%20not%20work%20(Excel%202016%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-324011%22%20slang%3D%22en-US%22%3EPrecisely%20how%20do%20you%20insert%20the%20Pivot%20Table%3F%20Do%20you%20use%20Insert%2C%20Pivot%20table%3F%20Is%20your%20workbook%20saved%20as%20xlsm%20or%20xlsx%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1325099%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20Default%20Pivot%20Table%20Options%20does%20not%20work%20(Excel%202016%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1325099%22%20slang%3D%22en-US%22%3EHi!%20I%20am%20still%20having%20the%20same%20issue%20where%20new%20pivot%20tables%20are%20not%20using%20the%20custom%20default%20settings%20I%20edited.%20How%20did%20you%20get%20it%20to%20work%3F%20Just%20Insert%26gt%3BPivot%20table%20isn't%20working%20for%20me.%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1325208%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20Default%20Pivot%20Table%20Options%20does%20not%20work%20(Excel%202016%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1325208%22%20slang%3D%22en-US%22%3EOkay%2C%20understandable.%20So%20how%20do%20I%20insert%20a%20%22modern%22%20pivot%20table%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1325360%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20Default%20Pivot%20Table%20Options%20does%20not%20work%20(Excel%202016%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1325360%22%20slang%3D%22en-US%22%3EIt%20should%20work%20on%20pivots%20inserted%20using%20the%20button%20on%20the%20Insert%20tab%20of%20the%20ribbon%20as%20far%20as%20I%20know.%20Check%20out%20Excel%20Options%2C%20is%20there%20a%20Data%20tab%20there%20which%20has%20a%20button%20to%20edit%20the%20default%3F%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello All,

 

I recently discovered the new feature in Excel 2016 that allows you to set the default options for pivot tables in Options>Data>Edit Default Layout>PivotTable Options.  I was very excited because I am always changing the same options for every single pivot table i create (classic view, show zero for error or missing data, don't adjust column width etc).  It saves my settings in the option screen but when i create a new pivot table they just revert to the standard options.

Has anyone gotten this to work?

image.png

15 Replies
Highlighted
Precisely how do you insert the Pivot Table? Do you use Insert, Pivot table? Is your workbook saved as xlsm or xlsx?
Highlighted

Generally I use the Pivot Table and PivotChart Wizard quick access button.

 

image.png

Highlighted
I just did standard Insert>Pivot Table and it DID work! is there another quick access button that would work? Small request but would be great if MS could fix that.

Appreciate the clue into getting this working!!
Highlighted
You could just right-click the button on the insert tab and select add to QAT.
Highlighted
Did not know you could do that either - Thank you!
Highlighted
Hi! I am still having the same issue where new pivot tables are not using the custom default settings I edited. How did you get it to work? Just Insert>Pivot table isn't working for me.
Highlighted
The default only works for "modern" pivot tables, it does not work for "old style" pivots inserted using the old school pivot table and pivot chart wizard
Highlighted
Okay, understandable. So how do I insert a "modern" pivot table?
Highlighted
It should work on pivots inserted using the button on the Insert tab of the ribbon as far as I know. Check out Excel Options, is there a Data tab there which has a button to edit the default?
Highlighted
Yes. I was able to complete all of the steps to edit the default layout of the pivot tables.

I cannot figure out why it won't work. The videos I watched showing me how to edit the default layout don't show this problem at all and I followed them step by step.
Highlighted
Highlighted

@Jan Karel Pieterse 

I have a similar issue, and is even weirder in my case.

If I insert a pivot using Insert-->Pivot Table, it works

But if I do Alt+D+P (shortcut for insert pivot) and then Alt+F (which is Finish) then it still uses the old layout.

I normally prefer using keyboard shortcuts and not clicking.

 

Any solution to this?

Highlighted
Alt+D, P is the keyboard shortcut for the old Pivottable Wizard I'm afraid, you cannot change that. Alt+N, V is the short-cut key for the new one. You can also add the insert Pivottable button to your QAT. Then it is alt + the-number-where-it-is-positioned-on-your-QAT.
Highlighted

@Jan Karel Pieterse 

You are right, that worked.

Thanks a lot!

Highlighted

@Jan Karel Pieterse 

My understanding is that

- the only difference, except cosmetic, between old Alt+D+P wizard and new one is that with new wizard we could add or not data to data model (by default it is added) and old wizard doesn't have such option at all, data never added to the data model;

 

- If do not add to data model, dates are automatically grouped or not depends on setting,

- starting from 2016 if that setting was on, dates hierarchy automatically was built in data model for the table, if only no one else table in data model was marked as Date Table.

 

- currently automagical time grouping doesn't work for data model, only for separate from it PivotTable.