Excel Pivot Table Date Range: How to Filter

To filter an Excel PivotTable to a date range, open the drop-down for the date field in the Rows or Columns area, choose Date Filters > Between, and enter the start and end dates. For a visual, adjustable range, select a cell in the PivotTable and use PivotTable Analyze > Insert Timeline.

How date filtering works in a PivotTable

A PivotTable summarizes records from a source table or range. A date filter limits which source records contribute to the summary; it does not change or delete the source data. For example, if a sales table has one row per transaction, filtering the Order Date field to January through March includes transactions in that period in the PivotTable totals.

In Excel for Microsoft 365 on Windows, place the date field in Rows or Columns, then use the field’s drop-down arrow in the PivotTable. Choose Date Filters and select a condition, such as Between, Before, After, This Month, or Last Quarter. The available choices depend on the field and how it is arranged. Enter the dates in the dialog and confirm. Excel updates the displayed summary to include records meeting the condition.

The filter controls the date field, not the values being summarized. If you want to show only dates where total sales exceed a threshold, that is a Value Filter on the relevant row or column field, not a date-range filter. Keeping those two kinds of filters distinct makes it easier to diagnose an unexpected result.

Check that the source contains real dates

Excel’s date filters depend on values Excel recognizes as dates. Dates are stored as serial numbers and displayed using a date format. A value that merely looks like a date may instead be text, especially if it was imported from a CSV, copied from another system, or entered using a format that does not match the computer’s regional settings. Text dates may be sorted alphabetically and may not produce the expected Date Filters menu.

Before building or troubleshooting the PivotTable, inspect the source date column. Try changing a sample cell’s number format to General: a real date normally displays as a number, while text remains text. Also check for blanks, error values, and mixed entries such as dates alongside words like Pending. Correct the source column first, using a consistent date format and genuine Excel date values, then refresh the PivotTable.

When the source contains timestamps, Excel can still filter by date, but values may include a time as well as a day. This matters at the end of a range: a record on the final date at 3:00 p.m. is later than midnight at the start of that date. If a boundary result seems absent, check whether the source values include times and consider using a date-only helper column or a range that accounts for the time component.

Apply a custom start and end date

Click inside the PivotTable and make sure the date field is in Rows or Columns. If it is only in the Filters area, move it to Rows or Columns when you need the Date Filters commands.
Open the drop-down beside the date field’s heading. If the field is nested under other row fields, use the arrow for the date field itself, not the overall Row Labels heading.
Choose Date Filters > Between. Depending on the Excel build and layout, you may first need to point to or expand Date Filters.
Enter the beginning and ending dates in the filter dialog, then select OK. Use unambiguous dates, such as 1-Mar-2025, if regional date order could cause confusion.

For instance, a report intended to cover the first quarter of 2025 can use 1-Jan-2025 as the beginning and 31-Mar-2025 as the ending date. A between condition includes the boundary dates, so transactions dated on January 1 and March 31 are within the selected period. If you are filtering a field that contains timestamps, check the time issue described above rather than assuming every value on the last calendar day is identical.

To remove the condition, reopen the field drop-down and choose Clear Filter From followed by the field name, where that command is available. You can also use the filter menu to select all dates. Clearing a filter does not remove the date field or undo other PivotTable layout choices.

Use a Timeline for an adjustable date range

A Timeline is a visual date filter designed for PivotTables. Click in the PivotTable, choose PivotTable Analyze > Insert Timeline, select the date field, and confirm. The Timeline displays a scale that you can set to Years, Quarters, Months, or Days. Drag across the scale to select a period; use the selection handles to extend or narrow the range. This can be more convenient than reopening a dialog whenever a report’s period changes.

A Timeline works with one date field at a time. It is especially useful on a dashboard where readers need to adjust a period without opening the field menu. It filters the PivotTable rather than changing its source records. If the command is unavailable, first verify that the selected PivotTable has a usable date field and that the field contains actual dates. The standard Date Filters menu is a practical fallback.

