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