Excel Pivot Tables: Summarize Dates by Month

Yes. In Excel for Microsoft 365 on Windows, put your dates in a PivotTable, right-click a date in the table, choose Group, and select Months. If your data covers more than one year, also select Years so that January from different years is not combined into one total.

What monthly grouping does

A PivotTable normally treats each distinct date as a separate item. If your source contains hundreds of daily records, the Rows area may therefore list hundreds of dates. Grouping the date field by month rolls those daily records into monthly categories while leaving the original source dates unchanged.

Grouping changes the labels used to organize the PivotTable; it does not decide how the other fields are calculated. For example, a sales amount can be summed for each month, while an order number can be counted. You can also add more than one value field to show, say, monthly sales and monthly order counts side by side.

A key detail is that a month name alone does not identify a year. If your source includes January 2024 and January 2025 and you group only by Months, Excel can place both sets of dates under January. Grouping by both Months and Years gives distinct categories such as January 2024 and January 2025.

Create and group the PivotTable

Start with a rectangular source table: one header row, one record per row, and a column containing real Excel dates. The following steps apply to desktop Excel for Microsoft 365 on Windows:

Click a cell in the source data. If the data is an Excel Table, click within that table; otherwise, check that the selected range includes all the relevant rows and columns.
Choose Insert > PivotTable. Confirm the table or range, choose where to place the PivotTable, and select OK.
In the PivotTable Fields pane, drag the date field to Rows. Drag a numeric field, such as Sales, to Values.
Click a date displayed in the PivotTable, then right-click and choose Group.
In the Grouping dialog box, select Months. If the records cover multiple years, select Years as well. Clear grouping intervals you do not need, such as Quarters, then choose OK.

Excel will display grouped date fields in the PivotTable Fields pane and arrange the records under the selected periods. If Excel has already created date levels such as Years, Quarters, and Months, use the existing fields rather than grouping the same dates again. You can remove a level from the layout by dragging it out of the Rows area.

On a Mac, the same general method is available in desktop Excel, although the ribbon layout and context-menu presentation can differ. If a mouse has no right button, Control-click the date cell. Excel for the web may not offer every desktop grouping command in every interface; if Group is unavailable, open the workbook in desktop Excel or use a month-start helper column.

Keep months separated by year

Choose the grouping fields based on the question you want the report to answer. For a single year of data, grouping by Months alone is often clear. For several years, include Years and Months. Excel then gives you year-level categories with the individual months nested beneath them, making it possible to expand or collapse a year and compare the same month across years.

For example, suppose a source has sales records dated January 18, 2024, January 29, 2024, and January 12, 2025. Grouping only by Months places all three records in January. Grouping by Years and Months instead produces January under 2024 and January under 2025. This distinction matters when calculating trends: combining all Januaries answers a seasonal question, while separating the years answers a year-over-year question.

Quarters can be useful alongside months when readers need both detail and a higher-level summary. In the grouping dialog, selecting Years, Quarters, and Months creates a hierarchy. Keep only the levels that make the report easier to read; extra levels can make a PivotTable harder to scan.

Choose how values are summarized

After grouping the dates, check the calculation in the Values area. Excel commonly sums numeric fields, but it may choose Count when the field contains text or when the data is not consistently numeric. To change the calculation, open the field’s menu in the Values area, choose Value Field Settings, and select an option such as Sum, Count, or Average.

Use the calculation that matches the meaning of the data:

Sum adds amounts, such as revenue or units shipped, for each month.
Count counts populated records, which can represent transactions if each row is one transaction.
Average calculates the mean of the numeric entries in each month; it is not the same as an average per day unless the source has been arranged to support that interpretation.

For instance, if three January sales rows contain 120, 80, and 50, the monthly sum is 250. If the intended question is how many sales records occurred, Count returns three instead. A PivotTable summarizes rows, so duplicate records or multiple rows per order affect the result unless the source and calculation account for them.

Number formatting is separate from the summary calculation. Use the value field’s number format to display currency, whole numbers, or percentages consistently. Applying a format to the source column alone may not give the PivotTable the appearance you want.

Prepare source dates and refresh the report

