How to Group Dates by Year in Excel with a PivotTable

To group dates into years in Excel, place the date field in a PivotTable, right-click any date in the table, choose Group, select Years, and click OK. This turns individual dates into calendar-year groups for a clearer summary. Before you start, make sure the source column contains real Excel dates rather than text that only looks like dates.

Grouping is useful when you want to compare yearly sales, counts, expenses, or other totals without manually sorting and combining every date. If you do not need a PivotTable, a helper column containing each date’s year is another straightforward option; both methods are covered below.

What You’ll Need

An Excel workbook with a date column and, ideally, another column to summarize, such as sales, quantities, or category names.
A consistent header row, with one record per row and no merged cells in the source data.
Excel for Microsoft 365 on Windows for the ribbon paths below. Excel for Mac has similar PivotTable grouping controls; if the command is unavailable in Excel for the web, open the workbook in the desktop app.

Step-by-Step

Check that the source dates are valid. Click a few cells in the date column. On the Home tab, check the Number Format list: valid dates can be displayed in a date format, and changing the format to General typically shows a serial number. A cell that remains ordinary text may not be groupable. Also check for blank cells, errors, or date-like text mixed into the column; these can block PivotTable grouping. Do not change the date values themselves yet—first identify any inconsistent records.
Make the source data easy to use. Give every column a unique header, for example Date, Region, and Amount. Keep each record on one row and avoid blank rows inside the data. For an expanding dataset, select a cell in the range and press Ctrl+T to turn it into an Excel Table; confirm whether the table has headers. A Table is convenient because new rows can be included in the PivotTable source when you refresh it.
Insert a PivotTable. Click a cell in the source data, then choose Insert > PivotTable. Confirm the Table/Range and choose New Worksheet, then click OK. In the PivotTable Fields pane, drag the Date field to Rows. Drag a numeric field such as Amount to Values. Excel usually summarizes numbers by Sum; if it shows Count instead, check that the source values are numeric. You can also put a category field in Columns or Filters if you want to compare yearly totals by category.
Open the date grouping controls. In the PivotTable, right-click one of the date entries in the row labels and choose Group. Use an actual date entry, not a total or a blank. If you put Date in the Columns area instead, right-click one of the date labels there. You can also select a date label and use PivotTable Analyze > Group > Group Selection on the Windows ribbon.
Select Years in the Grouping dialog. In the list of time units, select Years. Clear other selected units, such as Months or Quarters, if you want one group for each calendar year rather than nested year-and-month groups. Leave Starting at and Ending at alone unless you have a specific reason to restrict the date range. Click OK. The PivotTable will replace the individual dates with year labels.
Check the resulting summary. Confirm that the year labels match the dates in your source and that the Values column contains the expected sum or count. If you grouped by Years and another unit, expand or collapse the year items using the small plus or minus buttons beside the labels. For a simple annual report, keep Years in Rows and remove any unwanted lower-level date fields from the layout.
Refresh after changing the source. If you correct dates or add records, right-click inside the PivotTable and choose Refresh. When the PivotTable uses a fixed cell range, newly added rows may be outside its source; choose PivotTable Analyze > Change Data Source to update the range. If the source is an Excel Table, adding rows to the Table and refreshing is usually simpler.
Use a helper column if you do not want a PivotTable. With dates in A2:A100 and a new Year header in B1, enter =YEAR(A2) in B2, then fill the formula down. If A2 contains 6/15/2024 as a real date, the formula returns 2024. You can then sort or filter by the Year column, or use it as a field in a PivotTable. This method adds a year value to every source row; it does not itself combine or total records.

On Excel for Mac, the basic process is the same: create or open the PivotTable, select a date label, and use the contextual Group command. Ribbon tab names or placement can differ slightly by build. In Excel for the web, if you cannot find a Group command for the PivotTable date field, open the file in Excel for Microsoft 365 on Windows or Mac and group it there.

Common Mistakes

