How to Keep Excel Pivot Table Dates Grouped by Month
Question details
The user wants to prevent an Excel Pivot Table from losing its monthly date grouping and reverting to daily totals after refreshing the data.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Refreshing an existing Pivot Table that relies on date fields grouped by months and years.
- Observed behavior
- After a data refresh, the monthly date groups change back to daily totals, and column labels or grand total layouts are unexpectedly altered.
Verify that the date column in your raw data is formatted properly as standard Dates and does not contain hidden text strings or error values, which are the most common causes of grouping failures.
Clean Data Source and Reapply Date Grouping
Invalid data, text strings, or blank rows within the source date column prevent Excel from maintaining groups. Cleaning the source range ensures your month/year grouping holds during a refresh.
A Pivot Table requires every single cell in a date column to be either a valid date or completely blank to sustain date grouping. If even one cell is stored as text, Excel will break the grouping upon refresh.
Go to your raw data sheet and verify that the target date column contains only valid dates or true blanks. Remove any cells containing text entries like 'N/A' or error values.
Check the entire data range that your Pivot Table is referencing. Delete any completely blank rows or columns at the bottom or edges of your dataset that might disrupt the data model.
Navigate back to the sheet containing your Pivot Table, right-click anywhere inside the table, and select 'Refresh' to update the data cache.
Right-click on any date item in your Pivot Table, select 'Group', and highlight 'Months' and 'Years' in the grouping dialog box. Click OK to apply the consistent grouping.
Create and Manage Pivot Tables Effortlessly with WPS Office
WPS Spreadsheet provides a robust and highly compatible Pivot Table feature. It flawlessly handles Microsoft Excel files and maintains your grouped dates, custom layouts, and formatting without unexpected changes upon refreshing.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing .xlsx spreadsheet containing the raw data.
- 2. Insert a Pivot Table: Select your data range, navigate to the 'Insert' tab on the top ribbon, and click 'PivotTable'.
- 3. Add the Date field: In the PivotTable Field List pane, drag your date field into the Rows or Columns area to build your layout.
- 4. Group by Month: Right-click any date cell in the generated Pivot Table, select 'Group', and choose 'Months' to instantly organize your data.
- 5. Refresh securely: When adding new data, simply right-click the Pivot Table and hit 'Refresh'. Your monthly groupings will remain completely preserved.

Frequently Asked Questions
Why does my Pivot Table say 'Cannot group that selection'?
This error occurs when the source data contains non-date values, text formatted to look like dates, or blank cells in the date column. Select your source date column, use 'Text to Columns' under the Data tab, and click Finish to quickly convert text-stored dates into proper numerical dates.
How do I stop column widths and layouts from changing on refresh?
Right-click anywhere inside the Pivot Table, select 'PivotTable Options', and navigate to the 'Layout & Format' tab. Uncheck the box for 'Autofit column widths on update' and make sure 'Preserve cell formatting on update' is checked.
Can I group Pivot Table dates by weeks instead of months?
Yes. Right-click the date field in the Pivot Table and select 'Group'. In the dialog box, unselect 'Months', select 'Days', and change the 'Number of days' counter at the bottom to 7. Ensure the 'Starting at' date falls on the day of the week you want your week to begin.
Does adding new columns to source data ruin the grouping?
Adding new columns does not directly ruin date grouping, provided you update the Pivot Table's data source range correctly. However, if a newly included column contains blanks or text in the date field being grouped, the grouping will break upon the next refresh.




