Pivot Table Issues

Stop Excel PivotTables from Grouping Dates into Quarters and Years

Camila MilosovichCamila Milosovich Nov 22, 2026 868 views

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.
Before you start

Open the workbook and locate a date label in the PivotTable. Check that the source date column contains valid Excel dates rather than text.

Solution 1Recommended

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.

1
Remove the existing grouping

Right-click a grouped date label in the PivotTable and select Ungroup.

2
Open the grouping options

Right-click a date in the PivotTable and select Group.

3
Select months only

In the Grouping dialog, select Months and clear any other selected intervals, including Quarters and Years. Click OK.

Ungroup the dates and group them by month only
Dates from different years: Grouping by Months alone combines the same month across years. Include Years if you need to keep each year's months separate.
Group PivotTable dates in WPS

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. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Find the date field: Click the PivotTable and locate a date label in its row or column area.
  3. 3. Change the grouping: Right-click a grouped date label and select Ungroup if needed. Then right-click a date and select Group.
  4. 4. Choose months: In the grouping options, select Months, clear Quarters and Years, and confirm the change.
Group PivotTable dates by month for a concise summaryOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with spreadsheets using the free WPS Office suite
QA img-9

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.