Pivot Chart: Group Dates by Month in Excel

Yes. In Excel for Microsoft 365 on Windows, group dates for a PivotChart through its underlying PivotTable: right-click a date in the PivotTable, choose Group, select Months, and click OK. If your data spans multiple years, select Years as well so January from different years is not combined into one total.

How date grouping works in a PivotChart

A PivotChart displays the summarized fields in its associated PivotTable. To change how dates are summarized, you generally group the date field in that PivotTable; the connected chart then reflects the grouped categories. Grouping changes the way Excel organizes the date values for analysis, not the dates stored in the source data.

For example, if a table contains individual order dates and sales amounts, an ungrouped PivotTable may list every order date separately. Grouping those dates by month combines the sales into monthly totals. The PivotChart can then show one column, point, or other chart mark for each month, depending on the chart type.

Month grouping uses calendar months. It does not automatically create a fiscal calendar, calculate a rolling 30-day period, or turn text that looks like a date into a real Excel date. Those distinctions matter when the result looks unexpected.

Group dates by month: Windows steps

Click a cell in the source data and create a PivotTable, or click inside the PivotTable already connected to your PivotChart.
Place the date field in the PivotTable’s Rows area or Columns area. Put a measure such as Sales in Values.
Right-click one of the displayed date items in the PivotTable and choose Group.
In the Grouping dialog box, select Months. If the source includes more than one year, select Years too.
Click OK. Check the resulting fields and the PivotChart’s category axis; adjust the chart if needed to make the month labels readable.

In many cases, Excel adds grouped fields such as Months and Years to the PivotTable field list. You can rearrange them in the Rows, Columns, Filters, or Axis areas. For a monthly trend, Years followed by Months is often useful: it preserves the sequence of months within each year and lets viewers distinguish one year from another.

Prepare the source dates first

Excel can group values only when it recognizes the field as dates. A cell displaying 3/8/2025 might be a true date serial, or it might be text that merely looks like a date. Formatting alone does not convert text into a date. If the field contains a mixture of true dates, text, blanks, or errors, the Group command may be unavailable or Excel may report that it cannot group the selection.

Before building the PivotTable, check a sample of the source values and correct inconsistent entries. Import settings and regional date conventions can cause ambiguity: 04/05/2025 could mean April 5 or May 4, depending on how the data was interpreted. Confirm the intended dates rather than assuming the display format proves they are correct.

Remove or correct text values in the date column, converting them to genuine Excel dates where their meaning is known.
Decide how to handle blank dates. Fill them only when the correct date is known; otherwise, exclude those records from the date-based analysis or report them separately.
Check for error values and inconsistent source columns, especially after importing data from another system.
Keep one date field consistent throughout the source range rather than mixing dates with explanatory labels such as “Pending.”

A PivotTable built from an Excel Table can be easier to maintain as rows are added: select the source data and use Insert > Table before creating the PivotTable. This does not fix invalid dates, but it helps the source range include new records when the PivotTable is refreshed.

Why select Years as well as Months?

Selecting Months alone groups by month name across the entire date range. If the source covers 2024 and 2025, all January records can be combined into a single January total, as can all February records. That is suitable when the question is “How much did we sell in January, regardless of year?” It is not suitable for comparing a month-by-month timeline across years.

For a chronological comparison, select both Years and Months. Excel then separates, for example, January 2024 from January 2025. In the PivotTable, you can keep Years above Months in the row hierarchy, or move Years to a filter or column position if that gives the chart a clearer layout. The best arrangement depends on whether the reader needs a continuous timeline, separate yearly series, or a quick year filter.

Do not confuse a month label with a unique month. “January” by itself does not identify a particular year. When a chart shows only month names, check whether years have intentionally been combined before drawing conclusions about trends.

Make the PivotChart easier to read

After grouping, inspect the chart’s axis and legend. A PivotChart may show a hierarchy of years and months, or multiple series, depending on how the grouped fields are placed. If the chart is crowded, move Years to the Filters area, use it as a series field, or filter to a smaller date range. These choices alter the presentation, not the underlying source dates.

