How to Group Dates in Excel by Month, Quarter, or Year

To group dates in Excel, create a PivotTable, place the date field in Rows, then right-click a date in the PivotTable and choose Group. Select Months, Quarters, or Years (and select Years alongside Months or Quarters when your data spans multiple years), then choose OK. This turns a long list of individual dates into a summary you can scan and analyze.

The steps below are for Microsoft Excel for Microsoft 365 on Windows. Grouping works on actual Excel dates, not text that merely looks like a date. Before starting, check the date column and remove blank or invalid entries; these can prevent Excel from grouping the field.

What You’ll Need

A worksheet with a header row and one date column containing valid Excel dates.
At least one other field to summarize or compare, such as sales, hours, orders, or a category.
A copy of your source data with blank rows removed. Converting the data to an Excel Table first is helpful if you expect to add more records later.

Step-by-Step

Check and prepare the source data. Arrange the records in columns with one header per column, such as Date, Region, and Sales. Each row should represent one record. Make sure every populated cell in the date column contains a real date, and remove completely blank rows from the middle of the data. A date format such as 3/14/2026 is fine, but changing a cell’s number format alone does not convert text into a real date. If you want future rows included more easily, click in the data and choose Insert > Table, confirm that My table has headers is selected, and click OK.
Insert a PivotTable. Click a cell in the source data or Table. On the ribbon, choose Insert > PivotTable. Check that the proposed table or range includes all of your records, choose whether to place the PivotTable on a new worksheet or an existing one, and select OK. A new worksheet is usually easiest while you are learning, because it keeps the summary separate from the source.
Build a basic summary. In the PivotTable Fields pane, drag the date field to Rows. Drag a numeric field, such as Sales or Hours, to Values. Excel will usually summarize a numeric field by Sum; if it shows Count instead, check that the source values are numeric. You can also drag a category such as Region to Columns or place it above the date field in Rows to compare categories over time.
Open the date grouping options. In the PivotTable itself, right-click one of the individual date labels. Choose Group from the context menu. Make sure you right-click a date item in the PivotTable, not a date in the original data. If Excel has already added a date hierarchy with fields such as Years, Quarters, and Months, you may already be looking at grouped dates; use those fields or right-click a date and choose Ungroup before setting the grouping yourself.
Choose the time periods you need. In the Grouping dialog, select Months to summarize by month, Quarters to summarize by quarter, or Years for annual totals. To see months or quarters separately within each year, select Years as well as Months or Quarters. This is important when your source covers more than one year: grouping by Months alone combines every January, every February, and so on. Select Days if you need daily buckets; you can set the number of days per group. Click OK to apply the selections.
Check the summary and arrange its layout. Review the row labels and values to confirm that the periods make sense. If you selected Years and Months, use the expand and collapse buttons beside the year labels to show or hide individual months. Drag fields in the PivotTable Fields pane to reorder them. For example, placing Region above Years in Rows gives each region its own time summary; placing Region in Columns shows regions side by side.
Format the results and refresh when needed. Right-click a value in the PivotTable, choose Number Format, and select a suitable format such as Currency or Number. This formats the summary without changing the source values. When source records change, right-click inside the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. If your source is an Excel Table, new rows added to the Table can be included when you refresh. If the PivotTable uses a fixed cell range, check that newly added rows are inside its source range.

On Excel for Mac, the overall process is similar: create the PivotTable from the Insert tab, then Control-click or right-click a date item in the PivotTable and look for Group. Ribbon labels and dialog details can differ slightly by Excel version. Excel for the web has more limited PivotTable editing in some cases; if you cannot find the grouping command, open the workbook in the desktop app.

Common Mistakes

Grouping months without years. If records cover several years, the January group can combine January 2025, January 2026, and other years. Select both Months and Years to keep each year’s months distinct.
Assuming date-looking text is a date. Text such as “2026-03-14” may look correct but still be stored as text, especially after importing a CSV. Applying a date format does not fix the underlying value.
Leaving blanks or inconsistent entries in the date column. One blank, text value, or error among the source dates can stop grouping. Check the entire source column, including rows that are currently hidden by a filter.
Using the wrong date field. A workbook may contain both Order Date and Ship Date. Confirm that the field in Rows is the one you intend to analyze before grouping.
Expecting the source rows to be rearranged. PivotTable grouping summarizes the dates in the PivotTable; it does not merge, rewrite, or sort the original records into monthly blocks.

Tips

Keep both detail and summary. A PivotTable leaves the source records unchanged, so you can use the same clean data for other summaries or filters.
Choose a clear date hierarchy. Years above Months is easy to browse, while Years above Quarters suits high-level reporting. Avoid adding every level unless the extra detail helps answer a question.
Use a Table for growing data. A Table expands as you add records, making it less likely that new rows are missed. Refresh the PivotTable after adding records to update the summary.
Set the value calculation deliberately. Right-click a value, choose Summarize Values By, and select Sum, Count, or another suitable calculation. For prices or amounts, Sum is common; for a date or ID field, Count may be more meaningful.
Keep the source dates precise. Grouping creates period labels for the PivotTable, but it does not replace the original dates. Retaining those dates lets you change the grouping later or investigate a specific record.

Troubleshooting

Group is unavailable or Excel reports that it cannot group the selection: First inspect the full source date column for blanks, text, errors, or mixed values. Clear filters temporarily if they make it harder to inspect the records. Correct or remove invalid entries, then refresh the PivotTable and try grouping again. If an empty row is inside the source range, remove it or adjust the range so it does not include the blank row.

The dates appear as text: Select the source date column and use Data > Text to Columns. In the wizard, choose Delimited and click Next twice; under column data format, select Date and the order that matches the incoming text, such as MDY or DMY, then click Finish. Verify several converted cells by changing their number format or checking that they behave as dates. Date order matters: interpreting 04/05/2026 as April 5 rather than May 4 can change your results.

Months from different years are combined: Right-click a grouped date label, choose Ungroup, then right-click an individual date again and select both Years and Months. If you only need annual totals, select Years alone instead.

New records do not appear: Refresh the PivotTable. If that does not include the additions, open PivotTable Analyze > Change Data Source and confirm that the source covers the new rows. Consider converting the source range to an Excel Table before rebuilding or updating the PivotTable.

Grouping is present but the layout looks wrong: Check the field order in Rows and Columns, and expand or collapse the year or quarter labels. If Excel automatically grouped the field but you want individual dates, right-click a date label and choose Ungroup.

Frequently Asked Questions

Can I group dates by month without a PivotTable?

For a quick summary, a PivotTable is the built-in approach described here. You can also add a separate month or year column to your source data, but that changes the source layout and requires maintaining those extra fields. PivotTables are generally more flexible when you want to change the grouping or compare categories.

Can I group by month and year at the same time?

Yes. In the Grouping dialog, select both Months and Years. The PivotTable will show months within their respective years rather than combining the same month across the full date range.

Can I group dates by week?

Yes. Right-click a date and choose Group, select Days, and set the number of days per group to 7. The resulting groups are seven-day intervals from the starting date shown in the dialog; they do not necessarily match calendar weeks that begin on Monday or Sunday.

How do I undo date grouping?

Right-click a grouped date label in the PivotTable and choose Ungroup. This returns the field to individual dates. It changes the PivotTable view, not the source date values.

Why does Excel show Years, Quarters, and Months automatically?

Some desktop Excel versions automatically create a date hierarchy when you add a date field to a PivotTable. You can use the generated fields to navigate the periods, or right-click a date and choose Ungroup if you want to set a different grouping or show individual dates.

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.