How to Prevent Grouped Dates in Excel

Prevent Grouped Dates in Excel

As a teenager, group dates can be fun. If you have strict parents, that might be the only kind of dates they allow! But, in your Excel worksheets, it can be annoying when dates are automatically grouped. This happens in AutoFilters, and in pivot tables too (in newer versions). Here’s how you can undo or prevent grouped dates in Excel AutoFilters and Pivot Tables.

AutoFilter Grouped Dates

By default, when you turn on an AutoFilter, the dates are grouped by year and month in the drop down list. This screen shot shows an example.

groupdatesoption02

Prevent Grouped Dates in AutoFilter

If you want to see the individual dates, you can change one of the Excel options. This is a workbook level setting, so it will affect all the AutoFilters in the active workbook.

This video shows the steps, and the written instruction are below the video.

How to Change the Setting

Here are the steps for changing the Group Dates option:

  • On the Ribbon, click the File tab, then click Options
  • Click the Advanced category
  • Scroll down to the Display Options for This Workbook section
  • Remove the check mark from Group Dates in the AutoFilter menu.
  • Click OK to apply the setting change.

prevent grouped dates in Excel

Use a Macro

If ungrouping dates is something that you do frequently, you can use a macro to turn the grouping option on or off.

Grouped Dates in Pivot Table

Starting in Excel 2016, dates are automatically grouped in a pivot table. It’s not a bug, it’s an enhancement!

Here’s a pivot table in which I added the Order Date field to the Rows area. Extra columns were created, to show the Years and Quarters, as well as the Order Date.

pivotdategrouping01

Remove the Pivot Table Date Grouping

To get rid of the date grouping in a pivot table,

  • press Ctrl+Z, immediately after adding the date field.
  • or, right-click on one of the date fields, and click Ungroup.

pivotdategrouping04

That removes the extra columns, and just leaves the original date field.

pivotdategrouping02

Prevent Pivot Table Date Grouping

If you’re using Excel 2019 or Excel for Office 365, there’s good news. You don’t have to use workarounds or make changes to the registry. Now, you can simply change one of the Excel options, to stop pivot table date grouping. Thanks to Sue, who pointed out this option in the comments below.

NOTE: This is an application-level setting, not a setting for individual workbooks or pivot tables.

To turn this setting  off:

  • At the top of Excel, click the File tab
  • Click Options, then click the Data category
  • In the “Data options” section, add a check mark to “Disable automatic grouping of Date/Time columns in PivotTables”
  • Click OK to close Excel Options

pivotgroupdatesoption01

Avoid Date Grouping in Excel 2016

If you’re still using Excel 2016, that option setting to disable automatic is not available. Here’s a workaround that I used in Excel 2016.

When I dropped a date field into the Filters area, I noticed that the individual dates were listed, instead of grouped dates.

pivotdategrouping03

Then, if I moved that date field into the Rows area, Excel didn’t automatically group the dates – they still showed as individual dates.

So, you could use that 2-step process to add a date to the pivot table, to avoid the automatic date grouping, until you get Excel 2019 or Excel for Office 365.

__________________________

16 thoughts on “How to Prevent Grouped Dates in Excel”

    1. New Info…. … happy to say since this article was written excel added a feature to turn off the automatic grouping at the program level, not just the workbook. So you don’t have to use the registry method.
      File→Options→Advanced→Data→“Disable automatic grouping of Data/Time columns in PivotTables”

      1. This didn’t quite work for me. But as a workaround I added a new column in my data to reference the date but reformatted as “General”. That new column was used to structure the pivot table, then the text in the PivotTable reformatted back to “Date”.

      2. Yay! Thanks Sue! Here in March 2020 my Office 365 path is File→Options→Data options→“Disable automatic grouping of Data/Time columns in PivotTables”.
        Oh Debra, that blog post link doesn’t work (any more).

    2. THank you boss this was crisp simple and clear . hatsoff of to people like you who save so much of your time..Happy new year

  1. SO HELPFUL. I thought I was losing my mind! Why is the grouping not the option, instead of the ungrouping. I could see where it would be helpful but not in this case. There are actually times when Excel is NOT smarter than me and I wish it would just do what I say!

  2. Thank you. But I am looking for how to avoid SLICERS from Pivot-tables not to state “Jan, Feb, Mar” etc, as a summry of Jan 2017, Jan 2018 and Jan 2019… Any one can help?

    If you can, great big thanks in advance!

  3. On Excel / Mac you have to do it at the Excel Preferences level, not the worksheet level, So:

    Excel Preferences
    Tables & Filters

    There are two tick boxes to play with:

    Groups Dates when filtering
    Disable Automatic grouping of Date/Time columns in PivotTables

    the second worked for me.

  4. Thank you! This was frustrating me for a couple of minutes…

    This is typical Microsoft. They do stuff automatically for the user to “help” them, but don’t make it obvious how you can stop/reverse it. I really hate it, at least give me a little popup “hey! we grouped your dates for you! If you don’t want that, click here”.

    It’s especially aggravating if you’re a techy user and MS starts messing around in your settings or configuration to “help” you. Stuff breaks or stops working the way you set it up and you don’t know why and you spend hours fixing it. If you want to “help” me, let me know so at least I know what’s going on on my machine.

    Rant over.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.