Excel Pivot Table Filter by Year: Steps and Fixes

Charts & pivot tables · Updated November 19, 2026

To filter an Excel PivotTable by year, group its date field by years, then use the resulting Years field’s dropdown to select the year you want. In Microsoft 365 for Windows, right-click a date in the PivotTable, choose Group, select Years, and click OK; you can then filter the grouped years like any other PivotTable field.

How year filtering works

A PivotTable summarizes records by fields in its source data. If a source column contains individual dates, Excel initially treats it as a date field, which may appear as a list of days or may be grouped automatically into months, quarters, and years. Grouping tells Excel to create date categories, including a year category, so you can summarize all records from a year together.

For example, suppose a sales list has a Transaction Date column and a Revenue column. Grouping Transaction Date by Years lets the PivotTable total revenue for 2023, 2024, and 2025 separately. Selecting 2024 in the year filter then hides the other years from the PivotTable’s displayed results. It does not delete or alter the source records.

There are two useful ways to interpret “filter by year.” You can group dates and select a year, which is generally clearest for summaries, or keep the dates ungrouped and apply a date-range filter. The first creates a year category in the PivotTable; the second limits the underlying date field to a range without adding a separate Years field.

Group dates into years and select a year

  1. Click a cell inside the PivotTable so the PivotTable tabs appear.
  2. Find a date value in the date field, such as Transaction Date. Right-click that date and choose Group. You can also use PivotTable Analyze > Group Field when the date field is selected.
  3. In the Grouping dialog, select Years. Clear other selected intervals if you want years only, or keep options such as Months if you want to drill into detail later. Click OK.
  4. Find the new Years field in the PivotTable. Open its filter dropdown and select the year or years to display. Clear (Select All) first if you want to choose only a few years, then click OK.

Excel may place the grouped Years field in the Rows area, often above the original date field. If the PivotTable is arranged in a compact layout, the dropdown might be labeled Row Labels rather than showing the field name. Use the dropdown next to the year labels, or move Years to the Filters area in the PivotTable Fields pane if you prefer a report filter above the table.

To show every year again, open the same dropdown and choose Clear Filter From followed by the field name, or select all listed years. The exact wording can vary slightly with the field’s position and Excel build.

Choose a filter method for your report

Grouping by year is usually the best choice when the PivotTable should compare annual totals or let readers switch between years. It keeps year selection visible and makes it easy to display multiple years at once. Put the Years field in Rows for a year-by-year comparison, or put it in Filters when the report should show one selected year above the results.

If you need a flexible visual control, use a timeline. Click inside the PivotTable and choose PivotTable Analyze > Insert Timeline, select the date field, and click OK. Use the timeline’s time-level control to display years, then select or drag across the year or years you want. A timeline is useful in a dashboard because people can change the date window without opening a field dropdown. It filters a date field; it does not replace the underlying dates or change the source data.

For a simple one-time date window, an ungrouped date field can be filtered directly. Open its dropdown and choose Date Filters, then a suitable option such as Between. Set the beginning and ending dates for the desired calendar year—for example, January 1 through December 31, 2024. This is handy if grouping is unavailable or you want a precise interval, but it may be less obvious to another person reading the report than a clearly labeled Years field.

  • Grouped Years: Best for annual summaries and selecting one or more complete years.
  • Timeline: Best for interactive reports and changing the date span visually.
  • Date Filters: Best for a specific start-to-end range or when you do not want to group dates.

Filter one year, several years, or a fiscal year

To show one year, select just that year in the Years dropdown. To compare two or more years, select all the required year labels; multi-select filtering is supported by the field dropdown. If the list includes many years, search or scroll within the dropdown, and confirm that Select Multiple Items is enabled when you need to make multiple selections. A report filter can also show a single selection at a time, depending on its layout.

A calendar year is not automatically a fiscal year. If a company’s fiscal year starts in July, selecting the grouped year 2024 still means January through December 2024. One way to create fiscal-year reporting is to add a Fiscal Year column to the source data, calculated according to the organization’s definition, and use that field in the PivotTable. Another option is to use date grouping by months and quarters, but grouping alone does not redefine the year boundary to match a fiscal calendar. Confirm the organization’s fiscal-year labels and start month before building the field.

