Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

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.

10 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Andy shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    completed  ·  Dan Battagin [MS XL] responded  · 

    Hi all,

    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.

    Regards
    Ashvini Sharma [MS]

    72 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • D. Owen commented  ·   ·  Flag as inappropriate

        These instructions from Dan Battagin are incorrect.
        File→Options→Advanced→Data→“Disable automatic grouping of Data/Time columns in PivotTables”
        Should be:
        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.

      • Michael commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        HATE THIS - COMPLETELY SCREWING UP MY ABILITY TO ANALYZE DATA BY CAMPAIGN DROP DATE. Effing Microsoft.

      • Anonymous commented  ·   ·  Flag as inappropriate

        Same comment as the rest, just wanted to add another voice. Feature should be an option and by default, should be off.

      • Anonymous commented  ·   ·  Flag as inappropriate

        please remove automatic grouping for mac version of Office 2016. It is very frustrating making it almost impossible to organize reports.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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.

        It's stupid.

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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.

      • Renee commented  ·   ·  Flag as inappropriate

        Turning of that feature in teh Data section DOES NOT WORK. So very annoying.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        Date grouping is a most frustrating feature in Excel 2016 - tips to turn it off do not work. You have made my job much more difficult. Yes, it is a useful feature but you are making my life difficult.

      • Anonymous commented  ·   ·  Flag as inappropriate

        another user said it best, quote below. But seriously, do you have a group of daily excel users on hand to provide input BEFORE release? This is a great example of something that really only works conceptually for non-users. 'Features' are often really bugs that 'look cool' or 'seem cool' but in practice lack common sense.

        his 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

        "...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

      • Anonymous commented  ·   ·  Flag as inappropriate

        Since my Outlook is in our company licensing, I found that to remove this I had to open a Pivot table and then go to the Analyze menu within the workbook and there is a segment called Group: click the Ungroup to remove this annoying "enhancement."

      • Anonymous commented  ·   ·  Flag as inappropriate

        I am trying to show things by week commencing the 'blah' still showing months! why on earth did you do this!

      ← Previous 1 3 4

      Feedback and Knowledge Base