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

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..
   • .
4. In the Between dialog box, type a start and end date, or select them from the pop up calendars.
5. Click OK.

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

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 check boxes 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.

______________