Dates are stored as text. A value such as 2024-06-15 may look like a date but be text, especially if it was imported. Applying a date number format does not convert text into a true date. Convert the source values, then refresh or recreate the PivotTable.
A blank, error, or non-date value is in the date field. One inconsistent entry can prevent Excel from grouping the whole field. Find and correct or remove the problem record; do not just hide its row in the PivotTable.
Months remain visible. The Group dialog may have more than one interval selected. Select Years and clear Months, Quarters, or other intervals when you want only a single year level.
The result is formatted as a date rather than a year. A PivotTable may display a date field in an unexpected format, or a helper formula may have inherited date formatting. Select the year values and choose Home > Number Format > General or Number.
The totals do not match expectations. Check that the Values field uses Sum rather than Count, that amounts are numeric, and that the PivotTable includes the full source range. Grouping changes how dates are arranged; it does not repair missing or duplicated records.

Tips

Use a copy of the workbook or preserve the original source data before making cleanup changes, especially when dates came from an external system.
Format the year labels and totals after grouping. Number formats affect how values appear, while grouping controls which date periods are combined; these are different settings.
For a comparison by year and category, put Years in Rows, a category such as Region in Columns, and the measure in Values. Keep the layout simple if the report will be printed or shared.
Use calendar-year grouping for January-to-December reporting. If your organization uses a fiscal year that starts in another month, a calendar-year grouping will not automatically create the required fiscal periods. Add a separate fiscal-year field using rules that match your reporting calendar.
Rename the generated year field or the Values heading if clearer labels will help readers. A descriptive PivotTable title also makes it easier to distinguish this summary from the underlying daily records.
Remember that date grouping is a PivotTable feature, not a permanent change to the source date column. The original dates remain available in the source data and can be shown again by ungrouping.

Troubleshooting

Group is unavailable or Excel reports that the selection cannot be grouped: Check the entire source date column, not only the visible PivotTable labels. Look for blanks, errors, text, and mixed formats. Correct the source values, refresh the PivotTable, and try Group again. If it still fails, create a new PivotTable after cleaning the source; an old PivotTable can retain an unsuitable source range.

Excel groups dates automatically and the layout looks different: Some Excel builds automatically place date fields into a hierarchy, such as Years, Quarters, and Months. In the PivotTable Fields pane, inspect the fields that were added and remove levels you do not need. You can also right-click a date label and use Group or Ungroup to change the arrangement. The underlying dates are not deleted by this automatic grouping.

A new year or recently added dates are missing: Refresh the PivotTable. If the new records are still absent, check the source range through PivotTable Analyze > Change Data Source. A fixed range does not necessarily expand to include rows typed below it. Converting the source to an Excel Table helps the source expand as records are added, but you still need to refresh the PivotTable to update its results.

The helper column shows a number that looks like a date: Select the helper cells and choose Home > Number Format > General or Number. The formula returns a year number; a date format can make that number display in a confusing way. If the result is an error, verify that the referenced source cell contains a valid Excel date.

The year totals are incomplete or unexpectedly small: Check filters on the PivotTable and confirm that all source rows are included. Also inspect the source for amounts stored as text, duplicate records, or dates that fall outside the years you expected. Clear filters temporarily to see whether records were excluded from the report.

Frequently Asked Questions

Can I group dates by year without a PivotTable?

Yes. Add a helper column and use =YEAR(A2) when the date is in A2, then fill the formula down. Sort, filter, or summarize using that year column. A PivotTable is more convenient when you also want Excel to total or count records by year.

Why does Excel say it cannot group that selection?

The source field usually contains at least one blank, text value, error, or entry that is not a valid date. Find and fix the inconsistent source record, refresh the PivotTable, and try grouping again.

Can I show only the year and not the month?

Yes. In the Group dialog, select Years and clear Months and any other intervals. If Excel has already created multiple date levels, remove the extra levels from the PivotTable layout or ungroup and group again with Years alone.

Does grouping change the dates in my original data?

No. Grouping changes the PivotTable’s display and summary. It does not replace the daily dates in the source column. The helper-column method adds a separate year value but also leaves the original date intact.

Can I group dates by fiscal year instead?

Not by selecting Years alone: that option groups by calendar year. For a fiscal year, add a separate field that assigns each date to the correct fiscal year based on your start month, then summarize by that field. Confirm the fiscal-year labels against your organization’s reporting rules.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.