Excel Pivot Chart: Group Dates by Month?

Yes. In Excel for Microsoft 365 on Windows, group dates in the PivotTable behind the PivotChart: right-click a date, choose Group, select Months, and click OK. If your data covers more than one year, select Years as well as Months to keep, for example, January 2024 separate from January 2025.

How date grouping works in a PivotChart

A PivotChart gets its categories, values, and filters from its associated PivotTable. That means date grouping is generally set in the PivotTable, rather than by changing an ordinary chart axis. When you group the date field in the PivotTable, Excel updates the chart to use the grouped date categories too.

Grouping changes how dates are organized for analysis; it does not change the dates in your original source data. Excel creates grouped fields in the PivotTable, such as Months or Years, and summarizes the underlying records within each group. You can still drill into the source records or change the grouping later.

This is useful when a report contains daily transactions but the question is about monthly performance. Instead of displaying a long sequence of individual dates, the chart can show a compact series of month-level totals, counts, or other summaries.

Steps to group PivotChart dates by month

Confirm the source date column contains real Excel dates, then refresh the PivotTable if the source data has changed.
Click the PivotTable connected to the chart. If you are unsure which table it is, click the PivotChart and locate its associated PivotTable in the workbook.
In the PivotTable, right-click any date item in the date field. Choose Group from the context menu.
In the Grouping dialog, select Months. If the records span multiple years and you want each month-year combination kept separate, also select Years.
Click OK. Excel groups the PivotTable dates, and the PivotChart should reflect the new categories. Check the chart axis and legend to confirm the result is the view you intended.

On a Mac, the same grouping is available from the PivotTable’s date field context menu; use a right-click or Control-click depending on your mouse or trackpad settings. The ribbon and dialog appearance can differ slightly by platform, but the key operation is grouping the date field in the PivotTable.

Choose the right month and year view

The most important choice in the Grouping dialog is whether to group by Months alone or by Months and Years together. If a dataset contains only one year, Months alone usually gives the expected January-to-December view. With multiple years, grouping by Months alone combines every January into one category, every February into another, and so on.

For example, imagine sales records from January 2024 through December 2025. A Months-only grouping produces 12 categories. The January category summarizes January from both years. Selecting both Months and Years produces a separate category for each month in each year, giving 24 monthly periods. This is usually the better choice for a trend chart because it preserves the sequence of time.

Months only: Use this to compare the typical or combined performance of calendar months across a multi-year period.
Months and Years: Use this to show a chronological month-by-month trend without merging years.
Years only: Use this when annual totals are more important than month-level detail.
Quarters and Years: Use this for a higher-level seasonal or quarterly comparison.

When both Years and Months are selected, Excel may display separate grouped fields in the PivotTable field list. Put the fields in the Rows or Axis area in the order that makes the chart easiest to read. A hierarchy with Year above Month can make it easier to expand or collapse periods; the exact layout depends on the PivotTable structure.

Make the chart readable and meaningful

Grouping determines the time categories, but the PivotTable’s Values area determines what the chart measures. For instance, placing Revenue in Values and summarizing it by Sum creates monthly revenue totals. Placing Order ID there and summarizing by Count instead reports the number of orders per month. Check the value-field summary rather than assuming that every chart displays a sum.

For monthly trends, a line chart can make direction and seasonal patterns easy to see. A clustered column chart is often useful for comparing separate months or categories. Choose a chart type that fits the question, and keep the date sequence chronological. If month names appear alphabetically, inspect the PivotTable’s date grouping and sort order; a true date grouping should be based on the date field, not a text field containing month names.

PivotChart field buttons can make a chart look crowded. You can hide them from the chart’s field-button options without removing the fields from the PivotTable. Also consider a clear chart title, readable date labels, and number formatting that suits the measure, such as currency for sales. Avoid adding more series than the chart can communicate clearly.

Check the source dates before grouping

