Excel PivotTable: Keep Dates Grouped by Month and Year
Question details
The user needs Excel PivotTables to preserve date grouping by month and year without another PivotTable changing to daily dates.
- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- Multiple PivotTables are built from the same source data, and each report needs consistent or different date-grouping settings.
- Observed behavior
- Grouping dates by month and year in one PivotTable can group or ungroup dates in other PivotTables, especially after a refresh, because they share a PivotTable cache.
Check whether the affected PivotTables use the same source range, table, or PivotTable cache. Refresh the PivotTable from the PivotTable Analyze tab and note whether the grouping changes afterward.
Check the shared PivotTable cache and restore month-and-year grouping
Use this option when all related PivotTables should display dates by month and year.
PivotTables created from the same source commonly share an internal PivotTable cache. Date grouping is stored in that shared cache, so grouping or ungrouping a date field in one report can affect linked reports.
Click any cell inside the PivotTable, then open the PivotTable Analyze tab on the Excel ribbon.
Choose Refresh in the Data group and check whether the date field returns to the expected grouping.
Right-click a date label in the PivotTable, select Group, choose Months and Years in the Grouping dialog, and click OK.
Review each linked PivotTable after grouping and refreshing to confirm that the month-and-year layout is consistent.
Create an independent source for PivotTables that need different grouping
Use this option when one PivotTable must remain grouped by month and year while another must show individual days.
Build Compatible PivotTable Reports with WPS Office
WPS Office is a free, lightweight Microsoft Office alternative for creating and reviewing spreadsheet reports. Its familiar interface and compatibility with Excel XLSX files make it easier to migrate existing workbooks and maintain organized PivotTable-style analysis.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the existing Excel XLSX workbook.
- 2. Review the source data: Check the date column and supporting fields before creating or editing a summary report.
- 3. Create an independent report source: Copy the required source data to a separate worksheet or table when different reports need different date views.
- 4. Save in Excel format: Use Menu > Save As and select Excel 2007-365 Workbook (*.xlsx) to preserve compatibility when sharing the file.
Frequently Asked Questions
Why does grouping dates in one Excel PivotTable affect another PivotTable?
The PivotTables likely share the same PivotTable cache created from the same source. Grouping or ungrouping a date field is then applied to the linked reports as well.
How do I group an Excel PivotTable date field by month and year?
Click a date inside the PivotTable, right-click it, select Group, choose Months and Years, and click OK. Refresh the PivotTable afterward if the grouping does not display correctly.
Can I keep one PivotTable by day and another by month?
Yes, but the PivotTables should use independent source ranges, tables, or caches. Copy the source data to a separate worksheet or table before inserting the second PivotTable.
Why does refreshing an Excel PivotTable remove or change date grouping?
A refresh can reapply settings from the shared PivotTable cache or expose inconsistent date values in the source. Confirm that the date column contains valid dates and review the grouping after selecting PivotTable Analyze > Refresh.
Will changing the source range create a separate PivotTable cache?
Using a genuinely independent source range or table when inserting a new PivotTable is the safer approach for separate grouping. Reusing the original source may continue to link the reports through the same cache.
