To filter an Excel PivotTable to a date range, open the dropdown for the date field in the PivotTable and choose Date Filters > Between, then enter the start and end dates. For a visual, adjustable range, select the PivotTable and use PivotTable Analyze > Insert Timeline.
What a PivotTable date range filter does
A date range filter limits which source records contribute to the values shown in a PivotTable. For example, if a sales table contains an Order Date column, you can display sales from January 1 through March 31 without deleting or changing the underlying records. The PivotTable recalculates its summaries using the records that meet the filter.
This is different from filtering a normal worksheet table: a PivotTable summarizes its source data and has its own field controls. The date field must be part of the PivotTable layout or available as a report filter before you can use it to control the displayed results. A date filter also does not edit the source data; clearing the filter restores the other dates to the summary.
Use Date Filters > Between
In Excel for Microsoft 365 on Windows, put the date field in the Rows or Columns area if it is not already visible there. Then apply the range filter:
- Click the dropdown arrow beside the date field in the PivotTable. If the field is displayed as Row Labels, use the dropdown for that field.
- Point to Date Filters, then select Between. If Date Filters is missing, see the troubleshooting section below; Excel may not be recognizing the source values as dates.
- Enter the beginning and ending dates in the dialog. For example, enter January 1, 2026 and March 31, 2026 to include the first quarter of 2026.
- Click OK. The PivotTable now summarizes records within the selected range. To change the range, reopen the same menu and edit the dates.
The Between filter is useful when you need precise boundaries and want to type dates directly. Excel’s date-filter options can also include choices such as Before, After, This Month, Last Month, and Year to Date, depending on the field and Excel build. Relative choices are convenient for quick views, while Between is better when the report needs fixed start and end dates.
Make sure Excel recognizes the source dates
Date filtering depends on real Excel date values, not just text that looks like a date. Excel stores a date as a number and displays it using a date format. A value imported as text, such as a date preceded by an apostrophe, may look correct in the worksheet but fail to behave like a date in a PivotTable. Mixed formats can cause the same issue: some rows may contain date serials while others contain text.
Check the source column for blanks, errors, and inconsistent entries. Try changing a sample cell’s number format to General; a real date will display as a number. Also check whether dates were imported in a different regional order—for instance, whether 03/04/2026 means March 4 or April 3. Correct the source values before relying on the filter, then refresh the PivotTable.
If the column contains timestamps as well as dates, a time component can affect the end boundary. A date entered as March 31 is stored at midnight, whereas March 31 at 3 p.m. has a later numeric value. If the range unexpectedly omits records on the final day, add a helper column that removes the time portion and use that field in the PivotTable. With source timestamps in A2:A100, enter =INT(A2) in the helper column and fill it down. For a timestamp of March 31, 2026 at 3 p.m., the result is the date March 31, 2026 at midnight; format the helper cells as dates and refresh the PivotTable.
Filter dates that are grouped by month or year
PivotTables can group date fields into periods such as months, quarters, and years. Grouping makes a report easier to scan, but it changes what appears in the field list. If a date field has been grouped, the PivotTable may show separate fields such as Years and Months rather than one list of individual dates. In that layout, filtering a month or year field is not always the same as filtering the original dates to an exact start and end day.
For a precise daily range, use the original date field and apply Between. If the report is intended to show whole months, grouping can be helpful: right-click a date in the PivotTable, choose Group, and select the periods you need. To report, for example, on February through April, filter the grouped month field, and keep the year field visible or filtered as well. Otherwise, selecting February could include February from more than one year.
Grouping may be unavailable when the date column contains blanks, text, or errors, or when its values are not consistently recognized as dates. Fix those source entries first. If you need to undo grouping, right-click a grouped date and choose Ungroup, where that command is available.
Use a Timeline for an interactive date range
A Timeline is a date-specific PivotTable control. It shows a horizontal time scale with a slider, allowing a reader to select a continuous span and change the view to years, quarters, months, or days. This can be easier to use in a dashboard than opening a menu and typing two dates.
- Click inside the PivotTable.
- On the ribbon, choose PivotTable Analyze > Insert Timeline.
- Select the date field in the list and click OK.
- Use the Timeline’s time-level control to choose a scale, then drag the handles or click and drag across the period to filter.
A Timeline can control a PivotTable only when Excel recognizes the selected field as a date. To connect one Timeline to multiple PivotTables, the PivotTables generally need to share the same source/cache; select the Timeline, open the Report Connections command, and select compatible reports. If the command or Timeline feature is unavailable in your Excel version or platform, the date-field dropdown remains a practical alternative. Excel for the web and Mac can differ from Windows in which PivotTable authoring controls are available, so use the date dropdown if Insert Timeline is not shown.
Use a report filter or a slicer
You can drag a date field into the PivotTable Fields pane’s Filters area to show a report-filter dropdown above the PivotTable. This is useful when you want to choose a date or period without displaying dates as row labels. However, a report filter commonly presents a list of available items; it may be less convenient than Date Filters > Between for entering an exact continuous range. For precise boundaries, place the field in Rows or Columns, set the date filter, and then adjust the layout if needed.
A slicer is a clickable filter panel, but an ordinary slicer is primarily a list of field items. For a continuous date span, a Timeline is the more suitable visual control. Slicers can be useful for categorical fields such as Region or Product alongside a date filter, allowing readers to combine filters without opening multiple menus.
Refresh behavior and keeping the filter useful
Changing a date filter does not add new records to the PivotTable’s source. If the source data gains rows, first confirm that the source range includes them. Converting a source range to an Excel Table before building the PivotTable can make expanding data easier: tables typically expand as new rows are added. Then refresh the PivotTable using PivotTable Analyze > Refresh, or Data > Refresh All to update workbook connections and PivotTables.
After a refresh, check that the new records appear and that the chosen date range still represents the intended period. A fixed range such as January 1 through March 31 will remain fixed; it will not automatically become the latest quarter. If a report must always show a moving period, use an appropriate relative date filter when available or maintain the report’s dates deliberately. Do not assume that refreshing changes the filter’s start or end date.
Common problems and practical fixes
- Date Filters does not appear: Excel may see the field as text, or you may have opened a filter for a different field. Check the source values and select the date field’s own dropdown.
- Some dates are missing: Look for text dates, blank cells, errors, hidden filters, or a source range that excludes rows. Correct the source and refresh.
- The final day is partly missing: Check for timestamps. A helper date-only field created with
=INT(A2)can make filtering by calendar day more predictable. - Months from several years appear together: Include or filter the year field as well as the month field, or filter the original date field with exact boundaries.
- The filter seems not to affect the report: Verify that the PivotTable is built from the expected source and that another filter or report connection is not changing the view. Clear other filters temporarily to diagnose the result.
It is also worth distinguishing a PivotTable filter from a worksheet filter placed on the source table. Filtering the source worksheet may hide rows on screen, but that is not a reliable substitute for setting the PivotTable’s own date filter. Set the filter on the PivotTable so its displayed summary clearly reflects the chosen interval.
Frequently Asked Questions
Can I filter an Excel PivotTable between two dates?
Yes. Open the date field’s dropdown, choose Date Filters > Between, enter the start and end dates, and click OK. The field must be recognized as a date.
Can I filter a PivotTable by month and year?
Yes. You can group dates by month and year, then filter the relevant fields. Keep the year in the layout or filter it too, so a month such as January is not combined across multiple years unintentionally.
Why is Date Filters missing from my PivotTable?
The source field may contain text instead of Excel dates, mixed date formats, blanks, or errors. Check and correct the source data, refresh the PivotTable, and open the dropdown for the date field itself.
How do I show only the current month?
Open the date field’s dropdown and look under Date Filters for a relative option such as This Month, if it is available in your PivotTable. A fixed Between range is another option, but it will not roll forward automatically.
Can one date range filter multiple PivotTables?
A Timeline can connect to compatible PivotTables through its Report Connections command. The reports generally need to share the same source/cache. If they are not compatible, apply filters separately or build them from a shared PivotTable source.
Does filtering a PivotTable delete source rows?
No. A PivotTable filter changes which records contribute to the displayed summary; it does not remove or alter the source records. Clear the filter to show the broader summary again.
Related
- Excel Number Format: How to Show Zero as Blank Cells
- Excel Number Format: Red Negative Numbers in Brackets
- Excel Number Format for Negative Numbers in Parentheses
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Compare Two Excel Sheets With a Pivot TableTo compare two Excel sheets with a PivotTable, combine their rows into one table, add a column identifying…
- Power Pivot Not Working? Fix Common Excel ProblemsIf Power Pivot is not working in Excel for Microsoft 365 on Windows, first enable its COM add-in:…
- Excel Speedometer Chart: How to Create a GaugeExcel for Microsoft 365 does not include a built-in speedometer chart type. You can make a semicircular gauge…
- Add a Series to a Google Sheets Chart: Steps and TipsTo add a series to a chart in Google Sheets, open the chart editor, choose Setup, and select…
- Combo Chart Icon in Excel: Where to Find and Use ItIn Excel for Microsoft 365 on Windows, find the combo chart icon on the Insert tab, in the…
- Excel Radar Chart Fill: Add, Color, and Adjust the AreaTo fill the area of an Excel radar chart, select the chart and choose Chart Design > Change…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.