Excel Pivot Table Date Filters

In a pivot table, you can use date filters to show the results for a specific date or date range

Three Types of Date Filters

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:

• Individual date check boxes (Report filters only have this type of date filter)
• Selection by specific date range
• Dynamic date range selection, such as last week, or next month

All three types of filters are shown in the video below, with step-by-step instructions further down on the page.

Video: 3 Types of Pivot Table Date Filters

This short video shows the three types of pivot table date filters, and how to use them. There are written steps below the video

Filter With Date Check boxes

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:

1. Click the drop down arrow on date field heading cell
2. To show the check boxes, add a check mark to "Select Multiple Items"
• Otherwise, only the list of dates will show, and you can choose one at a time
3. In the list of dates, add check marks to show dates, or clear a filter checkbox to hide specific dates.
4. Click the OK button, to apply the filter

Prevent Date Grouping in Filters

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.

• On the Ribbon, click the File tab, then click Options
• Click the Data category, and in "Data options", add a check mark to "Disable automatic grouping of Date/Time columns in PivotTables"
  • NOTE: If you don't have that option, you can manually ungroup the dates.
• Click OK to apply the new settings.

NOTE: This is an Excel-Level setting, and will affect all pivot tables that you work on.

Clear a Date Filter

To remove a date filter from a pivot table field:

1. Click the drop down arrow on the field heading
2. If necessary, select the Field name from the drop down list (this step might be necessary for Row Labels, in Compact layout)
3. Click Clear Filter From [date field name]

Filter for Specific Date Range

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.

1. Click the drop down arrow on the Row Labels heading
2. Select the Field name from the drop down list of Row Labels fields
3. Click Date Filters, then click Between…

   

4. In the Between dialog box, type a start and end date, or select them from the pop up calendars.
5. In Excel 2013 and later, there is a "Whole Days" option. Check that option to ignore times that are stored in the date cells.
6. Click OK.

   

Filter for Dynamic Date Range

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.

Dynamic Date Range Options

The dynamic date range options are for the following time periods:

• Single Day: Tomorrow, Today, Yesterday
• Single Week: Next Week, This Week, Last Week
• Single Month: Next Month, This Month, Last Month
• Single Quarter: Next Quarter, This Quarter, Last Quarter
• Single Year: Next Year, This Year, Last Year
• Year to Date

Apply Dynamic Date Range Filter

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.

Pivot Table in Compact Layout

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:

1. In the pivot table, click the drop down arrow on the Row Labels heading
2. In the Select Field box, slick the drop down arrow
3. Select the date field that you want to filter

Pivot Table in Outline or Tabular Layout

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.

Apply a Dynamic Date Filter

After you click the drop down arrow, you can see all of the Sort and Filter options that are available for the selected field.

• In the list of Sort and Filter options, point to Date Filters
• In the pop-up list at the right, click on the dynamic date range that you want to apply as a filter.
• In the screen shot below, the option This Week is selected

Dynamic Date Filter Results

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.

Video: Date Filters in Report Filter Area

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.

Date Filters in Report Filter Area

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.

Download the Sample File

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.

More Tutorials

FAQs - Pivot Tables

Pivot Field Multiple Filters

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table