Excel Slicer: Group Dates by Month

Yes. In Excel for Microsoft 365 on Windows, you can group a PivotTable date field by month, then insert a slicer for the resulting Months field; if your data covers multiple years, include Years as well so January from different years does not get combined.

How date grouping and slicers work

A slicer is a clickable filter for a PivotTable or Excel table. It does not create date groups by itself. For a PivotTable, first group the date field into calendar periods; Excel then makes grouped fields, such as Months and Years, which you can use in a slicer. Clicking a month filters the PivotTable to records in that period.

Grouping affects how the PivotTable summarizes dates, not the underlying dates in your source data. A source value such as March 18, 2025 remains that date. The PivotTable displays or filters it as part of March 2025 after grouping. This is useful for monthly sales, attendance, service calls, or other records with one date per row.

The distinction matters because a slicer added directly to an ordinary date column may list individual dates. It will not automatically turn those entries into month buttons. To get monthly choices, use a grouped PivotTable field or create a separate month-level field in the source data.

Group a PivotTable date field by month

These steps are for Excel for Microsoft 365 on Windows. Start with a PivotTable whose source includes a genuine Excel date column, such as Order Date. If the PivotTable already exists, click one of its date entries before grouping.

Click a date inside the PivotTable.
Right-click the date and choose Group. You can also use PivotTable Analyze > Group Field when that command is available.
In the Grouping dialog, select Months. If the data spans more than one year, also select Years.
Choose OK. Excel adds grouped fields to the PivotTable field list and organizes the date entries into the selected periods.

You can then arrange the new fields in the Rows, Columns, or Filters areas as needed. For example, place Years and Months in Rows to show a monthly report, or keep the existing layout and use the fields only as slicer filters. The precise field labels can vary according to the name of the original date column.

If Excel selects a range of dates automatically, check the starting and ending dates in the dialog. Those boundaries affect the groups Excel creates. They do not repair invalid source entries, so inspect the source column if the periods look incomplete.

Insert a slicer for Months or Years

Once grouping has created the fields, add a slicer using the grouped field rather than the original date field:

Click anywhere in the PivotTable.
Select PivotTable Analyze > Insert Slicer.
In the field list, select Months, Years, or both, then select OK.
Click a month button to filter the PivotTable. Use the slicer’s clear-filter button to remove the selection and show all periods again.

If you add both Years and Months slicers, choose a year first and then a month. This makes it clear whether you are viewing, for example, March 2024 or March 2025. A multi-select slicer can include more than one month; use its multi-select control or the selection method shown by the slicer’s buttons. Holding Ctrl while clicking can also select or deselect multiple items in many Windows slicer views.

After inserting a slicer, drag it to a convenient position and adjust its size. The Slicer tab offers display options such as button columns and height. These are layout choices: they do not change the grouping or the PivotTable’s source data.

Why the year field matters

A Months grouping by itself can combine every January in the source, every February, and so on. That is appropriate for a report comparing typical monthly totals across years, but it is usually wrong when readers expect to filter a specific calendar month in a specific year. A January button could include January 2023, January 2024, and January 2025 together.

Including Years creates a separate year grouping, so the PivotTable can distinguish the same month name in different years. For a report spanning 2023 through 2025, the slicers can narrow the view to 2025 and then January. If the report is strictly for a single year, a Months slicer alone may be clear enough, though adding the year can still help readers understand the reporting period.

For a date range that needs exact boundaries rather than discrete month choices, a Timeline may be more suitable. A PivotTable Timeline is a date-oriented filter control that can be set to display periods such as years, quarters, months, or days. It is different from a slicer, even though both are visual filters.

Use a Timeline instead of a month slicer

To add one, select the PivotTable and choose PivotTable Analyze > Insert Timeline, then select the date field. Use the Timeline’s period selector to switch between the available time scales and drag its handles to choose a period. A Timeline is often easier when users need to move through a continuous range, such as April through September, rather than select several separate month buttons.

A month slicer can be more compact and makes individual month choices visually obvious. A Timeline communicates chronology and supports range selection more naturally. Neither is universally better: use the control that matches the question your report is meant to answer. Both depend on a PivotTable date field that Excel recognizes as dates.

