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.
I have no idea if this feedback is listened to by MS but this "new" feature was apparently added as the default without any input from actual users. There has not been a time when I wanted anything other than the full date. I hope this gets resolved, but maybe this is just one of life's mysteries that we have to put up with.
I was struggling with this date grouping logic. I went to settings and checked the disable automatic grouping but dates were still in monthly frequency. You have to further right click and ungroup to get data back to what 2010 and previous versions had it. Tad bit annoying to introduce the feature like this. Why not default to how it was and somebody wanted twisted grouping then they can go and say "Enable twisted date grouping"
Satheesh Kumar commented
It does not work.
Even after disabling the option File→Options→Advanced→Data→“Disable automatic grouping of Data/Time columns in PivotTables it does not work. I do not know why MS introduces such kind of grouping and make the user to disable it. Rather it should be other way around.
This "feature" is a waste of time. I wouldn't mind it if it wasn't automatic, but I haven't a clue why Microsoft would automate something that many people don't need or want. Also, despite my many years of experience using Excel, I sometimes can't figure out how to upgroup dates to get what I need. It's crazy when what I need is in the original data set. I beg you to make this feature optional rather than automatic.
D. Owen commented
These instructions from Dan Battagin are incorrect.
File→Options→Advanced→Data→“Disable automatic grouping of Data/Time columns in PivotTables”
File→Options→Data→“Disable automatic grouping of Data/Time columns in PivotTables”
Holy moly do I ever dislike this cantakerous & mystifying feature. It is uncontrollable, makes bad choices, screws up my pivots, ignores the Number Format of the date field.
Should be always disabled.
Should be a way to choose Date/Time components for pivot tables WHILE DESIGNING THE PIVOT TABLE. *breathe* OK. Thanks. Please consider losing this irritating "feature" ASAP.
When would Microsoft start understanding user's needs instead of coming up with their own half-baked solutions? " it still sorts them by month when I need individual dates"
Ben Smith commented
This fix does not work for me. It stops the year/quarter groupings, but it still sorts them by month when I need individual dates
Alex Campbell commented
It is actually File→Options→Data→Data options
“Disable automatic grouping of Data/Time columns in PivotTables”.
I like other people found the feature absolutely useless and counter-productive.
John Brooking commented
Thanks for the discussion. I agree with the other comments that this is a pain in the butt, and it's frustrating that such a minor thing has so greatly inhibited my productivity this morning.
Here's a secondary issue I discovered and fixed, which I'll mention here in case others have the same problem. Even after I applied the registry fix (and then discovered I also had the UI option), my data column was still being grouped to the month level, even though it was no longer grouping to the quarter and year. This was happening even after I totally deleted and re-created the pivot table. To get the pivot column back to the day level, I had to completely ungroup it by right-clicking and choosing "Ungroup" from the context menu.
this worked, thanks
[Deleted User] commented
Anonymous of January 11.
HATE THIS - COMPLETELY SCREWING UP MY ABILITY TO ANALYZE DATA BY CAMPAIGN DROP DATE. Effing Microsoft.
Same comment as the rest, just wanted to add another voice. Feature should be an option and by default, should be off.
please remove automatic grouping for mac version of Office 2016. It is very frustrating making it almost impossible to organize reports.
The date automating in excel is awful and entirely useless. An option to turn it off should have been made available a decade or two ago.
If most excel users are like me, more than 99% of cells used aren't denoting dates. To turn "12-13" into "December 13" is not helpful. If i need to write a date, i'll write a date.
Yes, yes you can preemptively set a cell to text instead of general to avoid the auto-format, but why should you have to take unnecessary steps to avoid an unnecessary auto-format?!?
If you work with any sort of coded list, (such as BLS OES occupational codes) it's a nightmare when you have hundreds or thousands of rows imported in a ##-#### format and any code with the first two digits <13 get converted into dates.
If there's any good argument for keeping the date auto-format, I'd love to hear it, because in my experience discussing this stupid topic everyone has agreed that it's an inefficient pain. Get rid of it.
Save it as an excel workbook, not a CSV. That worked for me.
If this feature is turned off it would be greatly appreciated. For one who need grouping, it is easy to group but for all other it is a nightmare.
I would have appreciated the _option_ to do this, as it can be an useful feature. To absolutely force it by default was an asinine decision. This isn't the kind of thing that needed forced adoption.
Turning of that feature in teh Data section DOES NOT WORK. So very annoying.
I can't believe anyone thought this was a good idea to group months of different years together. I have a table with dates in one column, category in another, and balance in the last one. I want to plot a different line for each category, but with the actual date value on the X axis. Why is this so hard for Microsoft to figure out. Even Power-BI falls on its face trying to do this.