How to Show Months as PivotTable Row Labels in Excel
Question details
The user needs to display months as row labels in an Excel PivotTable, but the grouping option is either missing or not applied automatically.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Organizing data chronologically by grouping daily date entries into monthly summaries within a PivotTable.
- Observed behavior
- The PivotTable does not offer months as a row-label grouping option, often caused by the source date field containing ungrouped data, text-formatted dates, or invalid values.
Ensure that all data in your source date column are formatted as valid Excel dates, with no blank cells, errors, or text strings mixed in.
Group PivotTable Dates by Month
Use the built-in Group feature to quickly summarize your daily date values into months without creating helper columns.
Excel allows you to quickly aggregate date values into broader chronological categories like months, quarters, or years directly within a PivotTable. This feature makes it simple to analyze trends over time.
Locate any valid date inside the row labels area of your active PivotTable and right-click on it to open the context menu.
Click on 'Group...' from the dropdown list to open the Grouping dialog box.
In the dialog box, click on 'Months' to highlight it. If your data spans multiple years, it is highly recommended to also click 'Years' so your months are separated correctly.
Click 'OK' to apply the changes. Your PivotTable will now display months as row labels.

Fix Invalid Dates in the Source Data
Resolve underlying data issues that prevent the PivotTable grouping feature from working.
Easily Group and Analyze PivotTables in WPS Spreadsheet
WPS Spreadsheet offers powerful PivotTable functionalities identical to Microsoft Excel. You can quickly group dates by months, quarters, or years to analyze data trends with ease, all within a highly compatible and lightweight application.
- 1. Insert a PivotTable: Select your source data range, navigate to the Insert tab on the ribbon, and click 'PivotTable'.
- 2. Add Date Field to Rows: In the PivotTable Fields pane, drag your date column into the 'Rows' area to display the individual dates.
- 3. Group by Month: Right-click any date value in the resulting PivotTable, select 'Group', highlight 'Months', and click 'OK'.

Frequently Asked Questions
Why is the Group option missing or grayed out in my PivotTable?
This usually happens when the source date column contains blank cells, text strings, or error values. The grouping feature requires every cell in the designated column to be formatted as a valid date. Fix your source data, then refresh the PivotTable.
How do I group by both months and years in a PivotTable?
When the Grouping dialog box opens, you can select multiple time units by clicking on them. Highlight both 'Months' and 'Years' (they will appear highlighted in blue) before clicking OK. This ensures data spanning multiple years is separated properly.
Can I ungroup dates if I want to see the daily data again?
Yes. Simply right-click on any of the grouped month labels in your PivotTable and select 'Ungroup' from the context menu. This will instantly revert the view back to the original daily date values.




