How to Prevent Excel Pivot Table Month Grouping from Disappearing
Question details
The user needs to maintain the month grouping in an Excel PivotTable's Columns area, which currently disappears when the source data is modified and the table is refreshed.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Adding or deleting source data rows and subsequently refreshing a connected PivotTable.
- Observed behavior
- The month grouping formatting in the PivotTable's Columns area drops and disappears entirely after the refresh.
Verify that your source data is formatted as an official Excel Table and ensure that all entries within your date column are recognized as valid date formats without trailing blanks.
Properly Delete Rows from the Source Table
Using the Delete key only clears cell contents and leaves blank rows that break date grouping. You must delete the entire row instead.
When managing PivotTable source data, clearing a cell's contents is fundamentally different from deleting the row. Blank rows left inside the source table are interpreted as null values by the PivotTable, causing date grouping features (like month grouping) to instantly break and disappear upon refresh.
In your Excel source table, click and drag on the row numbers on the left side to select the complete rows you wish to remove.
Right-click the highlighted row numbers and choose 'Delete' from the context menu so that the rows are entirely removed from the table structure.
Navigate to your PivotTable worksheet, right-click anywhere inside the PivotTable, and select 'Refresh'. The month grouping will now remain intact.

Ensure Valid Date Formatting in the Source Column
PivotTables cannot group dates if there is text or invalid date formats mixed into the source date column.
Easily Manage Pivot Tables with WPS Spreadsheet
WPS Spreadsheet provides robust PivotTable functionality that flawlessly groups dates, handles data modifications, and prevents unexpected grouping errors when refreshing data. It features a highly intuitive interface perfectly compatible with Excel files.
- 1. Open your data file: Launch WPS Spreadsheet and open your existing dataset or Excel file.
- 2. Create a PivotTable: Select your data range, navigate to the 'Insert' tab, and click on 'PivotTable'.
- 3. Group dates by month: Drag your date field to the Columns or Rows area, right-click any date header, select 'Group', and choose 'Months'.
- 4. Safely update data: When updating source data, easily delete entire rows using right-click > Delete, then click 'Refresh' in the PivotTable Options to maintain perfect grouping.

Frequently Asked Questions
Why do I get a 'Cannot group that selection' error in my Excel Pivot Table?
This error occurs when the date field contains non-date values such as blank cells, text values, or improperly formatted dates. You must check your source data column to ensure every cell contains a valid Excel date format and remove any empty rows.
Can I group dates by both month and year simultaneously?
Yes. Right-click any date value in the PivotTable, select 'Group', and then click on both 'Months' and 'Years' in the grouping dialog box to highlight them. This will organize your data hierarchically.
How do I temporarily hide blank rows without breaking Pivot Table grouping?
Instead of leaving empty rows inside the source table, delete the rows entirely from the table structure. If you need to reserve space for demonstration purposes, it is better to add the data later and refresh, rather than keeping blank placeholders.




