Pivot Table Issues

How to Prevent Excel Pivot Table Month Grouping from Disappearing

Huda QurayshiHuda Qurayshi Oct 22, 2026 874 views

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.

How to Prevent Excel Pivot Table Month Grouping from Disappearing
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the rows to remove

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.

2
Delete the entire rows

Right-click the highlighted row numbers and choose 'Delete' from the context menu so that the rows are entirely removed from the table structure.

3
Refresh the PivotTable

Navigate to your PivotTable worksheet, right-click anywhere inside the PivotTable, and select 'Refresh'. The month grouping will now remain intact.

Properly Delete Rows from the Source Table
Verify Table Boundaries: Confirm that the bottom of your Excel table ends exactly at the last row of data. Any trailing blank rows mistakenly included in the table range will cause the grouping to fail.
Efficient Data Analysis

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. 1. Open your data file: Launch WPS Spreadsheet and open your existing dataset or Excel file.
  2. 2. Create a PivotTable: Select your data range, navigate to the 'Insert' tab, and click on 'PivotTable'.
  3. 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. 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.
Fully compatible with Microsoft Excel (.xlsx) formatsIntuitive PivotTable creation, modification, and managementSmart automatic date grouping (by months, quarters, years)Free and lightweight alternative for advanced data analysis
QA img-9

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.