How to 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.

Or, if you have my Excel Tools add-in, that macro is built in. Click the Workbook Appearance command, then click Toggle Filter Grouping.

group dates option

Grouped Dates in Pivot Table

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

Avoid the Date Grouping

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.

Prevent Pivot Table Date Grouping

Unfortunately, there’s isn’t an Excel option that will prevent grouped dates in pivot tables. If you really want to prevent pivot table date grouping, you can make a change in the Windows Registry.

To do that, follow the instructions in this blog post by the Excel team. Remember to make a backup of the registry first, and read the warning at the top of that blog post.

 

__________________________

Save

You may also like...

3 Responses

  1. Jeff Weir says:

    Hilarious…my workplace internet police flagged the video in this post as “Potentially Adult Audio/Video clips” no doubt based on the ‘Group Dates’ tag. Obviously grouped dates ain’t what they used to be :-)

    I’m surprised there is no UserVoice entry for this. Maybe there was, and they closed it because they still think it’s a ‘feature’. Most users don’t agree, including my boss who struck this yesterday and couldn’t for the life of him work out what was going on. And he’s probably far smarter than your average user. God knows what the actual average user makes of this feature.

  2. Susanne McRae says:

    In Excel 2013, when I copy rows of data (where column A is a date) into my spreadsheet, Excel automatically sets all those rows with 5 ‘levels’ of grouping, ..which I must then manually remove. (ie. Ungroup, Clear Outline). I tried setting the Options, Advanced, ‘Group dates in the AutoFilter menu’ to Off. ..but it still sets it with 5 levels. Is there somewhere else I can turn this ‘feature’ off?
    Thank you kindly

Leave a Reply to Jeff Weir Cancel reply

Your email address will not be published. Required fields are marked *