Pivot Table Issues

How to Filter an Excel PivotTable by Different Months in Different Years

Maira Mehtab Oct 1, 2026 868 views

Question details

The user wants to filter a PivotTable to show data for specific selected months in one year and different specific months in another year.

How to Filter an Excel PivotTable by Different Months in Different Years

Product: Excel
Device & OS: not provided
Scenario: Filtering data across non-consecutive or mixed date ranges within a single PivotTable.
Observed behavior: Needs a way to isolate data for customized year-and-month combinations, such as January/February of one year and March/April of another.

Before you start

Ensure your source data includes a valid date column formatted correctly as dates (not text) before attempting to apply advanced time filters to your PivotTable.

Solution 1Recommended

Use the PivotTable Timeline Feature

The Timeline is a visual filtering tool that makes it easy to select and adjust date ranges interactively without altering source data.

The Timeline feature is highly recommended for date filtering because of its intuitive interface. It allows you to drag a slider to include specific months, quarters, or years seamlessly.

Access PivotTable Analyze

Click anywhere inside your PivotTable to reveal the PivotTable Analyze tab on the top ribbon.

Insert Timeline

In the Filter group on the ribbon, click on 'Insert Timeline'.

Select Date Field

Check the box next to your Date field in the prompt dialog and click OK.

Adjust the Timeline

Use the slider on the Timeline box to select the specific months and years you need. You can change the time level from Months to Years or Quarters from the drop-down on the top right of the Timeline.

Quick Range Selection: You can hold down the Shift key or drag the edges of the timeline selector to highlight a continuous range spanning across multiple years.

Solution 2

Create a Helper Column in the Source Data

Use a helper column with an IF/AND/OR formula for highly specific, non-contiguous month and year combinations that a standard Timeline might not easily capture.

Solution 3

Apply Standard Date Filters

Use the built-in Date Filters if your required months form a continuous date range across years.

Filter PivotTables Efficiently

Easily Manage and Filter PivotTables with WPS Office

WPS Spreadsheet provides robust PivotTable functionalities, including dynamic timelines and advanced formula filtering, to handle complex data analysis effortlessly.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your data and PivotTable.
2. Access PivotTable Tools: Click on your PivotTable to activate the PivotTable Analyze tab on the top ribbon.
3. Use Timelines: Select 'Insert Timeline' to visually filter your data across different months and years.
4. Apply Helper Columns: Alternatively, utilize WPS Spreadsheet's full formula support to add custom IF statements for advanced conditional filters.

Fully compatible with Microsoft Excel (.xlsx) files and existing PivotTables.Supports interactive Timeline tools for visual date filtering.Provides complete formula support for building advanced helper columns.Free, lightweight, and features a familiar tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Can I select multiple non-adjacent months using the Timeline feature?

Standard Timelines are designed for selecting continuous date ranges. While you can hold down the Shift key to expand a selection, picking entirely non-adjacent months (like January 2020 and June 2021 exclusively) is difficult on a single timeline. For disjointed dates, using a Helper Column in the source data is the most reliable approach.

Why is the Insert Timeline option grayed out in my ribbon?

The Timeline feature requires the source data to have a column formatted strictly as dates. If the dates are saved as text strings, or if the date column contains blank cells or errors, Excel cannot process the timeline structure and the option will be unavailable.

How do I refresh a PivotTable after updating my helper column?

To include new helper column calculations, right-click anywhere inside the PivotTable and select 'Refresh'. Alternatively, go to the PivotTable Analyze tab on the ribbon and click the 'Refresh' button. The new field will then appear in your PivotTable Fields list.

Will filtering out specific months affect my PivotTable Grand Totals?

Yes. When you apply filters via a Timeline, a helper column, or standard Date Filters, the PivotTable Grand Totals and Subtotals will automatically recalculate to sum only the visible data that meets your filter criteria.

Excel & Data Problems