To filter an Excel PivotTable by month, place the date field in Rows or Columns, right-click a date in the PivotTable, choose Group, select Months, and click OK. If your source includes more than one year, select Years as well; then filter the Months field without accidentally combining January from different years.
Group a date field by month
These steps apply to Microsoft Excel for Microsoft 365 on Windows. The source field must contain actual Excel dates, not text that merely looks like dates.
- Click a cell in the PivotTable. In the PivotTable Fields pane, place the date field in the Rows area or the Columns area if it is not already there.
- Right-click one of the displayed dates in the PivotTable and choose Group. If dates are already grouped, right-click a date or month label and choose Group to review the grouping options.
- In the Grouping dialog box, select Months. If the data covers multiple calendar years, also select Years. Clear other intervals, such as Quarters, if you do not need them.
- Click OK. Excel adds grouped fields, commonly shown as Months and Years, to the PivotTable. You can filter the month field using its dropdown, or move Months to the Filters area in the PivotTable Fields pane.
To show one month, open the dropdown for the Months field, clear Select All, select the month you want, and click OK. To show several months, select each one. In a report filter, Excel may show a Select Multiple Items option; turn it on if you need to choose multiple entries there. The exact dropdown location depends on the PivotTable layout and where you placed the field.
What Excel does when it groups dates
Grouping changes how the PivotTable summarizes the date field; it does not edit the dates in the source worksheet. Excel creates fields representing date intervals. With Months selected, dates such as 4/12/2025 and 4/28/2025 are summarized under April. The individual day values are no longer the visible row labels unless you also keep the original date field in the layout.
Excel may group date fields automatically when you build a PivotTable. In that case, the row labels can already show Years, Quarters, or Months, and the PivotTable Fields pane contains the grouped fields. You can filter the existing Months field rather than grouping a second time. If the PivotTable displays a hierarchy, expand or collapse it to show the level you want.
A month label represents a calendar month, not a fixed number of days. February, for example, can have 28 or 29 days. The PivotTable sums, counts, or otherwise summarizes the records assigned to that month according to the value field and its selected summary calculation.
Why include Years when filtering months?
Months repeat every year. If your source has transactions from 2024 and 2025 and you group only by Months, the January group can contain January records from both years. That is useful for a combined January comparison, but it is not the same as filtering to January 2025.
For year-specific results, include Years in the Grouping dialog. Keep both the Years and Months fields in Rows, or put Years in Filters and Months in Rows. Select the relevant year and month using their respective dropdowns. For example, choosing 2025 in Years and April in Months limits the PivotTable to April 2025. If you want to compare April across several years, select April in Months and leave the years visible as a separate grouping.
This distinction is one of the most common sources of surprising totals. Before relying on a monthly result, check whether the year is included in the grouping or filtered separately. A month-only view can be correct while combining multiple years by design.
Choose between a month filter and a date range
A grouped Months field is convenient when readers need to choose named calendar months, such as January and March, from a list. A date range is better when the report must include a precise start and end date, such as 15 April through 15 May, or a rolling period that does not align with whole months.
To filter a date field by a period, use the original date field rather than assuming the grouped month label represents an exact range. If needed, right-click a grouped date label and choose Ungroup, then open the date field dropdown and look for Date Filters. Options can include comparisons or a period such as Between, depending on the field and Excel version. Enter the desired boundary dates and confirm. If you need full calendar months, use date boundaries that cover the first day of the first month through the last day of the final month.
Grouped month filters and date-range filters answer different questions. A month selection chooses calendar categories; a date filter chooses records within specified boundaries. Choose the one that matches the reporting requirement rather than using a month label as a substitute for an exact range.
Make the filter easier to use
You can put Months in the Filters area to create a report filter above the PivotTable. This keeps the month selection visible without adding another row level to the report. Alternatively, leave Months in Rows and use the field dropdown to hide unwanted months. In a compact PivotTable layout, the dropdown may appear beside Row Labels rather than beside a separate Months heading.
For a more visual control, select a cell in the PivotTable and choose PivotTable Analyze > Insert Slicer. Select the Months field, then click OK. A slicer displays buttons for month choices and can make a dashboard easier to operate. If the grouped Months field is not offered, check the field list and confirm that grouping succeeded. Slicers filter the PivotTable; they do not change the underlying source records.
To adjust the presentation, use PivotTable Design > Report Layout to choose Compact, Outline, or Tabular Form. Tabular Form can make separate grouping fields, such as Years and Months, easier to identify. You can also rename a field label for readability, but keep the distinction between a month name and a specific year clear in report headings.
Common grouping problems and fixes
- Group is unavailable or returns an error: Check the source date column for blank cells, text values, or errors. A single invalid entry can prevent date grouping. Correct the source values, refresh the PivotTable, and try Group again.
- Dates look valid but are stored as text: Formatting text as a date does not convert it into a true date value. Convert the source entries to Excel dates, verify that they sort chronologically, and rebuild or refresh the PivotTable.
- January combines several years: Add Years in the Grouping dialog, then filter Years as well as Months. A month-only grouping intentionally combines matching months across years.
- New source rows do not appear: Confirm the source range includes those rows, then select PivotTable Analyze > Refresh. If the source range was fixed and new rows were added outside it, change the data source or use an Excel Table as the source so the range can expand.
- Months appear in an unexpected order: Use the grouped Months field, which represents calendar months, rather than a separate text column containing month names. Text labels can sort alphabetically or according to their underlying values, rather than by calendar order.
- There is no Group command: Make sure you right-clicked a date item in the PivotTable, not a value cell or a worksheet cell outside it. Some PivotTable sources or connection-based reports restrict grouping; if the command remains unavailable, try a regular PivotTable built from a clean date column.
After changing source data, refresh the PivotTable so its results reflect the corrected dates. Refreshing does not necessarily repair an incomplete source range, and it will not turn text dates into real dates automatically. Fix the source and verify the range before refreshing again.
Using Excel on Mac or in a browser
On Excel for Mac, the overall method is similar: place the date field in the PivotTable, open the context menu for a date, choose Group, and select the desired intervals. Depending on the Mac and mouse settings, use a two-finger click or Control-click to open the context menu. Ribbon names and dialog placement can differ slightly from Windows.
Excel for the web can display and interact with PivotTables, but the available grouping and editing controls can depend on the workbook and current web features. If you cannot find Group or cannot change the grouping, open the workbook in the desktop Excel app, make the change there, and save it. The saved grouping can then be viewed by other users who open the workbook.
Frequently Asked Questions
Can I filter a PivotTable to one month only?
Yes. Group the date field by Months, open the Months dropdown, clear Select All, choose the desired month, and click OK. Include and filter Years too if the source contains multiple years and you mean one particular year.
Why does my PivotTable show months from different years together?
Because grouping by Months alone creates a shared January group, shared February group, and so on. Add Years in the Grouping dialog or use a separate year filter to keep the periods distinct.
Can I group dates by month and quarter at the same time?
Yes. In the Grouping dialog, select Months and Quarters, and include Years when the source spans multiple years. Excel creates corresponding grouped fields that you can arrange or filter in the PivotTable.
Why can’t I group the dates in my PivotTable?
The date column may contain blanks, text dates, or errors, or the PivotTable source may restrict grouping. Check and correct the source column first. If the data comes from a connection or another restricted source, try a standard PivotTable based on a clean worksheet date column.
Does grouping by month change my source data?
No. Grouping changes the PivotTable’s display and summary fields; it does not rewrite the dates in the source range. The underlying source records remain available in the worksheet or external data source.
Related
- Excel Next Tab Shortcut: Move to the Next Worksheet
- Excel Keyboard Shortcut to Highlight a Cell with Color
- Excel Keyboard Shortcuts Mouse Pad: Guide to Choosing
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table Values Not Summing? Fix the CauseIf Excel PivotTable values are not summing, first check that the source column contains real numbers rather than…
- T-Test Distribution Graph: How to Read and Interpret ItA t-test distribution graph is a picture of the t-distribution: a symmetric, bell-shaped probability curve centered at zero.…
- Excel Pivot Table Formatting: A Practical GuideTo format an Excel PivotTable in Microsoft 365 for Windows, click inside it, open the contextual Design tab,…
- Excel Gantt Chart Template Monthly: Build One That WorksYes. You can make a monthly Gantt chart template in Excel by listing each task with its start…
- How to Create a Dashboard in Excel: A Practical GuideTo create a dashboard in Excel, organize your source data in a clean table, summarize it with PivotTables…
- Excel Scatter Plot Linear Regression: How to Add a TrendlineTo show a linear regression on an Excel scatter plot, select your numeric X and Y data, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.