Pivot Table Date Filter

Hi,

I am currently getting my Date Filters to look like this

Have.GIF

I want them to look like this with Expand & Collapse Buttons on Years

Want.gif

Seems as If there is some sort of setting I am unable to find.

Can anyone guide me?

Hi,

It looks like you need to click on one of your dates and in the dropdown menu click on GROUP.

See http://www.contextures.com/xlPivot07.html for what will then happen.

NOTE - in any of your dates are text you won't get the correct menu. Make sure you don't have any blank cells in your date range and ALL the dates are real dates.

Hi Marvin,

in any of your dates are text you won't get the correct menu. Make sure you don't have any blank cells in your date range and ALL the dates are real dates.

I have taken care of all that routine stuff.. And the link posted by you is not what I require..

I have also ensured, all the dates on my Pivot are real dates, but I still wont get the right menu..

And yes, If I see the source data and apply an AutoFilter, I do get the Date Filters..

Attaching the sheet for reference

Hi,

I still think you need to right click anywhere in your pivot in the Date Column and GROUP by Month and YEAR. See the attached where I've done that. Is this what you are looking for?

If you take a look at the filter added by you, the expand and collapse buttons are there on the Actual Values on the Pivot, however, in the screenshot attached on Post #1, the Expand/Collapse buttons are there on the filter itself. It is different.

Besides, on sheet name Data, if you click on the filter drop down on the InvoiceDate Column, that is exaclty the type of filter I want on the Pivot.

I know it is 100% possible as I have used it many a times previously, and also the screenshot in Post #1 shows it..