In an Excel PivotTable, you can group a date field by month, quarter, year, or several of those levels at once. Add the date field to the PivotTable, right-click one of its dates, choose Group, select the time periods you need, and click OK.
How to group dates in an Excel PivotTable
These steps are for Excel for Microsoft 365 on Windows. First, make sure your source data has a column of dates and a separate column for the values you want to summarize. Create a PivotTable by selecting a cell in the data and choosing Insert > PivotTable. Choose where to place it, then click OK.
- In the PivotTable Fields pane, drag the date field into Rows. Drag a numeric field, such as Sales, into Values.
- Click a date shown in the PivotTable, then right-click and choose Group.
- In the Grouping dialog, select Months, Quarters, Years, or the combination you want. Click OK.
- Move the resulting grouped fields between Rows, Columns, and Filters if you want a different report layout.
The Group command works on dates already displayed in the PivotTable; it does not convert text that merely looks like a date. If Group is unavailable or Excel reports that it cannot group the selection, check the source column for text values, blanks, or other inconsistent entries before changing the PivotTable.
What date grouping does
Grouping gives a PivotTable a way to summarize individual dates at a broader time scale without requiring you to rewrite the source data. For example, a table with one sales record per day can show monthly totals, quarterly totals, or yearly totals. Excel creates additional grouped fields in the PivotTable, which you can use to organize the report.
The choice of grouping levels affects what the totals mean. If you select Months alone, Excel combines all January records together, all February records together, and so on. If the source covers several years, January 2024 and January 2025 contribute to the same January total. That is useful for comparing typical monthly patterns, but not for seeing each year separately.
To keep periods distinct, select Years and Months together. The PivotTable can then show each year with its own months beneath it. The same principle applies to quarters: select Years and Quarters to distinguish, for example, the first quarter of 2024 from the first quarter of 2025. Selecting only Years produces annual totals.
Prepare the source data first
A PivotTable can group a date field only when Excel recognizes the field as dates. A cell that displays 1/5/2025 might still contain text, particularly if data was imported from a website or a system that uses a different date format. Click a source cell and inspect its value and formatting; changing the number format alone does not turn text into a real date.
Keep the source in a consistent, tabular layout:
- Use one header row with a clear heading such as Order Date.
- Put one record on each row and keep the date for that record in the same column.
- Do not mix dates with labels such as Pending or Not provided in the date column.
- Check for empty cells, error values, and dates stored as text, especially in newly imported rows.
- Avoid merged cells and blank separator rows within the data range.
After correcting the source, refresh the PivotTable. Right-click inside it and choose Refresh, or use PivotTable Analyze > Refresh. If the original PivotTable source range does not include newly added records, update the source through PivotTable Analyze > Change Data Source, or use an Excel table as the source so added rows can be included more easily.
Choose the right month, quarter, and year layout
Think about the question the report should answer before selecting grouping levels. A single level is often clearest for a high-level summary; multiple levels are useful when readers need to drill into detail.
- Months only: Compares recurring months across the full source period. Use it when the year is irrelevant, and label the report so readers know the years are combined.
- Years and Months: Shows a chronological monthly trend with each year kept separate. This is usually the best layout for comparing month-to-month performance across multiple years.
- Quarters only: Combines all first quarters, all second quarters, and so on across years. This can reveal recurring seasonal patterns, but it does not show each year’s quarterly result separately.
- Years and Quarters: Shows each year’s quarterly totals, a common choice for management summaries.
- Years only: Rolls all dates in each calendar year into a single total.
For a hierarchy, place Years above Quarters or Months in Rows. To show months as columns instead, drag the Months field to Columns and keep Years in Rows or Filters. The Values area determines what is calculated, such as Sum of Sales or Count of Orders. If Excel displays Count where you expected Sum, inspect the values column for text or blanks; a field containing numbers stored as text may not summarize as expected.
Example: monthly sales across two years
Suppose a sales list contains transaction dates from January 2024 through December 2025, along with an Amount for each transaction. Add Transaction Date to Rows and Amount to Values. Group the date field by Years and Months. The resulting report can show 2024 with January through December beneath it, followed by 2025 with its own months.
For instance, if January 2024 has transactions of 120 and 80, its monthly total is 200. If January 2025 has transactions of 150 and 100, that month’s total is 250. Grouping by Months alone would combine those four transactions into a January total of 450. Grouping by Years and Months preserves the separate totals of 200 and 250. The underlying transactions remain unchanged; the PivotTable is aggregating them at a different level.
For a quarterly report, group by Years and Quarters instead. Excel uses calendar quarters: Q1 is January through March, Q2 is April through June, Q3 is July through September, and Q4 is October through December. If a business uses a fiscal year that starts in a different month, the standard date grouping does not automatically redefine those quarter boundaries.
Common grouping problems and fixes
The most familiar error is Excel’s message that it cannot group the selection. Start by checking every cell in the source date column, not just the visible rows in the PivotTable. One text entry, blank, or error among otherwise valid dates can interfere with grouping. Correct the source consistently, refresh, and try Group again.
- Group is disabled: Confirm that the selected PivotTable field contains real dates and that you right-clicked an item from that field, rather than a value or heading.
- Some dates appear under blank: Look for missing dates or values Excel cannot interpret in the source column.
- Months appear combined across years: Add Years as a grouping level; Months alone intentionally aggregates matching month names.
- Quarters do not match a fiscal calendar: Create fiscal year and fiscal quarter fields in the source data, or use a calendar table designed for the reporting calendar.
- New records are missing: Refresh the PivotTable and verify its source range includes the new rows.
There is also an important source-type limitation: PivotTables built from the Data Model or certain OLAP sources do not support ordinary date grouping in the same way as a PivotTable built from a regular worksheet range. For those reports, include calendar attributes such as Year, Month, and Quarter in the model or a related calendar table, then use those fields in the PivotTable.
Ungroup dates and use alternatives
To reverse a grouping, right-click a grouped date or period in the PivotTable and choose Ungroup. This removes the grouping and returns the date field to its ungrouped form; it does not delete or alter the source dates. You can then group the field differently. If you only need to change which levels are shown, adjust the grouped fields in the PivotTable layout rather than rebuilding the source.
For recurring reports, or when the calendar is not a standard calendar year, explicit date attributes can be more dependable than interactive grouping. Add fields such as Year, Month Name, Month Number, Fiscal Year, or Fiscal Quarter to the source table, or maintain them in a calendar table for a Data Model. Month Number is useful for sorting month names chronologically: alphabetical sorting would put April before February. Keep the numeric sort key available even if the finished report displays only readable month labels.
Another option is to use a formula-based summary or a chart built from a prepared summary table. Those approaches can provide more control over custom periods, but they require maintaining the summary logic. A PivotTable’s main advantage is that readers can rearrange fields and summarize updated source records without manually creating each total.
Frequently Asked Questions
Can an Excel PivotTable show months, quarters, and years at the same time?
Yes. Right-click a date in the PivotTable, choose Group, and select the relevant time units in the dialog. Use a hierarchy such as Years, Quarters, and Months when readers need to move from an annual view to finer detail.
Why does grouping by month combine different years?
Months alone are recurring categories, so January from every year is grouped together. Select Years as well as Months to keep each year’s January, February, and other months separate.
Can I group dates into fiscal quarters?
The built-in Quarters option uses calendar quarters. For a fiscal year with different boundaries, use fiscal period fields in the source or a calendar table rather than assuming Excel’s standard grouping will shift the quarter start.
How do I sort month names in calendar order?
Use the grouped date field when possible, since it is based on dates. If you created month names as ordinary text fields, include a month-number field and sort by that number so the sequence runs January through December rather than alphabetically.
Does grouping change my original data?
No. Grouping changes how the PivotTable summarizes and displays its date field. The source records remain in place, and you can use Ungroup to remove the PivotTable grouping.
Related
- Epoxy Resin Sales Inventory Spreadsheet: Setup Guide
- How to Make a Poster in PowerPoint: A Practical Guide
- Instrument Inventory Spreadsheet: Setup and Key Fields
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Area Chart: How to Create and Format OneTo create an area chart in Excel for Microsoft 365 on Windows, select your data, then choose Insert…
- Excel Pivot Chart Colors Keep Changing? Fix the CauseExcel PivotChart colors usually change because filtering, refreshing, or rearranging fields changes which series are displayed or their…
- Excel Pivot Table Can’t Group Dates? Fixes That WorkYes—Excel can group dates in a PivotTable, but it needs the source column to contain valid date values…
- Excel Pivot Table Org Chart: Options and WorkaroundsAn Excel PivotTable cannot turn employee records into a graphical organization chart with connected boxes. It can summarize…
- Make a Pivot Wheel Chart in Excel: Steps and OptionsYes. In Excel for Microsoft 365, make a PivotTable, insert a PivotChart, and select a radial style such…
- Create a Pivot Table in Excel From Multiple Sheets?To create a PivotTable from multiple sheets in Excel for Microsoft 365 on Windows, combine the sheets’ data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.