Excel Pivot Table: Group Dates by Year, Quarter & Month

Charts & pivot tables · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, put a real date field in a PivotTable, right-click one of its dates, choose Group, then select Years, Quarters, and Months. Excel can also group dates automatically; if grouping fails, the source column usually contains text, blanks, or errors rather than valid Excel dates.

What date grouping does

A PivotTable normally treats each distinct date as a separate item. If a sales table has transactions on 2 January, 3 January, and 4 January, dragging the transaction date into Rows can produce three row labels. Grouping combines those individual dates into broader periods, so the same report can show monthly, quarterly, or yearly totals without changing the underlying transaction records.

For example, a PivotTable that sums sales can show one total for each month, a total for each quarter, and a total for each year. Grouping changes how dates are arranged in the report; it does not rewrite the dates in the source data. You can expand or collapse the grouped levels to move between detail and summary.

Excel may automatically create a date hierarchy when you add a date field to a PivotTable. Depending on the workbook and Excel settings, you may see separate Years, Quarters, and Months fields rather than one field of daily dates. If the hierarchy is not created, manual grouping provides the same kind of summary.

Group dates into years, quarters, and months

Start with a source table that has one record per transaction and a column containing the transaction date. Create or select the PivotTable, then follow these steps in Excel for Microsoft 365 on Windows:

  1. Drag the date field into the Rows area. Add the value you want to summarize, such as Sales, to Values.
  2. In the PivotTable, right-click any date label and select Group. You can also select a date item and use PivotTable Analyze > Group Field.
  3. In the Grouping dialog box, select Years, Quarters, and Months. Clear any interval you do not need.
  4. If the dialog offers Starting at and Ending at values, check that the range covers the dates you intend to report.
  5. Select OK. Excel adds grouped levels that you can arrange in the Rows area and use to expand or collapse the report.

The right-click Group command is also available in Excel for Mac, though ribbon layout and labels can differ by version. If you are using Excel for the web and cannot find the grouping command, open the workbook in the desktop app to perform the grouping. Once the PivotTable is grouped, check that the displayed periods and totals match the reporting range you need.

How Excel interprets the date field

Excel stores dates as numeric serial values and displays them using a date format. The visible text 3/4/2025, for instance, is not enough by itself to prove that a cell contains a date: it could be a true date value or text that merely looks like one. PivotTable grouping requires date values Excel can interpret consistently. Regional settings also affect how ambiguous text such as 4/5/2025 is read: it may mean April 5 or May 4.

When you group by Years, Quarters, and Months, Excel uses calendar periods. A quarter is three calendar months: January through March is Q1, April through June is Q2, July through September is Q3, and October through December is Q4. The grouping does not automatically apply a company’s fiscal calendar. Month names may appear under each year, so January in one year remains distinct from January in another.

For a useful summary, put the date levels in a sensible order. Years above Quarters and Quarters above Months gives a natural drill-down from annual to monthly totals. If you only need a monthly trend across the full data set, use Months without a year level carefully: months from different years can be combined into a single January, February, and so on. Include Years when you need each month to remain tied to its own year.

Automatic grouping and manual grouping

In some Microsoft 365 PivotTables, adding a date field creates a hierarchy automatically, commonly with Years, Quarters, and Months. That can save steps, but it can also surprise someone who expected to see every individual date. Look in the PivotTable Fields pane for generated date levels, and remove or rearrange them as needed. To return to individual dates, use the original date field or ungroup the grouped field.

If automatic grouping is undesirable, Excel for Windows has a setting under File > Options > Data named Disable automatic grouping of Date/Time columns in PivotTables. The wording and availability can vary with Excel version. Turning off automatic grouping changes the default behavior for PivotTables; it does not prevent manual grouping when the source dates are valid.

Manual grouping is useful when you want specific periods or want to confirm exactly which units are being used. Right-click a grouped date label and choose Ungroup to undo the date grouping. This removes the grouping from the PivotTable, not the source dates. You can then group again with different intervals.

Fix dates that will not group