In current Microsoft 365 releases for Mac, PivotTable tools and Timeline support may depend on the installed build, and ribbon placement can differ from Windows. Look for the PivotTable Analyze tab and its Timeline command; if it is not present, use the date field’s filter menu instead. Excel for the web also has a different ribbon layout, so the exact sequence may vary. The underlying principle is the same: filter a recognized date field, then refresh if the source has changed.

Choose between date filters, Timelines, and grouping

These features are related, but they solve different tasks. Choose the method that matches how precise the report needs to be and how readers will use it.

Date Filters: Best for a precise, fixed condition such as dates between two specific days, dates before a cutoff, or a relative period offered in the menu.
Timeline: Best for interactive reports where someone needs to adjust a continuous range visually, especially by month, quarter, or year.
Grouping: Best for organizing dates into useful categories, such as months or quarters, rather than selecting a custom continuous interval.
Manual item selection: Useful for a small set of separate dates, but less suitable for a changing range because each item must be selected or cleared individually.

Grouping is available from the PivotTable context menu: right-click a date in the PivotTable and choose Group. Excel offers grouping choices such as months, quarters, and years when the field supports them. Grouping changes the way dates are displayed and summarized; it is not the same as filtering the underlying records to a start and end date. For example, grouping by months can show monthly totals for all years, while a date filter can restrict the source records to a particular quarter.

Common problems and how to fix them

Date Filters is missing. Confirm that the field contains genuine dates and is placed in Rows or Columns. If Excel has grouped the dates, the visible field may be a generated Years or Months field rather than the original date field. Try filtering the original date field or use the Timeline where available. A text-formatted date column is another common cause.

Some dates do not appear in the expected order. Mixed text and date values can sort inconsistently. Correct the source values, then refresh. Also look for blank cells: blanks can appear as a separate item and may prevent date grouping until they are handled.

The result includes old or new records unexpectedly. Check the actual year as well as the month and day in the filter dialog. A report filtered to January through March without a year-specific condition can include those months across multiple years, depending on the filter or grouping used. For one particular quarter, specify both boundary dates or use the appropriate year and quarter grouping.

New source dates are not included. Refresh the PivotTable using PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. Refresh updates the PivotTable’s view from its source; it does not always expand a source range that was defined too narrowly. An Excel Table used as the source generally expands when new rows are added, while a fixed cell range may need to be changed through PivotTable Analyze > Change Data Source.

The last day seems partly missing. Check for timestamps and the range boundaries. A record with a date-time value late on the final day is not equal to a date value stored at midnight. Ensure the filter covers the intended records, or prepare a consistent date-only field in the source when day-level reporting is required.

Keep the report reliable as data changes

For recurring reports, make the source data a structured Excel Table before creating the PivotTable. Select a cell in the source and choose Insert > Table, confirming whether the table has headers. A table is easier to maintain as rows are appended than a fixed range, although the PivotTable still needs to be refreshed to show new records. Avoid inserting blank rows or putting notes inside the source table, since those entries can affect fields and summaries.

Also document whether the report uses calendar years, fiscal years, or a rolling period. A filter from April 1 through June 30 is a calendar-quarter range, but it may not match an organization’s fiscal quarter. If a report is shared, make the selected date period visible in a heading or nearby cell so readers do not mistake a filtered result for a full-year total.

Frequently Asked Questions

Can I filter a PivotTable by a date range? Yes. Put the date field in Rows or Columns, open its drop-down, choose Date Filters > Between, and enter the start and end dates. The source values must be recognized by Excel as dates.

How do I filter a PivotTable by month and year? Use Date Filters to enter the exact month’s first and last dates, or use a Timeline set to Months. If you group dates, include the year when needed; selecting March alone may represent March in more than one year.

Why does my PivotTable show Label Filters instead of Date Filters? Excel may not recognize the field as a date, or the field may be a grouped label such as Months or Years. Check the source column for text dates and inspect which field’s drop-down you opened. Use the original date field where possible.

Does filtering a PivotTable change the source data? No. It changes which records are displayed in the PivotTable summary. The underlying rows remain in the source table or range, and clearing the filter makes the omitted records available to the summary again.

How do I make a PivotTable date filter include new rows? Add records within an Excel Table used as the source, then refresh the PivotTable. If the source is a fixed range, update it with PivotTable Analyze > Change Data Source before refreshing.

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.