Pivot Table Issues

How to Filter an Excel PivotTable by Year and Show Years in Rows

Elise Williams Sep 30, 2026 868 views

Question details

The user wants to group date fields by year in a PivotTable and apply a slicer to filter specific years interactively.

How to Filter an Excel PivotTable by Year and Show Years in Rows

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing time-series data in a PivotTable and needing to view and filter specific yearly summaries easily.
Observed behavior: The goal state is to have dates automatically grouped by year (and optionally quarters and months), with a slicer added for interactive visual filtering.

Before you start

Ensure your source data contains valid, properly formatted date values without any text or blank cells in the date column, as these will prevent the PivotTable grouping feature from working.

Solution 1Recommended

Group Dates by Year and Insert a Slicer

Use the native grouping feature to organize dates into years, then add a slicer to filter the data visually.

By default, newer versions of Excel automatically group dates into years, quarters, and months when you add a date field to the Rows area. If it doesn't group automatically, you can manually trigger the grouping and insert a slicer to filter the data seamlessly.

Add the Date field to Rows

Click anywhere inside your PivotTable. In the PivotTable Fields pane on the right, drag and drop your Date field into the 'Rows' area.

Group dates by Year

Right-click any date in the PivotTable and select 'Group' from the context menu. In the Grouping dialog box, click on 'Years' so it is highlighted in blue (you can also select Quarters and Months), then click OK.

Insert a Slicer

Navigate to the 'PivotTable Analyze' (or Options) tab on the ribbon. Click on 'Insert Slicer', check the box next to the newly created 'Years' field, and click OK.

Filter the data

A slicer window will appear on your spreadsheet. Click on any year within the slicer to instantly filter your PivotTable to show only data for that specific year.

Selecting Multiple Years: You can hold the Ctrl key while clicking on the slicer buttons to select and display multiple years at the same time.

Group and Filter PivotTables by Year in WPS Spreadsheet

WPS Spreadsheet provides a powerful, intuitive PivotTable feature that makes grouping dates and adding interactive slicers incredibly simple, allowing you to analyze large datasets efficiently.

1. Create your PivotTable: Open your dataset in WPS Spreadsheet, navigate to the Insert tab, click PivotTable, and select where you want it placed.
2. Group by Years: Drag the Date field to the Rows box. Right-click a date cell in the PivotTable, choose 'Group', and highlight 'Years'.
3. Add a visual Slicer: Go to the PivotTable Options tab, select 'Insert Slicer', check the Year field, and use the interactive buttons to filter your data.

Fully compatible with Microsoft Excel (.xlsx) PivotTables and slicers.Intuitive drag-and-drop interface for seamless data grouping.Lightweight application that handles large datasets and complex time-series analysis without lag.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I group dates by year in my PivotTable?

This usually happens if your date column contains blank cells, text strings, or improperly formatted dates. Check your source data and ensure all cells in the date column are formatted as valid dates before attempting to group.

How do I ungroup dates in a PivotTable?

Simply right-click on any of the grouped date headers (like the Year or Quarter) directly in your PivotTable and select 'Ungroup' from the context menu to revert back to individual dates.

Can I show months instead of years in the slicer?

Yes. When inserting a slicer, check the box for 'Months' instead of 'Years'. Ensure that 'Months' was selected alongside 'Years' during the grouping step so it appears as an available field for slicing.

Will my slicer work if I share the file with someone using an older version of Excel?

Slicers are fully supported in modern versions of Excel and WPS Spreadsheet. However, if the file is opened in a very old version (such as Excel 2007 or earlier), the slicer feature may not function properly and will not filter the PivotTable.

Excel & Data Problems