Dynamic Date Range Filters in Pivot Table

In the source data for your pivot table, you can use the drop down filters in the heading cells, to show only the records for a specific date range. For example, in the table shown below, you could show just the orders that were shipped this week.

filterdatedynamic01

However, if you put a date field into a pivot table’s Report Filter area, those dynamic date ranges – Today, Next Month, etc. – aren’t available. You can only select specific dates by checking the boxes.

filterdatedynamic02

Move Date to Row Area

If you’d like to use dynamic date filters in a pivot table, move the date field to the last position in the Row Labels or Column Labels area, instead of the Report Filters area.

Then, click the drop down arrow, and click Date Filters, then the date range, such as This Week.

filterdatedynamic03

After applying the filter, the pivot table only shows the orders that have been shipped this week.

filterdatedynamic04

Hide the Date Details

If you don’t want to see a row for each shipping date, you can collapse the pivot table field.

  • Right-click on the ShipDate heading, and click Expand/Collapse
  • Click on “Collapse Entire Field”

filterdatedynamic05

After collapsing the date field, the Region and Product names show, but the dates are hidden. The ShipDate heading is still visible, and the filter icon shows that the date range filter is still applied.

filterdatedynamic06

To see the dates again,

  • Right-click on the ShipDate heading, and click Expand/Collapse
  • Click on “Expand Entire Field”

To see dates for a specific product

  • Click the + sign at the left of the product name.

Video: Dynamic Date Filters

To see the steps for applying a dynamic date filter, and hiding the dates, please watch this short video tutorial.

Or, watch on YouTube: Select Dynamic Date Range in Pivot Table Filter

Download the Sample File

To download the sample file for this tutorial, please visit my Contextures website: Pivot Table Date Filters

________________________

This entry was posted in Sort and Filter. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *