To filter an Excel PivotTable by year, group its date field: right-click any date in the PivotTable, choose Group, select Years, and click OK. Then use the new Years field’s dropdown to show the year or years you want.
How year grouping works
A PivotTable can summarize dates at different levels, such as individual days, months, quarters, or years. Grouping a date field by years creates a year-level field in the PivotTable; it does not change the dates in your source data. You can use that field to organize totals by year or filter the report to selected years.
This is usually the clearest method when the goal is to compare annual totals. For example, a sales PivotTable with transaction dates in the Rows area can show a separate total for 2023, 2024, and 2025. Selecting 2024 in the grouped year field then limits the report to transactions from that calendar year.
Grouping is different from merely changing how dates look. Applying a date number format changes the display of a date, but it does not create a year field or filter the underlying records. Likewise, filtering the original date field can select a range, while grouping gives you a convenient year label to reuse in the report.
Group a date field by year
These steps apply to Excel for Microsoft 365 on Windows. Start with a PivotTable whose source date column contains real Excel dates, then:
- Click a date value inside the PivotTable. Select a date item, not a subtotal or the Grand Total.
- Right-click the selected date and choose Group. You can also select the date and look for Group Field on the PivotTable Analyze tab.
- In the Grouping dialog box, select Years. Clear other intervals if you want only years; leave Months or Quarters selected as well if you need those levels beneath each year.
- Click OK. Excel adds a grouped year field to the PivotTable, and the displayed row or column labels update to show the grouped dates.
- Open the dropdown for the year field and select the year or years to display.
If your PivotTable puts dates in the Columns area rather than Rows, the year field appears among the column labels instead. If you prefer a report filter at the top of the PivotTable, drag the grouped year field from the field list into the Filters area.
Show one year or several years
Use the dropdown beside the grouped year field’s label. In the filter list, clear Select All, check the year you need, and confirm the selection. To compare two or more years, check each of those years rather than selecting only one. The PivotTable recalculates its totals for the included dates.
For example, suppose a report contains order dates from 2022 through 2025 and revenue in the Values area. Selecting 2024 shows revenue for dates in that calendar year; selecting 2023 and 2024 together lets you compare both years in the same PivotTable. You can keep Year in Rows to see separate totals, or move Year to Filters to show combined totals for the selected years.
When the field is in the Filters area, its dropdown appears above the report. This can make a dashboard easier to use, but it may hide which years are selected if the report filter is set to a compact display. Check the filter label before interpreting totals, especially when sharing a workbook.
Grouping versus Date Filters
Grouping is not the only way to restrict a PivotTable’s dates. Open the dropdown for the original date field and look for Date Filters. Depending on the field and the available options, you can choose a condition such as Between, Before, or a relative period such as This Year. This can be useful when you need a date range without adding a grouped year field.
- Choose grouping by Years when readers should select named years, compare annual totals, or use a year field in Rows, Columns, or Filters.
- Choose a date filter when the report needs a particular span of dates, such as a quarter or a custom start and end date.
- Choose a relative date filter when the report should follow the current date, such as a period labeled This Year. Review the filter after the calendar changes, since a relative filter can return a different set of records over time.
A calendar-year group is not necessarily the same as a fiscal year. If a company’s fiscal year starts in July, grouping dates by Years still groups January through December. For fiscal years, use a suitable date-range filter or add a fiscal-year column to the source data and use that field in the PivotTable.
Use year alongside months or quarters
The Grouping dialog lets you select more than one interval. Selecting Years and Months gives a hierarchy in which months are nested within years, so a January in one year is not confused with a January in another. Selecting Years and Quarters can provide a useful annual-to-quarterly view. The arrangement depends on the intervals selected and the PivotTable layout; expand or collapse items to show more or less detail.
If your goal is a simple annual summary, select Years alone. Extra grouping levels can make the report harder to scan and may add fields that are not needed. If you later want monthly detail, you can reopen the grouping options and add Months, provided the source dates can still be grouped.
Fix common date-grouping problems
If Excel reports that it cannot group the selection, check the source date column first. PivotTable grouping expects consistent date values. A column that looks like dates may contain text, blanks, errors, or a mixture of dates and text. Formatting text as a date does not convert it into a real date value.
- Check for blanks and errors. Find empty cells or error values in the source date column and correct them or handle them consistently before refreshing the PivotTable. A blank in the date field is a frequent cause of grouping trouble.
- Check for dates stored as text. A value such as 3/14/2024 may be text rather than a date serial. Convert the source values to real dates using an appropriate import or conversion method, taking care with regional date formats such as month/day versus day/month.
- Check for mixed values. A column with some true dates and some text entries such as Unknown may prevent grouping. Separate or correct those entries rather than assuming a date format will fix them.
- Refresh after source corrections. Once the source is fixed, click in the PivotTable and choose PivotTable Analyze > Refresh. If needed, try grouping again.
Repeated dates are not a problem: many transactions can share the same date. Date values that include times can also be valid date-time values, though a report grouped by days or years may display a broader period than the source timestamps. Confirm that your grouping level matches the analysis you intend.
Refresh, change, or remove the grouping
A PivotTable does not always update automatically when its source data changes. After adding rows or correcting dates, refresh the PivotTable so it reads the updated source. If new records fall outside the original source range, make sure the PivotTable’s source includes them; refreshing cannot include rows that are outside that range.
To adjust an existing grouping, right-click a grouped date or year label and choose Group, then change the selected intervals. To return to ungrouped dates, right-click a grouped item and choose Ungroup. The original date field remains in the source data, but the PivotTable returns to displaying individual date values. If grouping options are unavailable, inspect the source field for blanks, text, or errors.
Use a timeline or slicer instead
A timeline is a visual way to filter a PivotTable by date. Select the PivotTable, choose PivotTable Analyze > Insert Timeline, select the date field, and click OK. The timeline can be adjusted to show a period such as years, quarters, months, or days, depending on the control. It is useful on a dashboard where readers should interact with a date range rather than open a dropdown.
A slicer is a button-based filter for fields, including a grouped year field. Select the PivotTable and choose PivotTable Analyze > Insert Slicer, then choose the year field. A slicer can make the available year choices visible at a glance. For a straightforward report, the regular field dropdown is often simpler; timelines and slicers take up worksheet space and may need to be connected to other PivotTables separately.
Excel for Mac, web, and fiscal-year notes
In Excel for Mac, the ribbon and contextual tabs can look different, but the basic approach is to select a date in the PivotTable, use the grouping command or the context menu, and choose Years. Menu availability can vary by Excel build. If you cannot find the command, use the desktop version’s PivotTable tools and check that the selected item is an actual date.
Excel for the web can work with PivotTables, but some creation and grouping controls may differ from the Windows desktop app. If the web interface does not offer the needed grouping command, open the workbook in Excel for Microsoft 365 on Windows or Mac to create the year grouping, then save it. A fiscal-year label that starts in a month other than January is best represented explicitly in the source data rather than assumed to come from calendar-year grouping.
Frequently Asked Questions
How do I filter a PivotTable to just one year?
Group the date field by Years, then open the grouped year field’s dropdown, clear Select All, and select the year you want.
Can I filter by year without grouping?
Yes. Use the original date field’s dropdown and choose an appropriate option under Date Filters, such as Between or a relative period. Grouping is more convenient when you want a reusable year field or a year-by-year comparison.
Why can’t I group dates in my PivotTable?
The source date column may contain blanks, errors, or dates stored as text. Correct the source so the field contains consistent real dates, refresh the PivotTable, and try grouping again.
Does grouping by Years change my source dates?
No. Grouping changes how the PivotTable organizes the date field. It does not rewrite the underlying source values.
How do I show months under each year?
Right-click a date in the PivotTable, choose Group, and select both Years and Months. Then arrange or expand the grouped fields to show the desired detail.
How do I filter by a fiscal year?
Calendar-year grouping uses January through December. For a fiscal year that starts in another month, use a date range or add a fiscal-year field to the source data and use that field in the PivotTable.
Related
- Excel List Template Free? Where to Find and Use One
- Excel Long Number Format: Display More Digits Correctly
- Excel List of Weekday Dates: 10 Ways to Build a List
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Gantt Chart Template: Create and CustomizeAn Excel Gantt chart template lays out project tasks against dates, using horizontal bars to show when each…
- Excel Pivot Table: Remove Row TotalsTo remove the bottom grand-total row in an Excel PivotTable, select the table and choose PivotTable Design >…
- Excel Bar and Line Chart: Create and Format a Combo ChartTo put bars and a line in one Excel chart, select your category labels and data series, then…
- Excel Waterfall Chart Total Column: How to Add OneTo make a column a total in an Excel waterfall chart, select that individual column, right-click it, and…
- Excel Pivot Table Error Message: Causes, Checks, and FixesAn Excel PivotTable error is usually caused by a blank or invalid source-data header, an incorrect source range,…
- Excel Add Horizontal Line to Chart: Steps and TipsTo add a horizontal target or reference line to an Excel chart, add a data series whose value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.