Choose a chart type that fits the question. A line chart can emphasize the direction of monthly values over time; a clustered column chart can make month-to-month comparisons more explicit. If multiple years are shown, a separate series for each year can support comparisons, but too many series may make the chart difficult to interpret.

Use a clear title and, when necessary, state the year range or whether the figures are totals or averages. If a month has no source records, the PivotTable may show a blank or omit the category depending on its layout and settings. A missing month is not automatically evidence of zero activity; verify how the source represents periods with no transactions.

Common grouping problems and fixes

If Group is disabled or Excel says it cannot group the selection, inspect the entire source date field, not just the visible dates in the PivotTable. A single blank, text entry, or error can prevent grouping. Correct the source and refresh the PivotTable; if the problem remains, rebuild the PivotTable after confirming that the field contains consistent date values.

If months appear in an unexpected order, verify that they are grouped date values rather than month names imported as text. Text labels may sort alphabetically, placing April before February. Real dates grouped by month sort in calendar order. If the field is text and cannot reasonably be converted, a separate month-number field may be needed to support a meaningful order.

If the chart combines the same month from several years, return to the grouping settings and include Years, or place a year field in the chart layout. If the chart shows individual days instead of monthly totals, confirm that the PivotTable field was grouped and that the chart is still connected to that PivotTable.

If new records do not appear, refresh the PivotTable using PivotTable Analyze > Refresh. Also verify that the new rows are inside the source range or Excel Table. New dates outside the range used when the PivotTable was created will not appear just because the PivotTable was refreshed. If refreshed data introduces a text value or blank into the date column, grouping may need attention again.

Calendar months, fiscal periods, and alternatives

The built-in Months option follows calendar months. It does not know that a company’s fiscal year starts in July, nor can it automatically label a 4-4-5 retail calendar. For a fiscal calendar, add appropriate fiscal-year and fiscal-period fields to the source data, then use those fields in the PivotTable. This provides control over labels and period boundaries and avoids implying that calendar months match the organization’s reporting periods.

A Timeline is another PivotTable filter tool, but it serves a different purpose. It lets users filter a date field interactively over a date range; it does not, by itself, aggregate daily records into monthly totals. Use grouping for monthly summaries and a Timeline when viewers also need to select or narrow the displayed date range.

For a regular chart that is not a PivotChart, grouping in a PivotTable will not change it. You can instead create a PivotChart from the PivotTable or prepare a separate monthly summary table and chart that. A separate summary offers more freedom in layout, while a PivotChart stays linked to PivotTable fields and filters.

Remove or change the grouping

To return to individual dates, right-click a grouped date item in the PivotTable and choose Ungroup. Excel removes the date grouping and the PivotChart returns to the more detailed date categories. If the menu option is not available, check that you clicked a grouped date item rather than a value or a chart element.

You can also change the grouping interval by opening the Grouping dialog again and selecting a different interval, where available. For monthly reporting, select Months; for a broader roll-up, you can group by Quarters or Years. Grouping by quarters and months together can be helpful, but the resulting hierarchy should be checked so the chart does not duplicate or obscure the intended comparison.

Frequently Asked Questions

Can I group dates directly on a PivotChart?
Use the connected PivotTable to group the date field. The PivotChart reflects that grouping. Right-clicking a chart axis is not the clearest or most reliable way to change the PivotTable’s date grouping.

Why does Excel combine January from different years?
When you select Months without Years, Excel groups all January dates together, regardless of year. Select both Months and Years when each year’s January needs a separate value.

Can Excel group dates that include times?
Excel date-and-time values are stored as date serials with a time component, and valid values can generally be grouped by date intervals such as months. The field still needs consistent valid date/time values; text and blanks can prevent grouping.

Does month grouping change my source data?
No. Grouping summarizes the date field in the PivotTable and changes the categories used by the PivotChart. It does not replace the individual dates in the source table.

Can I group dates by month in Excel for Mac?
Excel for Mac also supports PivotTable date grouping in desktop versions that provide the Group command. The interface may differ slightly; select a date item in the PivotTable and look for Group in its context menu. The steps above describe Excel for Microsoft 365 on Windows.

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.