If Excel reports that it cannot group that selection, or the Group command is unavailable, inspect the complete source date column rather than only the visible PivotTable rows. One blank, text value, or error in the field can prevent grouping. A source range may also include a heading or stray cell that Excel interprets as part of the date field.

  • Check for blanks: Fill missing dates with the correct date where known, or remove records that genuinely should not be included. Do not invent dates merely to make grouping work.
  • Check for text dates: Convert text to real dates consistently. Text to Columns or a carefully checked conversion may help, but confirm the result because day and month order can be ambiguous.
  • Check for errors and mixed values: Correct error cells and make sure every nonblank entry in the field is a date, rather than a note such as Pending or N/A.
  • Check the source range: Confirm that the PivotTable includes the intended rows and that the column has one clear date heading.
  • Refresh after corrections: Use PivotTable Analyze > Refresh in Excel for Windows. Then try grouping again.

Formatting a text value to look like a date does not convert it into an underlying date value. Likewise, refreshing a PivotTable does not repair invalid source data; refresh only updates the report from the source. After converting dates, verify a few cells in the formula bar or temporarily apply a different date format to confirm that Excel recognizes them.

Calendar quarters versus fiscal quarters

Excel’s standard date grouping uses calendar quarters. If a business year begins in April, its fiscal Q1 runs from April through June, not January through March. Selecting Quarters in the Grouping dialog does not by itself change the quarter start month or reliably label a fiscal year. For a fiscal report, add helper columns to the source data and build the PivotTable from those fields instead.

For example, suppose transaction dates are in A2:A100. To calculate a quarter number for a fiscal year that starts in April, enter this in a helper column beside the first record and fill it down:

=ROUNDUP(MOD(MONTH(A2)-4,12)/3,0)+1

For a date of 15 April 2025, the result is 1; for 10 January 2025, the result is 4. This labels fiscal quarters but does not identify the fiscal year, so add a separate fiscal-year field if the report spans more than one year. Decide whether your organization names a fiscal year by its starting year or ending year, then label it consistently. Add the fiscal year and quarter fields to the PivotTable and sort the quarter labels in the intended order.

A helper field can also be useful if your reporting calendar uses 4-4-5 periods, custom season boundaries, or a week-based year. Those calendars are not the same as standard calendar months and quarters. Define the reporting period in the source data or a calendar table so that every transaction is assigned according to the business rule.

Choose the right layout and check the results

Use the simplest level that answers the reporting question. A year-over-year overview may need Years only; a quarterly performance report needs Years and Quarters; an operational review may need Months or individual dates. Adding every level can make a report long, but keeping the hierarchy available lets readers expand detail when useful.

After grouping, compare PivotTable totals with the ungrouped report or a known total. Check the first and last dates, especially if the source includes partial years, and verify that dates near a year boundary fall into the expected period. If you filter the PivotTable, remember that a filter can hide periods without removing the corresponding records from the source. For repeatable reporting, keep the source dates clean and refresh the PivotTable whenever the source data changes.

Grouping is distinct from sorting and filtering. Sorting changes the order of date labels; filtering limits which labels are displayed; grouping combines individual dates into period levels. A PivotTable can use all three, but each has a different effect. For example, grouping dates by month and then filtering to one year is different from grouping all dates by month alone, which may combine January across every year.

Frequently Asked Questions

Can an Excel PivotTable show year, quarter, and month at the same time?
Yes. Group the date field and select Years, Quarters, and Months. Arrange those levels in the Rows area in that order if you want to drill from annual totals into quarters and months.

Why does Excel say it cannot group that selection?
The source date field commonly contains at least one blank, text entry, or error, or the source range includes an unintended value. Check the entire source column, correct inconsistent entries, refresh the PivotTable, and try again.

How do I stop Excel from grouping dates automatically?
In Excel for Windows, check File > Options > Data for Disable automatic grouping of Date/Time columns in PivotTables. The setting can vary by version. You can also ungroup an individual PivotTable field without changing the source data.

Does Excel group dates into fiscal quarters?
Standard grouping uses calendar quarters, not a company-specific fiscal calendar. For fiscal quarters, create fiscal year and quarter fields in the source data or a calendar table, then use those fields in the PivotTable.

Can I group dates by month without combining different years?
Yes. Include Years as well as Months in the grouping and keep the year level in the PivotTable. If you use only Months across a multi-year data set, January from each year can be summarized together.

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.