Pivot Table Issues

How to Keep Excel Pivot Table Dates Grouped by Month

Maira MehtabMaira Mehtab Oct 28, 2026 871 views

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.

How to Keep Excel Pivot Table Dates Grouped by Month
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.
Before you start

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.

Solution 1Recommended

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.

1
Inspect the source data range

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.

2
Remove blank rows and columns

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.

3
Refresh the Pivot Table

Navigate back to the sheet containing your Pivot Table, right-click anywhere inside the table, and select 'Refresh' to update the data cache.

4
Group dates by month and year

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.

Pro Tip: Convert your raw data range into an official Excel Table by pressing Ctrl+T. This automatically adjusts the data source range dynamically when you add new records, preventing the accidental inclusion of infinite blank rows.
Use WPS Spreadsheet for Pivot Tables

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. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing .xlsx spreadsheet containing the raw data.
  2. 2. Insert a Pivot Table: Select your data range, navigate to the 'Insert' tab on the top ribbon, and click 'PivotTable'.
  3. 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. 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. 5. Refresh securely: When adding new data, simply right-click the Pivot Table and hit 'Refresh'. Your monthly groupings will remain completely preserved.
Keeps Pivot Table date groupings perfectly intact after a data refresh100% compatible with Microsoft Excel .xls and .xlsx file formatsFree and lightweight alternative for complex data analysis tasksIntuitive interface with powerful data visualization and grouping tools
QA img-9

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.