Disable time grouping
The new automatic time grouping in PivotTables should be an option within Excel. Users should not need to make a registry edit to disable this feature, and the time grouping should not be on by default.
This ability to turn off time grouping from the UI was recently added to the O365 version of Excel. File→Options→Advanced→Data→“Disable automatic grouping of Data/Time columns in PivotTables”.
We’re considering adding this back to the perpetual release of Office 16. Until that’s available, you can also turn it off manually using the registry, outlined in this article: https://support.office.com/en-us/article/Turn-off-time-grouping-in-PivotTables-in-Excel-2016-for-Windows-6be5afed-348c-4db2-9f87-5ac262d67b3f.
Ashvini Sharma [MS]
I think this has to be the single stupidest thing Microsoft has ever done. I really didn't need this today.
Unhappy User commented
Wish you would hurry up and do this for Office 16. Changing things via the registry is not an option in the majority of companies (like mine) as they lock down things like registry editing. I really wish you would make new "features" like this optional until your users have a chance to provide feedback. It is really a problem when grouping is not needed.
This seems a very good idea, but clumsily implemented rolled out. It seems the principles for a breaking change like this should be:
1. Preserve the existing behavior
2. Put an easily discoverable control to show the new feature. Eg, a right-click menu item like "Group by period"
3. Make sure that configuration changes apply without a save/reopen
Thomas McCalla commented
Thanks for the new "feature". I need to arrange a pivot with actual dates as columns to track daily KPIs. The disable feature isn't available in my excel 2016 and i do not have the ability to regedit or even request that level of administrative authority. Remove this yesterday.
worked!!! thank you.
Infuriating that I have to opt out of this. Please disable by default. At bare minimum you need to notify the user of what you've done and prompt them to leave the data grouped or allow one click to ungroup when they are adding fields to their pivot table.
1) add a date field
2) Excel prompts, do you want to group by year/quarter/month/week? Options for [Yes/No] [This time/always]
Hate it. Please quit trying to 'out think' Excel users. Overall Excel is amazing, but things like this are super frustrating. It was already possible to group by date, why make it difficult to ungroup (which is truly a pain in the rear). My company 'upgraded' to Excel 2016, now my existing files make it difficult.
Yes, this is an annoying feature, and the above fix does not work for me. Most of the new "features" in Excel seem to exist solely to justify someone's job. They don't help us at all. And the support is awful.
Annoying and a waste of time. Terrible new feature.
I've changed the option in O365 as you suggested but the date fields continue to automatically group. Is there a fix that works besides editing the registry or ungrouping each date field we want to display?
This needs to be an option you can change for each pivot table. The date grouping is very annoying some
Michael Wood commented
Hate this feature and to have to go into the registry to try and turn it off is horrible. I work in a higher ed institution and need to be able to group dates into specific semesters which I can no longer do in my Pivot table. I have to create a dummy variable with a bunch of nested if statements in the raw data to use my downloaded data. What a waste of my time when it used to be so much easier.
Microsoft: Please do not add features that users cannot opt out of. Taking control from users over their models' functionality is never a good idea and cannot possibly be in the best interest of the user.
Hate this feature and especially that it's automatic and not an option.
Harry Flashman commented
I hate this "feature" too. Killed the performance of my workbook. The option to disable this does not appear to be in my registry either (but perhaps that is because I am using 64 bit Excel). I won't be able to use Excel 2016 until this feature is changed to "opt-in" rather "opt-out" by tweaking registry settings (which will be a major hassle for the majority of business users who don't have admin rights).
Kok Kiang commented
Can't find this path: File→Options→Advanced→Data→“Disable automatic grouping of Data/Time columns in PivotTables”.
I went to the path you mentioned and it's disable, what happens when on the pivot you put the date on the row field and it also adds the month like by default and a different date format like 1-Jan instead of the original source 1/1/2017 and the format date doesn't change it.
I can't see it in my Excel 2016. I'm also concerned that this feature is significantly increasing size of my data model (by around 30% -> 1.2 MB out of 4.0 MB). I think such a feature should be set to off by default, you should not be forcing the users how they want to display the data...
Thanks for jacking up my files and crashing my machine. Opt in is the way to go. This forced improvement just corrupted one of my files.
Jonathan Flint commented
Thanks, Ashvini Sharma on an answer, unfortunately this reinforces for me why I did not want to upgrade to Office 2016 to begin with. Now I get to take much longer to do standard works because I have to figure out how to do the same work with new Microsoft Software.
Warning Rant! - Microsoft when will you stop "doing us favors". To turn on and force us to have Time grouping on is very presumptuous and now forcing me to un-adopt Office 2016. I have many jobs I have to do that the customer [others in my company] need output by date and to have it broken down into Year, Qtr,..etc. is completely useless. To come here and find out I need to edit the registry on a new machine with new software on the first task because someone at Microsoft thinks they know best how to handle my data is ridiculous.