Timelines and slicers filter the PivotTable; they do not change the source records or permanently remove data. Clear the filter to restore the full view. If the PivotTable is connected to an external source or Data Model, available grouping and filtering features can depend on that connection.

When the source is an Excel table

An Excel table can also have a slicer, but a table slicer does not offer the PivotTable’s date-grouping dialog. If you want month buttons on a table, add a helper column that assigns every date to the first day of its month, then insert a slicer for that helper column. Format the helper values as a readable month and year, for example mmm yyyy, so different years remain distinct.

With an Excel table column named Date, enter this formula in a new table column named Month:

=DATE(YEAR([@Date]),MONTH([@Date]),1)

The structured reference uses the Date value in the same table row. For a row dated March 18, 2025, the formula returns March 1, 2025; formatting the result as mmm yyyy displays it as Mar 2025. The underlying value remains a real date, which makes it suitable for sorting chronologically. Exclude or handle blank and invalid dates before relying on the helper field, since those entries can produce misleading results or errors.

After the helper column is filled, click inside the table and choose Table Design > Insert Slicer, then select Month. The slicer filters table rows whose helper dates match the selected month. This approach is useful when you need to filter a table directly instead of building a PivotTable, but it adds a calculated column to the source.

Common problems and fixes

Group is unavailable: Check that the selected cell is a date item in a PivotTable, not a regular worksheet cell. A PivotTable based on the Data Model or certain external sources may not support ordinary date grouping.
Grouping reports that selection cannot be grouped: Look for blanks, text that only looks like a date, error values, or mixed date and text entries in the source date column. Correct the source values, refresh the PivotTable, and try again.
Months from different years appear together: Edit the grouping and select Years as well as Months, or add a Years slicer so the month selection has a year context.
New records do not appear: Confirm that the PivotTable source includes the new rows, then use PivotTable Analyze > Refresh or refresh the PivotTable. An Excel table used as the source generally expands as rows are added, but the PivotTable still needs refreshing to update its results.
The slicer is not filtering a second PivotTable: Select the slicer and open Slicer > Report Connections (the command may be labeled PivotTable Connections in some builds). The other PivotTable must be compatible with that slicer’s source or cache to be connected.

When troubleshooting, distinguish between a display issue and a source-data issue. Changing a date’s number format does not turn text into a true Excel date. If a cell is left-aligned or behaves like text, convert or correct the source value rather than relying on formatting alone.

Choosing the right setup

Use grouped PivotTable fields when your report is already a PivotTable and you want compact month and year buttons. Use a Timeline when selecting a continuous date range is central. Use a helper month column and a table slicer when readers need to filter the original rows in an Excel table without a PivotTable.

Before distributing a report, check the behavior with representative dates: one month from two separate years, a date at the start or end of a month, and any blank or unusual source entries. Confirm that totals change as expected when you select a month and return to the full total when you clear the filter. These checks catch common grouping mistakes without changing the source data.

On Excel for Mac, PivotTable grouping and slicer features are available in desktop Excel, though ribbon labels or their placement can differ from Windows. Excel for the web may not expose every desktop grouping command in the same way; if the Group dialog or needed slicer controls are missing, open the workbook in the desktop app. Save and refresh the workbook after changing source data so the PivotTable reflects the current records.

Frequently Asked Questions

Can I group dates by month in a slicer without a PivotTable?
A table slicer cannot group a date column on its own. Add a month helper column to the table and insert a slicer for that column, or build a PivotTable, group its date field, and add a slicer for Months.

Why does my month slicer show January only once?
If you grouped by Months without Years, Excel may be combining January from every year in the source. Include Years in the grouping and use a Years slicer, or use a month-year helper field such as Mar 2025.

Can I show month names in calendar order?
Yes, when the grouped field is based on valid dates, Excel can order its month items chronologically. A helper column that contains real first-of-month dates and is formatted as mmm yyyy also sorts by date rather than alphabetically.

Can one slicer control multiple PivotTables?
Sometimes. Use the slicer’s Report Connections or PivotTable Connections command to select compatible PivotTables. PivotTables may need to share the same source or PivotTable cache; separate sources are not automatically controlled by one slicer.

Will grouping change my original date values?
No. Grouping creates period fields for the PivotTable; it does not rewrite the dates in the source column. The PivotTable can still be refreshed from its source, subject to its normal source and connection settings.

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.