How to filter a pivot table by date

Lets take a look.

Here we have a pivot table that shows Total Sales only.

Lets 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 lets 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.

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, lets group by month and year.

Then make Year a row label.

For example, we can easily filter to show only sales in June, July, and August.

Be careful with relative date filter options.