Excel Pivot Table - Date Range Filter

Question

I'm pretty new to Excel. I've created a pivot table and everything works how I want it to except for one thing, the date filter. By default by using the report filter, all of the dates pulled from my spreadsheet are in the dropdown list and they have to be checked individually to filter the table.

I was wondering if there is a work around to be able to filter the table using a date range, exactly how it can be done using the date filter in the spreadsheet. Why I want to be able to do is filter my pivot table by month rather than having to manually select all of the dates in the given month. Can this be accomplished using VBA?

kevinw690 · Accepted Answer · 2012-09-24 14:14:18Z

1

I was able to get this done without writing any code. It turned out to be quite simple. In order to get more functionality out of my date filter, I temporarily moved the date field from the report filter to the row labels. From here I grouped the date field by months and then just moved it back over to the report filter.

answered Sep 24, 2012 at 14:14

kevinw690

391 gold badge1 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bernhof Over a year ago

This doesn't work for me in Excel 2010. It simply removes the special date filter when moved back to report filter area.

Sean Over a year ago

I didn't know how to group, for anyone else: chandoo.org/wp/2009/11/17/group-dates-in-pivot-tables

Stepan1010 · Accepted Answer · 2012-09-21 20:20:30Z

0

Excel has pretty robust built in date filter options on pivot tables(which version of excel are you using):

PivotTableDateFilters

Good Luck.

EDIT:

Make your pivot table dynamic. This macro will update your pivot table:

Sub UpdatePivotTable()
    Sheets(1).PivotTables(1).RefreshTable
End Sub

You can edit/sort/filter your initial data however you want.

EDIT2: So you might be able to make use of something like this - then you can just autofill x's into the dates that you want - and then run the pivot table update macro: Custom Report Filter1

Good Luck.

edited Sep 21, 2012 at 20:20

answered Sep 21, 2012 at 14:40

Stepan1010

3,1341 gold badge18 silver badges21 bronze badges

3 Comments

kevinw690 Over a year ago

I'm using Excel 2010. The date filter built in to the spreadsheet works fine, which is exactly what I want for my pivot table/chart. I have the date column from my spreadsheet as the report filter in my pivot table and the only filtering option is selecting all the dates or selecting individual dates.

kevinw690 Over a year ago

Thanks for the response, but I'm already using a dynamic range for my pivot table's data sourcde so it's automatically updated with new data added to the spreadsheet. I'm still unsure of what I need to do to make a more functional report filter.

Stepan1010 Over a year ago

You create your own range select column and then use it as a report filter and then update the report with the macro provided above - see my second edit screenshot.

Collectives™ on Stack Overflow

Excel Pivot Table - Date Range Filter

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related