Grouping works when Excel recognizes the source values as dates, not merely strings that look like dates. A real date is stored as a date serial value and can usually be displayed in different date formats. Text such as “March 2025” may look suitable on screen but does not behave as a date field for normal date grouping.

Make sure the date column has a single, descriptive header and that each record has a valid date. Avoid blank cells, error values, and text mixed into the date column. If dates came from a CSV or another system, confirm that Excel interpreted day and month order correctly; ambiguous values such as 03/04/2025 can mean different dates under different regional settings.

A PivotTable is a summary of its source, not a live rewrite of the source data. After adding or correcting records, refresh it using PivotTable Analyze > Refresh. If new rows fall outside a normal cell range, they may not be included. Converting the source to an Excel Table before creating the PivotTable helps the source range expand as rows are added, though the PivotTable still needs refreshing to show updated results.

Use a helper column when grouping is unsuitable

If Excel cannot group the field, or you need a consistent month field for several reports, add a month-start date to the source. This preserves a genuine date value for each month and avoids relying on text labels. Assume the original dates are in A2:A20, with a valid date in A2. In a new column on row 2, enter =DATE(YEAR(A2),MONTH(A2),1) and fill it down. For a source date of March 18, 2025, the result is March 1, 2025; format the helper column as mmm yyyy to display “Mar 2025.”

Use that helper field in the PivotTable Rows area instead of the original daily date field, then refresh the PivotTable if needed. Because the helper values are actual first-of-month dates, they sort chronologically and keep March 2024 distinct from March 2025. Do not use a label such as “Mar” alone for multi-year data: it loses the year and can combine different periods or sort alphabetically in some report layouts.

Fix common grouping problems

If the Group command is unavailable or Excel reports that it cannot group the selection, inspect the entire source date column. One blank, text value, or error can prevent grouping in a field that otherwise appears to contain dates. Correct or remove invalid entries, refresh the PivotTable, and try grouping again. If blanks are meaningful, decide how to represent them in the source rather than silently treating them as a month.

If months appear in an unexpected order, verify that the field contains real dates rather than text labels. A real date field sorts by date. Text labels can sort alphabetically, so “Apr” may appear before “Feb.” A month-start helper column is a practical fix when a report must have custom labels but still needs chronological ordering.

If the PivotTable shows Years or Quarters even though you did not request them, Excel may have automatically grouped the date field. Use the visible date levels deliberately: remove unwanted levels from Rows, or ungroup the field and apply the intervals you want. If grouping starts or ends at an unexpected boundary, check the earliest and latest source dates and the grouping settings.

Finally, distinguish a display issue from a data issue. Changing a date’s number format changes how it looks, but it does not convert text into a real date. If changing the format has no effect on grouping or sorting, convert the source values to dates first and refresh the PivotTable.

When to use another approach

Grouping an existing date field is usually the quickest choice for an interactive PivotTable. A helper column is better when you want a reusable month key, need to work around inconsistent source dates, or want the same month definition in multiple PivotTables. A date table in a data model can be a better fit for more advanced reporting across related tables, fiscal calendars, or measures that need consistent time periods.

For a one-time report with a small dataset, a regular worksheet summary may be enough. But manually typed monthly totals are easier to misstate when rows change. Whichever method you choose, confirm that the total across all months agrees with the relevant total from the underlying records, and check that the date range includes the periods you intend to report.

Frequently Asked Questions

Can an Excel PivotTable group dates by month and year?
Yes. In the Grouping dialog, select both Months and Years. This keeps the same month in different years as separate categories.

Why does Excel combine January from different years?
That happens when the field is grouped by Months without Years. Add the Years grouping as well, or use a month-start date field that includes the year.

Why can’t I group dates in my PivotTable?
Check for blanks, errors, or text values in the source date column, including rows outside the part of the data you first noticed. Convert inconsistent values to real dates, refresh the PivotTable, and try again.

Will new records appear automatically in the monthly summary?
Not necessarily. Refresh the PivotTable after the source changes, and make sure its source range includes the new rows. An Excel Table can expand with added rows, but refreshing is still needed to update the summary.

Can I show months as Jan, Feb, and Mar?
Yes. You can format date labels for a concise display, but include the year when the report spans multiple years. If you use a helper date, a format such as mmm yyyy shows both the month abbreviation and year.

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.