• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table isn't grouping individual dates into months

I have a pivot table row called "Date" which consists of individual dates. When I click the AutoFilter at the top of that field, I still see all individual dates. I would like to see these grouped into months (with an expandable + sign) like AutoFilters. How can I get the pivot table to show this from date's filter list?
 
I'm afraid there is no way - at least I'm not aware - to make a pivot behave like that. There might be workable alternatives, and you might already know them. Then no need to read further.

What is shown in the row label filter are the date values except when in the row area of the pivot you have grouped dates per year, quarter, month. If you expand these fields the filter will show the years if you have selected in the pivot a year, it will show months if you have selected a month. In short a pivot is showing the "dimension" selected.

selected a month in the pivot row
62832

Selected a year in the pivot rows
62833

Expanding can work like a table/range "auto filter"
62834

Or date filter
62835

or use slicers/timeline?
62836
 
As others have mentioned, you need to do few things.

1. Make sure that your dates are indeed date value and not text
Use right click Group by on dates field and see if you can group by year & month. If you can, then it's date value.

2. Then add Year & Month hierarchy columns generated by grouping dates into Filter Field.
You could also add these to Row label field and control which field to use as filter condition.
62848

3. Alternately use slicer or Timeline slicer to control date filtering.

4. If you use Tabular format for Pivot Table design. You can have individual row field in it's own column and control it more like Auto-Filter. But I prefer more compact layout personally.
62850

As pivot tables require hierarchy to manage data slicing, you need these additional columns generated through grouping.
 
Back
Top