How to filter a pivot table by date

Excel provides special options for filtering date fields that have been added as a row or a column label.

Let’s take a look.

Here we have a pivot table that shows Total Sales only. Let’s add Customer as a row label, and Region as a column label.

Both of these fields contain text, so we see a standard set of filter options for labels. This includes things like Equals, Contains, Begins With, and so on. The options are the same for both fields.

Now let’s remove Region, and add the Date field. Let's group Date by year.

Excel recognizes that the Date field contains dated information, and alters the options in the field drop-down menu.

The lower section of the menu, the Manual Filter area, contains entries for each year in the field data. We can use these items to manually filter sales data.

Higher in the menu, the entry for Label filters has been replaced by Date filters. The Date filter sub-menu provides a long list of presets for dated information. For example, we can easily filter for all sales before Jan 1, 2013. Or, we can filter for sales between June 2011 and June 2012. We can also filter sales based on relative dates, like last year.

With date fields, any additional grouping will create new filter options. To illustrate, let’s group by month and year. Then make Year a row label. Now we have the option to filter on year, as before, but we also have the option to filter the Date field by month. For example, we can easily filter to show only sales in June, July, and August.

Be careful with relative date filter options. When you are grouping a date field by more than one time unit, the relative date filter options will operate on all filters at once. For example, if we filter on sales last year, using the year label filter, both filters are affected, and our previous filter on June, July, and August is removed in the process.