Pivot Table Issues

How to Show Months as PivotTable Row Labels in Excel

Algirdas JasaitisAlgirdas Jasaitis Oct 8, 2026 871 views

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.

How to Show Months as PivotTable Row Labels in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Right-click the date field

Locate any valid date inside the row labels area of your active PivotTable and right-click on it to open the context menu.

2
Select the Group option

Click on 'Group...' from the dropdown list to open the Grouping dialog box.

3
Choose Months and Years

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.

4
Apply the grouping

Click 'OK' to apply the changes. Your PivotTable will now display months as row labels.

Group PivotTable Dates by Month
Grouping Option Unavailable: If the 'Group' option is grayed out or unavailable, your source data likely contains blanks or text-formatted dates. You must fix the source data first.
Advanced Data Analysis

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. 1. Insert a PivotTable: Select your source data range, navigate to the Insert tab on the ribbon, and click 'PivotTable'.
  2. 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. 3. Group by Month: Right-click any date value in the resulting PivotTable, select 'Group', highlight 'Months', and click 'OK'.
One-click chronological grouping for dates into months, quarters, and years.Seamless compatibility with Microsoft Excel (.xlsx) PivotTable formats and structures.Lightweight software that runs smoothly on all major operating systems.User-friendly interface that makes complex data analysis intuitive for beginners.
microsoft office alternative - wps office

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.