Excel can group a date field when its entries are recognized as dates and can be handled consistently. A cell that looks like 3/5/2025 may actually contain text, depending on how the data was imported or formatted. Formatting text to look like a date does not convert its underlying value into an Excel date.

Inspect the source column for blanks, text entries, errors, or a mixture of dates and text. A common symptom is the message Cannot group that selection when you try to group the field. Correct the source entries, make sure the whole field has consistent date values, and refresh the PivotTable before trying again. If there are blank records, decide whether they should be removed or assigned a valid date based on the data’s meaning.

Also check that the PivotTable source range includes all relevant rows. If new records were added outside a fixed source range, refresh alone may not include them. Adjust the source range or use an Excel table as the source so that added rows are more readily included when the PivotTable is refreshed.

When built-in grouping is not the right tool

Excel’s Months grouping follows calendar months. It does not automatically create a company-specific fiscal calendar, a 4-4-5 retail calendar, or custom periods that begin on a different day. If a report needs those periods, add appropriate fields to the source data or use a dedicated calendar table, then build the PivotTable and PivotChart from those fields.

There is another important exception: PivotTables built from the Data Model or certain external OLAP sources may not offer the same right-click date-grouping behavior as a standard PivotTable. In those cases, create date attributes such as Year, Month, and Month-Year in the model or source, or use a calendar table with a date hierarchy. The available modeling steps depend on how the data connection is built.

For a quick monthly report from a conventional worksheet range, grouping is usually simplest. For recurring reports with fiscal periods, multiple date roles, or shared measures, a properly designed calendar table is more flexible and makes the period definitions explicit.

PivotChart grouping compared with other date views

A grouped PivotChart is not the only way to display time. A chart based on a normal worksheet table can use a date axis, but it does not have the PivotTable’s automatic aggregation and filtering behavior. If you want monthly summaries that respond to PivotTable filters, grouping the PivotTable date field is usually the direct route.

A Timeline is a separate PivotTable filtering control. It lets you filter records by periods such as years, quarters, months, or days, but it is not a substitute for placing monthly categories on the chart. You can use a Timeline to limit which dates are included while the chart still displays the grouped monthly results.

Month names created as text in the source can be convenient labels, but they can sort alphabetically and may combine different years unintentionally. If you use a helper field, include a year component or a true month-start date to preserve chronological order. The best method depends on whether you need a quick calendar-month summary or a reusable reporting calendar.

Frequently Asked Questions

Why can’t I group dates in my PivotChart?
Usually, the related PivotTable date field contains blanks, text, errors, or inconsistent values, or the PivotTable uses a Data Model or OLAP source that does not support ordinary grouping. Check the source field, correct its values if needed, refresh, and try grouping from the PivotTable.

Does grouping by month combine the same month from different years?
Yes, if you select Months without Years, Excel groups all January dates together, all February dates together, and so on. Select both Months and Years to keep each month-year period distinct.

Can I show month names instead of full dates?
Yes. Group the PivotTable date field by Months. If the data covers several years, include Years when you need separate monthly periods, then arrange the grouped fields so the chart labels communicate the time range clearly.

Will grouping change the dates in my original data?
No. Grouping changes the PivotTable’s reporting categories, not the source date values. The original records remain available, and you can change or remove the grouping without rewriting those source dates.

Can I group dates into fiscal months?
Not with the standard calendar-month grouping alone. Use additional fiscal-period fields in the source or a calendar table, then use those fields in the PivotTable and PivotChart.

Remove or revise a date grouping

If the result is not right, return to the PivotTable and adjust the grouped fields or reopen the Grouping dialog from a grouped date item. To return to individual dates, right-click a grouped item and choose Ungroup when that command is available. You can then group the field again using different intervals, such as Months and Years rather than Months alone.

Before changing a report used by others, check the PivotTable and chart together. A grouping change can alter category counts and totals, especially when several years are combined. Confirm that the chart answers the intended question, whether that is comparing month-of-year performance or tracking each month in chronological order.

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.