To filter an Excel PivotTable by month, place a real date field in Rows, open its field dropdown, and choose Date Filters > All Dates in the Period > the month you want. To group dates into month labels you can select, right-click a date in the PivotTable and choose Group, then select Months; also select Years if the source contains more than one year.
Choose a monthly filter or monthly grouping
Filtering and grouping are related, but they do different jobs. A date filter hides records outside the period you choose. Grouping changes how dates are displayed and summarized, usually turning daily entries into month or year-and-month categories. The best approach depends on what you want the finished PivotTable to show.
- Filter to a single month: Use Date Filters when you want to show, for example, only January transactions.
- Summarize every month: Group the date field by Months so the PivotTable has a row or column for each month.
- Compare months across years: Group by both Years and Months. This keeps January 2025 separate from January 2026.
- Let readers change the period: Consider a timeline or a report filter, which makes the date selection more visible.
A month name alone does not identify a unique month when data spans multiple years. If you group only by Months, Excel can combine all January records together. That may be useful for comparing seasonal patterns, but it is not the right setting for a year-by-year report.
Filter a PivotTable to one month
These steps apply to Excel for Microsoft 365 on Windows. Start with a PivotTable whose source includes a date column, such as Order Date, and at least one field to summarize, such as Sales or Quantity.
- Click inside the PivotTable to show the PivotTable tabs.
- In the PivotTable Fields pane, drag the date field into Rows. Excel needs the field available in the PivotTable before you can use its date-filter controls.
- Open the dropdown beside the date field in the PivotTable. Depending on the layout, the dropdown may appear beside the field name or beside Row Labels.
- Choose Date Filters > All Dates in the Period, then select the month, such as January.
The PivotTable now includes dates from that month in the source data. If it contains multiple years, check the result carefully: a month-period filter can include that month in each year represented, rather than only one particular January. To isolate January of a particular year, filter the year as well, use a grouped Year field, or apply a date range with both a starting and ending date.
To remove the selection, open the same dropdown and choose Clear Filter From the date field, or use the filter menu’s clear-filter command. The wording can vary slightly with the field layout and Excel build. Clearing a field filter restores the dates; it does not remove the date field from the PivotTable.
Group dates into months and years
Grouping is usually the clearest option when the report should show month-level totals. Put the date field in Rows or Columns, then right-click one of the displayed dates and choose Group. In the Grouping dialog, choose Months. If the source covers multiple years and you want separate months within each year, also select Years, then click OK. Another route in Windows is to select a date and use PivotTable Analyze > Group Field.
Excel creates grouped fields, often shown as Years and Months in the PivotTable Fields pane. You can arrange these fields in Rows, Columns, or Filters. For a familiar report, put Years above Months in Rows; values will then appear under each year, broken down by month. To show one year at a time, put Years in Filters and leave Months in Rows.
Grouping does not change the dates in the source table. It changes the PivotTable’s categories, so the underlying records remain daily dates. You can usually expand or collapse grouped levels using the expand/collapse controls beside the year labels, or rearrange the grouped fields to change the view.
If the Group command is unavailable or Excel reports that the selection cannot be grouped, check the source data before trying again. A single blank, text value, or error in the date field can prevent grouping. Also confirm that you clicked an actual date item in the PivotTable, not a value or a subtotal.
Separate January in different years
Month names repeat, and a month-only group can merge records from different years. For example, if a table contains sales from January 2024 and January 2025, grouping only by Months can show one January total combining both periods. That is accurate only if the question is about January seasonality across all years.
For a chronological monthly report, choose both Years and Months in the Grouping dialog. Excel then nests each month under its year, so the two January totals stay distinct. If the reader should compare each year’s January side by side, you can move Years and Months between the Rows and Columns areas to create a different layout.
If Excel has automatically grouped the date field, inspect the field list and the visible labels before adding another grouping. You may already have Years, Quarters, or Months fields. To change the grouping, right-click a grouped date label and choose Group, then adjust the selected intervals; use Ungroup if you need to return to individual dates. Ungrouping removes the date grouping from the PivotTable, not the source dates.
Use a report filter, timeline, or slicer
A report filter is useful when the month selection should sit above the PivotTable instead of appearing in its rows. Drag the grouped Months field, or a date field, into Filters in the PivotTable Fields pane. Use its dropdown above the report to choose a value. For data spanning multiple years, include a Year field too, or select a specific date range so the month choice is not ambiguous.
A timeline provides a visual way to filter a PivotTable by dates. Click inside the PivotTable and choose PivotTable Analyze > Insert Timeline, select the date field, and click OK. Use the timeline’s period control to display months, quarters, or years, then select the desired interval. A timeline works best when the PivotTable source has a consistent date field; it filters the report rather than replacing the source values.
A slicer is another clickable filter, but it is generally intended for fields with discrete labels. After grouping dates, you can insert a slicer for a Year or Month field using PivotTable Analyze > Insert Slicer. A month slicer by itself may select January in every year, so pair it with a Year slicer when the report needs one specific month in one specific year. Timelines and slicers are optional; the ordinary field dropdown is enough for many reports.
Fix dates that will not filter or group
Excel must recognize the source entries as dates for date-specific filtering and grouping to work reliably. A cell can look like a date while actually containing text, especially when data was imported from a CSV or copied from another system. A text date may sort alphabetically, fail to appear in the date-filter menu, or cause Group to be disabled.
- Check the source column: Look for blanks, error values, labels such as Unknown, or dates stored as text. Correct or remove invalid entries according to the reporting rules.
- Check date interpretation: Ambiguous entries such as 03/04/2025 can mean different dates under different regional settings. Confirm the intended day and month before converting imported text.
- Check the selected field: Make sure the PivotTable uses the date column, not a separate text label such as Month Name.
- Refresh after fixing the source: Click in the PivotTable and choose PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh.
Formatting a text value to look like a date does not convert it into a true Excel date. If the source contains text dates, convert them into valid dates in the source data before refreshing. When a column mixes dates and text, resolve the mixed values rather than relying on formatting alone.
Refresh and keep the monthly report current
A PivotTable reflects its source data as of its last refresh. If you add next month’s transactions to a source range that does not include the new rows, refreshing alone may not include them. Using an Excel table as the PivotTable source helps because the table expands as new rows are added. To check or change the source, click inside the PivotTable and use PivotTable Analyze > Change Data Source.
After adding records, refresh the PivotTable and confirm that the new dates are included in the relevant month. If the report uses grouped dates, verify that the new year or month appears as expected. A filter can hide new records even when the source and PivotTable are up to date, so clear or update the month and year filters when results seem incomplete.
For a PivotTable based on an external connection or the Data Model, refresh behavior can depend on the connection and workbook settings. If Refresh does not bring in expected records, check the data connection and the source query or model, not only the visible PivotTable filter.
Excel for Mac and Excel for the web
In current desktop Excel for Mac, the core method is similar: place the date field in the PivotTable, open its dropdown for date filters, or right-click a date and choose Group. Ribbon labels and the location of commands can differ slightly by version and window size, but the same distinction applies: a filter hides dates, while grouping creates month or year categories.
Excel for the web can handle many PivotTable filtering tasks, but its available grouping and PivotTable commands may differ from desktop Excel. If you do not see Group or the date-filter option you need, open the workbook in the Excel desktop app and use the Windows or Mac steps above. The workbook’s source dates and existing PivotTable layout are still the key requirements.
Frequently Asked Questions
How do I filter a PivotTable by month and year?
Group the date field by both Years and Months, then select the desired year and month in the field dropdowns. Alternatively, filter a date field to a bounded date range covering the month and year you need.
Why does January show a combined total for multiple years?
The date field is grouped by Months without Years, so January entries from different years are being combined. Group by Years as well to keep each year’s January separate.
Can I filter a PivotTable by month without grouping dates?
Yes. Put the date field in Rows and choose Date Filters > All Dates in the Period > the month. Use an additional year filter if the source includes several years and you need only one year’s month.
Why is Group greyed out for my date field?
Excel may see one or more source entries as blank, text, or an error instead of a valid date. Correct the source column, refresh the PivotTable, and try grouping an actual date item again.
Does a PivotTable month filter update when I add new data?
Not until the PivotTable includes and refreshes the expanded source. Use an Excel table as the source where practical, add the records, refresh, and then check that the desired month or year is selected.
Related
- Excel Keyboard Shortcut to Highlight a Cell with Color
- Excel Keyboard Shortcuts Mouse Pad: Guide to Choosing
- Excel Keyboard Shortcut for Next Sheet: Ctrl+Page Down
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Pivot Chart Definition, Uses, and How It WorksA PivotChart is an interactive chart linked to a PivotTable that summarizes and displays data by categories, measures,…
- Gantt Chart Percentage Complete Formula in ExcelFor a task-level Gantt chart, calculate percentage complete by dividing the amount of work completed by the total…
- Excel Graphs for Beginners: How to Choose and Make ThemExcel graphs—called charts in Excel—turn rows of worksheet data into a visual summary. To make one in Microsoft…
- Best Free Excel Gantt Chart Templates: 5 PicksFor most people, Vertex42’s Gantt Chart Template is the best free Excel Gantt chart template: it gives you…
- Treemap Chart in Excel: How to Create OneTo create a basic treemap chart in Excel, arrange your categories in columns from broadest to most specific,…
- Excel Waterfall Chart Help: Build, Format, and Fix OneTo create a waterfall chart in Excel for Microsoft 365 on Windows, place each step of your change…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.