Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I created a PivotTable in Excel 2013 and used a date value as a filter. When I want to filter by a range I have to select the individual days. The option looks like:

  • 1/1/2013
  • 1/2/2013
  • 1/3/2013

When I filter the date in the records table the date selection is much more intuitive:

[x]2013
-[ ]January
 -[ ]1
 -[ ]2
 -[ ]3 

Is there anyway to get this format in the pivot table field date filter?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

Nope. (Is this even an acceptable answer?)

You'll find that some "shared" functionalities between Tables and PivotTables aren't always identical - Custom Filters is another one.

But there's an alternative: you can create a few extra columns in your source records table to deal with that. You just have to fill them with a simple formula based on your date cells. That would be Year:

=YEAR(Date)

and Month,to your liking:

=MONTH(Date)                 ' shows 1 (January)
=TEXT(MONTH(Date), "mmm")    ' shows "Jan"
=TEXT(MONTH(Date), "mmmm")   ' shows "January"

You can then use those 2 fields in the "Report Filter" area of your PivotTable as a handy and quick access for filtering your dates.

Another huge benefit of adding these fields is that you can also use them for breaking down your PivotTables by year or month without having to mess with pivot groupings.

share|improve this answer
    
Great option, other than writing VBA code which I have minimal experience with. Thanks. –  RWL01 Jan 20 '13 at 19:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.