Stop Excel PivotTables from Grouping Dates into Quarters and Years
Question details
The user wants an Excel PivotTable to display dates by month without grouping them into quarters and years.
- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- A date field is added to a PivotTable and needs to be grouped by month only.
- Observed behavior
- Excel automatically groups the date field into years and quarters.
Open the workbook and locate a date label in the PivotTable. Check that the source date column contains valid Excel dates rather than text.
Ungroup the dates and group them by month only
Use this method to replace the existing year and quarter groups with a month-only grouping.
Right-click a grouped date label in the PivotTable and select Ungroup.
Right-click a date in the PivotTable and select Group.
In the Grouping dialog, select Months and clear any other selected intervals, including Quarters and Years. Click OK.

Group PivotTable dates by month in WPS Spreadsheet
Use WPS Spreadsheet to open a workbook and adjust PivotTable date grouping. Select Months without Quarters or Years when the grouping options are available.
- 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Find the date field: Click the PivotTable and locate a date label in its row or column area.
- 3. Change the grouping: Right-click a grouped date label and select Ungroup if needed. Then right-click a date and select Group.
- 4. Choose months: In the grouping options, select Months, clear Quarters and Years, and confirm the change.

Frequently Asked Questions
How do I remove all date grouping from an Excel PivotTable?
Right-click a grouped date label in the PivotTable and select Ungroup. The field will return to individual dates.
Why are January values from different years combined?
When you group by Months without Years, Excel combines matching months across years. Right-click a date, select Group, and choose both Months and Years to separate them.
Can I stop Excel from automatically grouping dates in new PivotTables?
In Excel versions that offer the setting, go to File > Options > Data and select Disable automatic grouping of Date/Time columns in PivotTables. This setting affects automatic grouping; existing groups may still need to be removed manually.
Why can't I group the date field?
Check the source date column for text, blank cells, or invalid dates and correct them. Then right-click the PivotTable, select Refresh, and try Group again.




