Pivot Table Date Filters

If there’s a date field in your Excel pivot table, you can use it to focus on a specific date, or a selected date range. To filter the dates, you can use the following filter types:

  • Date checkboxes
  • Date range entry
  • Dynamic date range selection

Using Date Checkboxes

If a date field is in the Row Labels area of the pivot table, do the following to show or hide specific dates.

  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. In the list of dates, add check marks to show dates, or remove check marks to hide dates.
  4. Click OK

DateFilter02

Clear a Date Filter

To remove a date filter from a pivot table field:

  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 Clear Filter From [date field name]

Filter for a Specific Date Range

If a date field is in the Row Labels area of the pivot table, do the following to show a specific date range.

  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…

    DateFilter03
     

  4. In the Between dialog box, type a start and end date, or select them from the pop up calendars.
  5. Click OK.

DateFilter04

Filter for a Dynamic Date Range

A Dynamic Date Range is one that changes automatically, as time moves forward. For example, Tomorrow, which will represent a different date, every day that you open the pivot table file.

If a date field is in the Row Labels area of the pivot table, do the following to show data from the current month, as a dynamic date range.

  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 This Month

DateFilter05

Date Filters in the Pivot Table Report Filters

Unfortunately, the Date Range filters and Dynamic Date filters aren’t available in the Pivot Table Report Filters area. If you move a date field to the Report Filters area, only the Checkbox 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 checkboxes were used to select specific dates.

Watch the Pivot Table Date Filters Video

To see the steps in action, please watch this short video on Pivot Table Date Filters.

______________

For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

______________

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in Excel Pivot Tables, pivot table filters, pivot table tutorial. Bookmark the permalink.

2 Responses to Pivot Table Date Filters

  1. Bernie says:

    Thanks! I moved the date from the filter to the row’s and was then able to set it to current month. When it was a filter and i set it to June it was picking up any june data regardless of the year. Now I just get this year’s June! You saved me a ton of time. I am very grateful.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>