In a pivot table, you can use date filters to show the results for a specific date or date range
In a pivot table, you can use date filters to show the results for a specific date or date range.
There are three types of pivot table date filters:
All three types of filters are shown in the video below, with step-by-step instructions further down on the page.
This short video shows the three types of pivot table date filters, and how to use them. There are written steps below the video
If a date field is in the Report Filter area, only the date check boxes are available. If you want to filter for a date range, move the field to the Row or Column area instead.
To select specific dates in a pivot table filter, follow these steps:
When you create a Pivot Table, Excel groups the dates into years and months. If you'd prefer to see individual dates, follow these steps to change your Excel options.
NOTE: This is an Excel-Level setting, and will affect all pivot tables that you work on.
To remove a date filter from a pivot table field:
For a date field in the Row or Column area of the pivot table, you can select a specific date range for the filter. This option is not available for Report Filters.
A dynamic date range changes automatically, based on the current date. For example, "Tomorrow" represents a different date, every day that you open the pivot table file.
Note: The dynamic date range option is not available for pivot fields in the Report Filters area.
The dynamic date range options are for the following time periods:
If a date field is in the Row or Column area, follow the steps below, to show the current month's data, as a dynamic date range.
If your pivot table is in Compact Layout, all of the Row fields are in a single column. The column heading says "Row Labels".
To choose the pivot field that you want to filter, follow these steps:
If your pivot table is in Outline or Tabular Layout, each Row field is in a separate column.
To see the Sort and Filter options, click the drop down arrow in the field heading cell.
After you click the drop down arrow, you can see all of the Sort and Filter options that are available for the selected field.
After you apply one of the dynamic date filters, the pivot table shows the data from the selected date range only.
In the screen shot below, sales orders from the current week are summared, because the "This Week" filter was applied.
Note: For the week options, dates from the selected week (Sunday to Saturday) are included.
For date fields in the Report Filters area, only the Check box filter type is available. This video shows how to move the filter to the Row area, where dynamic filters are available. Then, collapse the date field, so only the heading is available, and not the list of dates.
Unfortunately, the Date Range filters and Dynamic Date filters aren't available in the Report Filters area. For date fields in the Report Filters area, only the Check box filter type is available.
If you move a filtered date field from the Row Labels area to the Report Filters area, any filtering will be discarded, unless the check boxes were used to select specific dates.
To experiment with the date filters, you can download the zipped sample file. The file is in xlsx format, and does not contain any macros.
Last updated: June 22, 2022 2:11 PM