Stop Pivot Table Date Grouping

When you add a date field to your Pivot Table, Excel automatically groups the dates into a hierarchy, such as years and months. See how to stop pivot table date grouping in the latest versions of Excel, and a couple of workarounds for Excel 2016. You can also read about why this feature was added, and watch a date grouping video.

Prevent Date Grouping

It’s easy to prevent automatic date grouping for pivot tables in Excel 2019/365, by changing a setting. See the simple steps below.

For Excel 2016, it takes more work — use either the Excel 2016 Workaround, or the Excel 2016 Registry tweak, described below.

Further down in this blog post, you can read more about date grouping in Filters, and the benefit of pivot table date grouping, which started in Excel 2016.

Excel 2019/365 Setting

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.

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

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

Excel 2016 Workaround

In Excel 2016, there isn’t an easy way turn off pivot table date grouping. However, to prevent dates from automatically grouping in Excel 2016, you can use this 2-step workaround:

• Add the date field to the pivot table Report Filter area first. Dates are not automatically grouped there
• Then, in the PivotTable Fields list, drag the date field into the Rows or Columns area. Dates will remain ungrouped.

Excel 2016 Registry

The only way to turn off pivot table date grouping in Excel 2016, if you’re brave enough, is by making a change to the Windows Registry.

To do that, follow the instructions in this article by Microsoft’s Excel team. Remember to make a backup of the registry first, and read the warning at the top of that page.

Date Grouping in Filters

Starting in Excel 2007, dates have been automatically grouped into a hierarchy, such as years and months, when you turn on an AutoFilter or create a named Excel table.

You can change an Excel option to turn that automatic grouping on or off, and there are instructions and a video on my Contextures website.

Date Grouping in Pivot Tables

Pivot Tables dates weren’t grouped in earlier versions of Excel, but that behaviour changed in Excel 2016. Since that version, dates in Pivot tables have been automatically grouped too.

Here’s a pivot table in which I added the Category and OrderDate fields to the Rows area. Because of the date grouping, extra columns were automatically created, to show the Years and Quarters, as well as the Order Date.

Benefit of Date Grouping

Date grouping in pivot tables can be a helpful feature, and this archived blog post from the Excel team explains why this feature was added.

Implementing grouping for Data Model PivotTables allows for grouping to be used in conjunction with the power of the xVelocity engine and is a key feature for making Data Model PivotTables a replacement for native ones in the future. When used in a data model PivotTable, Time grouping adds relevant Date/Time columns such as Date (Year), Date (Quarter) and Date (Month) to the grouped table in the model; these columns could then be reused with other user endpoints of the data model, such as PowerView and Power BI

The date grouping feature can help in normal pivot tables too. For example, with a large dataset, Excel shows an error message if I try to put the date field into the Columns area.

• “You cannot place a field that has more than 16384 items in the column area. If you want to use this field in the report, click OK, and then move the field to either the row area or page area.”

However, Excel allows me to put the Date field into the Rows area, and it automatically groups the dates into Years and Quarters. That saves me the step of having to group the dates manually.

The new fields – Years and Quarters are also automatically added to the PivotTable Fields list.

With these grouped fields, I can move Years into the Columns area, and Excel won’t show an error message about the item limit.

Undo Pivot Table Date Grouping

So, the date grouping feature can be a real time saver, if you usually group the dates manually. But, if you don’t like the grouping feature, there are steps you can take to undo it or stop it, depending on which version of Excel you’re using.

For all versions (Excel 2016 and later), you can manually ungroup the dates, with one of these methods:

Keyboard Shortcut

• Add the Date field to the pivot table
• Immediately press Ctrl + Z, to undo the automatic date grouping
• The grouped fields are removed, and the original Date field remains in the pivot table

Ungroup Command

• Add the Date field to the pivot table
• At any time, right-click on any of the date field items or headings
• In the right-click menu, click Ungroup
• The grouped fields are removed, and the original Date field remains in the pivot table

Video: Pivot Table Date Grouping

Watch this video to see how to group the date field in 4-week periods, and set the starting date. There are written instructions on my Contextures website – Group Pivot Table Dates

Related Articles

Set Start Date For Pivot Table Grouping

Grouped and Ungrouped Dates from Same Pivot Table Source

Grouping Pivot Table Dates by Fiscal Year

Grouping Pivot Table Dates by Months and Weeks

______________________________

Stop Pivot Table Date Grouping

______________________________