Hi Laurence

It seems to me that it is a new feature in XL2003, that the PT includes
all options. I don't remember it doing this in earlier versions, but I
agree, that once you select Group by Months for example, all months
appear in the drop down list, even if the underlying data only includes
a single month's worth of data.

--
Regards

Roger Govier


"Laurence Lombard" wrote in message
...
I recently discovered the tickbox "Show all data" in the Field Settings
tab
of a Pivot Table. This is just what I needed as I reference data in
the
table using GETPIVOTDATA and this would return N/A if the field is not
there.

What puzzles me is how does Excel know what all the field values could
be if
they are not used in the underlying list. For example I have a Cash
book
that divided the year into 6 periods. During period 1 the other period
are
not represented but the pivot table displayed all 6 (Actually also
included
a 0 field, which is extra). How does Excel know what all the field
values
could be?

To test the feature I used a list with all 6 periods represented and
deleted
rows with periods other than 1. Could it be that the pivot table
"remembered" all the possible field values.

Thanks
Laurence