Filter Dates in Pivot Table

Excel provides special filters that you can use to filter dates in Pivot Tables:

  • Checkboxes for individual dates,
  • Specific cell ranges,
  • Dynamic cell ranges,
  • Slicers,
  • Timelines.

Table and Pivot Table

This is the example data we are going to use to create a pivot table.

Create a Pivot Table from this data and move Pivot Table columns to the following fields:

This will create the Pivot Table.

The Order column is the only column that contains dates. This column will be used to show how the filtering works.

Filter Dates Using Checkboxes

The first way to filter dates is to use checkboxes.

Click the filter button, next to Order.

By default, you can select only a single date. If you want to select multiple dates, click Select Multiple Items.

Now, you can select individual dates by checking or unchecking them.

The All checkbox checks all positions. Click it one more time to uncheck them.

Filtering by specific cell ranges

Because the order field is inside the filter report field, only the checkbox filtering is available. If you want to use other types of filtering, move the Order field from the report filter to Columns or Rows.

Let’s place it as the first position in the Rows field.

If you select the specific cell range, the range won’t change if time passes. To start using this type of range, click the filtering icon on Row Labels. There is a new position called Date Filters.

Here, you can select a specific date (Equals), date before or after a selected day, or date between two dates.

To clear the filter, click Clear Filter From “Order”.

Filtering by dynamic cell ranges

A dynamic date range changes when the date changes. For example “last week” today will be different than “last week” next month.

If you want to filter dates by dynamic cell ranges, you have a lot of options. You can select dates from yesterday, today, or tomorrow.

But also last week, this week, next week. You can do it for months, quarters, or years.

You can select dates from the beginning of the year, but also all dates in the selected period, like quarter or month.

Slicers

The next way to filter dates is to use slicers.

To add a slicer, click a pivot table and navigate to PivotTable Analyze >> Filter >> Insert Slicer.

Check Order and click OK. You can use a slicer not only for dates but also for other types of data. That’s why you have multiple options to select.

You can click date to select it or Ctrl + click to deselect.

To clear the filter, click the icon in the top right corner.

Timelines

The last method to filter dates in Pivot Tables is the timeline. To add it, first, click a Pivot Table and navigate to PivotTable Analyze >> Filter >> Insert Timeline.

This time, you can only select fields with dates. That’s why you have only one option to choose: Order.

With timelines, you can drag the beginning and the end dates. If you want to reset the timeline, click the Clear Filter icon in the upper-right corner.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.