Jan 22 2019 11:01 AM
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?
Jan 22 2019 01:31 PM
Jan 23 2019 07:33 AM
Generally I use the Pivot Table and PivotChart Wizard quick access button.
Jan 23 2019 07:35 AM
Jan 23 2019 11:02 AM
Jan 23 2019 11:26 AM
Apr 21 2020 07:42 AM
Apr 21 2020 08:03 AM
Apr 21 2020 08:16 AM
Apr 21 2020 09:03 AM
Apr 21 2020 10:38 AM
Apr 21 2020 11:21 AM
Jul 16 2020 04:47 AM
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?
Jul 17 2020 07:49 AM
Jul 19 2020 11:17 PM
Jul 20 2020 05:40 AM
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.
Sep 14 2023 03:10 PM
THANK YOU THANK YOU THANK YOU for this thread! I've been exceedingly frustrated that this support page doesn't seem to work:
But then I found this lovely thread that pointed me in the right direction, either by clicking through the Insert ribbon or ALT+N, V, and discovered that the default layout DOES work!
Thanks again, @Jan Karel Pieterse!!!
Feb 07 2024 12:01 PM
I am still having this issue, despite using the "insert ->pivot table" option. It used to work and then stopped. No idea what's going on, but it sucks to have to create number formatting every single time. Anyone have any tips/tricks for getting this to work in Excel 365?