Group Dates into Quarters in Excel: A Step-by-Step Guide

To group dates into quarters in Excel, put the date field in a PivotTable, right-click a date, choose Group, then select Quarters and Years. This summarizes dates into calendar quarters while keeping the same quarter in different years separate. Before you start, make sure the source column contains real Excel dates, not text that only looks like dates. If you need a quarter label in the source data or use a fiscal calendar, a helper-column formula is another practical option.

What You’ll Need

A worksheet with one row per record and a column containing the dates you want to group.
Optional: a numeric field, such as sales or units, if you want the PivotTable to summarize amounts by quarter.
Microsoft Excel for Microsoft 365 on Windows for the ribbon paths below. The general PivotTable method is similar on Mac; Excel for the web may have fewer grouping controls, so use the desktop app if Group is unavailable.

Keep a header row above the data, and avoid merged cells or completely blank rows inside the source range. You do not need to rearrange or pre-sort the dates. Excel will group the dates in the PivotTable without changing the original values in your worksheet.

Step-by-Step

Check the date column. Click a few date cells and look at the formula bar and number format. A genuine date is stored as a number by Excel, even when it is displayed as, for example, 5/14/2026. Change the display using Home > Number Format if needed; changing how a date looks does not change its underlying date value. If the cells are text, convert them before continuing. One quick check is to temporarily format a sample cell as General: a real date will display as a serial number, while text will remain text.
Select your source data. Click any cell within the data range, including its header row. If you have a simple range, Excel will usually detect the contiguous data automatically. If the data is already an Excel table, click inside the table instead. A table is useful when new rows are added regularly because it can expand to include them.
Insert a PivotTable. On Windows, choose Insert > PivotTable. Check that the proposed table or range includes all your records and headers. Choose New Worksheet to keep the summary separate, then select OK. If you need the PivotTable on the current sheet, choose Existing Worksheet and specify an empty destination cell so the report does not overwrite source data.
Add the date field to Rows. In the PivotTable Fields pane, drag the date field into Rows. If you want totals, drag a numeric field such as Sales into Values. Excel may automatically display date levels such as Years, Quarters, or Months. If it already gives you the quarter and year levels you need, inspect them and continue to the formatting step. Otherwise, remove any unwanted date levels from the layout and use manual grouping.
Open the grouping command. In the PivotTable, right-click one of the date entries, not a total or a blank cell, and choose Group. If you are using a Mac, Control-click or right-click the date entry; the available context-menu options can vary slightly by Excel version. If Group is missing or unavailable, see Troubleshooting below because the source field may contain text, blanks, or errors.
Select quarters and years. In the Grouping dialog, select Quarters and Years. Selecting both matters when your data covers more than one calendar year: the report can then show, for example, 2025 Q1 and 2026 Q1 as separate periods. If you select Quarters without Years, Excel may combine every first quarter together, every second quarter together, and so on. Clear Months if you want quarter-level summaries rather than month-level detail. Select OK.
Review the result and arrange the fields. Check that the quarter entries appear in chronological order and that the year level is visible. In the Fields pane, you can move Years above Quarters in Rows to show a year with its quarters underneath. If Excel has placed a field in Columns instead, drag it to Rows or the other way around to suit the report. Use the expand and collapse buttons beside a year to show or hide its quarters.
Format and verify the summary. If your Values field is showing Count instead of Sum, right-click a value, choose Summarize Values By, and select Sum. This can happen when some entries in the source amount column are stored as text. Apply a suitable number format with Home > Number Format, and compare the PivotTable’s grand total with the source data total. The grouping changes how dates are summarized; it does not alter the source dates.
Refresh when the source changes. After adding or correcting records, right-click inside the PivotTable and choose Refresh. If you added rows outside a fixed source range, the PivotTable may not include them. Update its source using PivotTable Analyze > Change Data Source, or use an Excel table as the source so it can expand as records are added.

Optional helper column for a reusable quarter label: You can create quarter text in the source data instead of relying on PivotTable date grouping. With dates in A2:A100 and a header in A1, enter this in B2, then fill it down alongside the records: =YEAR(A2)&" Q"&ROUNDUP(MONTH(A2)/3,0). For a date of May 14, 2026, the result is 2026 Q2. The year comes first so labels sort in chronological order as text. You can use this new field in a PivotTable’s Rows area, but remember that it is a text label rather than an actual date.

