Pivot table filter - grouping

A

AJB

I often want to filter the "date" field in my pivot table to show sales in a
specific period e.g. last month only or the last three months. However, in
excel 2003 I am struggling to find how to do this other than having to select
every single relevant date from the drop down filter list, which can be time
consuming. (Previously I could do this in Excel 1997, by right clicking on
the field, and choosing "Group").
Does anyone know of a way I could do this in excel 2003?
Thanks.
 
R

Roger Govier

Hi

The Group option still exists in PT's.
Right click on the field in the PT and you will see Group and Show
Detail. From this there is a sub-menu and Group is the 3rd item.
 
A

AJB

Thanks for your response Roger.

I'm aware that the "Group" option exists, however when I attempt to use it
on my "Date" field in the PT, I get a "Cannot group that selection" message.
I don't know why this is. I presumed it was something to do with the way
Excel 2003 treats PTs since I have only incurred this problem since I
upgraded. Perhaps there is some other explanation.
Do you have any further suggestions? Thanks for your help, much appreciated.

Regards,
AJB.
 
A

AJB

Many thanks Debra, I think that could be the reason for the message - blank
cells.

Perhaps you could help me with another question about grouping dates in PTs?
I'll use an example to explain it:
I have dates going back 12 months in my "Date" field. I use the Grouping
method as previously discussed to see data from months 6, 7, & 8 only.
However, the PT still shows the data from dates before and after the
specified period. Is there a way of hiding this data, so that it is not
included in the grand totals?

Thanks again, AJB.
 
D

Debra Dalgleish

When you group the dates, you can enter a Starting at date, and an
Ending at date. Then, in the Date field dropdown, you can uncheck the
"<" and ">" dates if they appear.

Or include all the dates, and uncheck all except the months you want to see.
 
Top