Pivot table "Show all data" query

L

Laurence Lombard

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
 
R

Roger Govier

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.
 
B

broro183

Hi Laurence,

Yes, the pivot table will "remember" all the possible field values.

The following web page of Debra's helped me & it may help you updat
your pivot table fields by showing how to remove the redundant items:

http://www.contextures.com/xlPivot04.html

hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience..
 
Top