For fiscal quarters that do not start in January, Excel’s built-in Quarters grouping still follows calendar quarters. With dates in A2:A100 and the fiscal-year starting month as a number from 1 to 12 in D1, enter this in B2 and fill down: ="Q"&(INT(MOD(MONTH(A2)-$D$1,12)/3)+1). For a July start, put 7 in D1; a January date will be labelled Q3. Add a separate fiscal-year field if the report needs to distinguish fiscal years, and confirm your organization’s fiscal-year naming convention before presenting the results.

Common Mistakes

Grouping quarters without years. Q1 occurs every year, so grouping by quarter alone can combine different years into one total. Select both Quarters and Years when the date range spans multiple years.
Assuming a displayed date is a real date. A value such as 2026-05-14 may be text imported from another system. Formatting it as a date does not necessarily convert it. Convert the source values to actual dates before making the PivotTable.
Using calendar quarters for a fiscal report. The PivotTable Group command does not reinterpret calendar dates using a custom fiscal year. Use a fiscal-quarter helper field when the organization’s year starts in a month other than January.
Expecting the source dates to change. Grouping affects the PivotTable view, not the original date column. If another worksheet or chart needs quarter labels, add a helper column or use the grouped PivotTable field.
Leaving an incomplete source range. A PivotTable built from a range may omit later rows added below it. Check the source range or convert the data to a table before creating the report.

Tips

Use a consistent date format in the source column, even though Excel stores valid dates as numbers. A consistent display makes outliers and accidental text entries easier to spot.
Keep the original date field in the source, even if you add a quarter helper column. Exact dates remain useful for filtering, checking individual records, and creating other summaries.
Use both a year and quarter in labels when exporting a report. A label such as 2026 Q2 is clearer than Q2 by itself and reduces the chance of confusing periods from different years.
For a chart, build the chart from the PivotTable after confirming that the year and quarter levels are arranged correctly. A chart based on labels alone may sort text unexpectedly if labels do not begin with the year.
If your data is an Excel table, give the date and amount columns clear, unique headers. PivotTable field names come from those headers, so descriptive names make the Fields pane easier to use.
Refresh the PivotTable after correcting source dates, and check its grand total again. A report can look neatly grouped while still omitting newly added rows or containing a misclassified record.

Troubleshooting

Group is greyed out or Excel says it cannot group the selection: Inspect the entire source date column, not only the date currently visible in the PivotTable. Look for blank cells, text values, errors, or a mixture of valid dates and non-dates. Fill or remove unintended blanks, correct errors, convert text dates to actual dates, then refresh the PivotTable and try Group again. If the field includes a formula, check that every row returns a valid date rather than an empty string.

Dates are grouped by months or years automatically: Excel may have created multiple date levels when you added the field. In the PivotTable Fields pane, remove levels you do not need, or use the Group dialog to select Quarters and Years. If the display is too detailed, collapse the year or quarter level rather than deleting the source date field.

All Q1 entries appear in a single total: Edit the grouping and select Years as well as Quarters, or add a year field to Rows. If you used a helper column, check that the label includes the year; a formula that returns only Q1, Q2, Q3, or Q4 cannot distinguish the same quarter in different years.

The result is off by a month or a day: Check whether imported values include times or were interpreted using a different date order, such as day/month versus month/day. A valid date-time can still be grouped by date, but an ambiguous text date may have been converted incorrectly. Verify questionable source values against the original records before relying on the summary.

The new records do not appear: Refresh first. If the report still excludes them, inspect PivotTable Analyze > Change Data Source and expand the source range, or convert the source to a table and base the PivotTable on that table. Refresh after changing the source.

The Group command is unavailable in Excel for the web: Open the workbook in the desktop Excel app and use the right-click Group command there. The web app’s PivotTable features can differ from the Windows desktop version, and the exact options depend on the workbook and current Excel build.

Frequently Asked Questions

Can Excel group dates into quarters without a PivotTable?
Yes. Add a helper column with a quarter-label formula, then sort, filter, or summarize using that column. The PivotTable method is usually more convenient for interactive totals and filtering.

Why does Excel show Q1 through Q4 but no year?
The grouping may include Quarters but not Years, or the year level may be hidden. Edit the grouping to select Years and Quarters, then check the Rows area for both levels.

Can I group by fiscal quarters?
Not directly with the standard PivotTable Quarters option, which uses calendar quarters. Create a helper field based on the fiscal start month, and include a fiscal-year field if you need separate year totals.

Will grouping change my original dates?
No. Grouping changes the PivotTable summary only. The dates in the source worksheet remain as they were.

How do I undo date grouping?
Right-click a grouped date or quarter field in the PivotTable and choose Ungroup. If the command is unavailable, click a grouped item rather than a total cell, or remove the grouped fields from the PivotTable and add the original date field again.

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.