How to Show Different Years Separately in an Excel PivotTable
Question details
The user needs to separate PivotTable data by both month and year to prevent identical months from different years from being aggregated together.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating or filtering a PivotTable report using a date field that spans multiple years.
- Observed behavior
- The PivotTable automatically groups dates by month only, causing data from January 2024 and January 2025 to be merged into a single 'January' category.
Check your source data to ensure the date column contains valid Excel date values instead of plain text, as text-based dates cannot be properly grouped or ungrouped.
Modify Grouping Settings to Include Years
Adjust an existing PivotTable to group dates by both Months and Years, preventing cross-year aggregation.
By default, Excel might only group your dates by month. You can manually adjust the grouping settings directly within your PivotTable to include the year so that January 2024 and January 2025 appear as separate items.
Right-click any date cell within your PivotTable's row or column labels.
Select 'Group...' from the context menu. Alternatively, navigate to the 'PivotTable Analyze' tab on the ribbon and click 'Group Field'.
In the Grouping dialog box, click to highlight both 'Months' and 'Years' in the 'By' list.
Click 'OK'. Your PivotTable will now display January 2024 and January 2025 as separate line items.
Disable Automatic Date Grouping in Excel Options
Prevent Excel from automatically grouping date columns by default when creating new PivotTables.
Separate Dates by Month and Year Using WPS Spreadsheet
WPS Spreadsheet provides powerful and intuitive PivotTable features, allowing you to accurately group and filter your date fields by months, quarters, and years without hassle.
- 1. Open your data: Launch WPS Spreadsheet and open the file containing your raw data.
- 2. Insert a PivotTable: Select your data range, go to the 'Insert' tab, and click 'PivotTable'.
- 3. Add Date Field: Drag your Date field to the 'Rows' area in the PivotTable Fields pane.
- 4. Access Grouping: Right-click any date item generated in the PivotTable and select 'Group'.
- 5. Select Criteria: Choose both 'Months' and 'Years' from the grouping options and click 'OK'.
Frequently Asked Questions
Why is Excel grouping my dates incorrectly?
Excel automatically groups dates to simplify large datasets. If it only groups by month, it aggregates the same month across all years together unless 'Years' is also manually selected in the grouping settings.
How do I ungroup dates in a PivotTable completely?
Right-click the grouped date field in your PivotTable and select 'Ungroup' from the context menu. This will remove all hierarchical categories and display every individual date exactly as it appears in your source data.
Why is the 'Group' option grayed out in my PivotTable?
The 'Group' option is usually disabled if your source data contains blank cells or text values within the date column. Ensure all cells in the date column are formatted correctly as valid Excel dates before attempting to group them.
