Pivot Table Issues

Fix Excel Pivot Table Date Grouping That Keeps Changing

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to stop an Excel Pivot Table and PivotChart from repeatedly regrouping dates by day even after the automatic date-grouping options have been disabled.

Product: Microsoft Excel 365
Device & OS: not provided
Scenario: Working with data analysis using Pivot Tables and PivotCharts where custom date groupings are continuously overridden by the software.
Observed behavior: The PivotTable automatically regroups the date fields by day, ignoring the user's manual grouping preferences and disabled automatic grouping settings.

Before you start

Ensure your source data column contains strictly valid date values, as blank cells, text strings, or mixed data types can cause Pivot Table grouping features to malfunction.

Solution 1Recommended

Verify Source Data and Manually Regroup Dates

Cleaning your source data and reapplying the grouping manually can often resolve automatic grouping overrides.

Excel 365 sometimes reverts to default grouping behaviors if it detects inconsistencies in the source data. Ensuring absolute data uniformity is the first step in troubleshooting this bug.

Inspect the source data

Go to the original worksheet containing your source data. Highlight the entire date column and ensure there are no blank cells or text values hidden within the dataset.

Ungroup existing dates

Right-click any date cell inside your Pivot Table and select 'Ungroup' from the context menu to clear all automatic groupings.

Reapply custom grouping

Right-click the date field again, select 'Group', and manually choose your preferred timeframe (e.g., Months, Quarters, or Years), then click 'OK'.

Disable Automatic Grouping: You can also verify that automatic grouping is globally disabled by navigating to File > Options > Data and checking 'Disable automatic grouping of Date/Time columns in PivotTables'.

Solution 2

Create a Sanitized Test File for Support

If the problem persists, reproducing the error in a clean, sanitized workbook allows support teams to investigate the specific Excel 365 bug without compromising your privacy.

Free Microsoft Office alternative

Switch to WPS Office for Reliable Pivot Table Management

If Excel 365 continues to override your manual date grouping preferences, consider using WPS Office. It provides a lightweight, highly compatible spreadsheet environment where your Pivot Table settings remain stable and responsive to your manual inputs.

1. Download and install: Visit the official WPS website to download WPS Office and install it on your computer.
2. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx file containing the problematic Pivot Table.
3. Apply your settings: Group your dates exactly how you want them, knowing the settings will be preserved upon saving.

Excellent compatibility with Microsoft Excel (.xlsx) formats and Pivot TablesStable date grouping that strictly respects your manual configurationsFree, lightweight, and fast to install without cloud-storage complicationsFamiliar user interface requiring zero learning curve for Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Pivot Table say 'Cannot group that selection'?

This error typically occurs when the selected column contains blank cells, text strings, or data that is not formatted correctly as a valid date or number. Cleaning your source data will fix this issue.

How do I turn off automatic date grouping in Excel?

To disable automatic date grouping, go to File > Options > Data, and check the box labeled 'Disable automatic grouping of Date/Time columns in PivotTables'.

What is a sanitized workbook in Excel?

A sanitized workbook is a duplicate of your original file where all confidential, sensitive, or personal data has been removed or replaced with generic dummy data. This allows you to safely share the file with technical support to troubleshoot functional issues.

Excel & Data Problems