For a dashboard with multiple PivotTables, a timeline or slicer can sometimes control more than one PivotTable. Select the control and use its Report Connections or PivotTable Connections option to choose compatible PivotTables. The tables generally need to share the same PivotTable source or data model. If a table is absent from the connections list, it may not share a compatible cache or model; adding another control will not fix that underlying difference.

Why date grouping may fail

If Group is unavailable, or Excel reports that it cannot group the selection, check the source date column. A date that looks correct on screen may actually be text, and even one blank or invalid entry in a date field can prevent grouping. Errors and mixed types can cause similar trouble. Excel needs consistent, valid date values for the field it is grouping.

  • Check several source cells and confirm they are real Excel dates, not text that merely resembles a date. Changing the cell format alone does not convert text into a date.
  • Look through the entire source date column, including rows outside the visible PivotTable, for blanks, errors, or text entries.
  • Correct or remove invalid entries, or use a consistent placeholder only if that is appropriate for the analysis. Do not silently assign an arbitrary date to unknown records.
  • Refresh the PivotTable after correcting the source. Use PivotTable Analyze > Refresh, or right-click inside the PivotTable and choose Refresh.

If dates were imported from a text file, regional date settings can also matter. A value such as 03/04/2024 can be interpreted differently depending on whether the source uses month/day/year or day/month/year. Verify the intended meaning before converting or grouping the values; otherwise, a technically valid date could be assigned to the wrong day, month, or year.

Refreshes, source ranges, and filter behavior

A PivotTable’s results do not necessarily update immediately when source rows change. Refresh it after adding or correcting records. If new records are not included, check the PivotTable’s source range. A source formatted as an Excel Table generally expands as records are added, while a fixed cell range may need to be adjusted. To inspect it, click the PivotTable and choose PivotTable Analyze > Change Data Source.

Filtering a year changes what the PivotTable displays, not which records exist in the source. Likewise, hiding a year from a field dropdown is not the same as deleting those rows. This matters when a total appears smaller than expected: check the active year filter, any other field filters, and whether the source range includes all relevant records.

In Microsoft 365 for Windows, the steps above use the standard PivotTable ribbon and right-click menus. Excel for Mac has PivotTable tools as well, but ribbon labels or available controls can differ by release. Excel for the web may not offer every desktop grouping or timeline command in the same place. If you cannot find Group or Insert Timeline in your version, use the available date filter or open the workbook in desktop Excel, then refresh and verify the result.

Frequently Asked Questions

How do I filter a PivotTable to show only 2024?
Group the date field by years if it is not already grouped. Open the Years field dropdown, clear the current selections, select 2024, and apply the filter. Check that no other active filters are excluding records.

Can I filter a PivotTable by year without grouping?
Yes. Open the date field dropdown, choose Date Filters > Between, and enter the first and last dates of the year. Grouping is more convenient for repeatedly selecting named years, while a date-range filter is useful for a one-off interval.

Why is Group greyed out or showing an error?
The source date field may include blanks, text dates, errors, or inconsistent values. Inspect the full source column, correct the invalid entries, and refresh the PivotTable before trying to group it again.

How can I show two years at the same time?
Open the Years dropdown, enable multiple selections if needed, and select both year labels. You can then compare them in the PivotTable, provided the year field is arranged in a way that displays separate year results.

Does choosing 2024 filter a fiscal year?
Not by default. Grouped years use calendar years, from January through December. For a fiscal year with a different start month, add and use a fiscal-year field defined to match the organization’s reporting calendar.

Check the result before sharing

After applying the filter, confirm the selected year is visible in the PivotTable and that the totals make sense for the intended date range. Review the source dates, refresh status, and any additional filters if records appear to be missing. For recurring reports, label the control clearly—such as Calendar Year or Fiscal Year—and keep the definition consistent so readers know exactly which dates the summary includes.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.