Pivot Table - Show "0" instead of (Blank)??

[jerschwab]

I have a pivot table in Excel (2000) which is based on Access data. The
problem is, it shows (Blank) where I would like it to show 0. I looked
around in the preferences for the pivot table and found nothing to my
advantage. I also browsed the forum, but only found ways to
conditionally format, and also a suggestion to alter the source data.

Anyone know of another way to do this? Altering the source data might
be quite difficult (and would seem like a total workaround)!

THANKS!!

 

[BrianB]

Right click in the field and select Field from the dropdown.
Click Number and change the Custom format to include a zero after the
second colon. eg
#,##0;[Red](#,##0);0 ;

This option is also available in the PT setup wizard by double clicking
the field.

 

[jerschwab]

Hi, thanks for the response...

In Excel 2000, when I right click on the field (which happens to be a
row heading)... I only have Format Cells or Field Settings.

Should I go to Format Cells and choose Custom format, then put
#,##0;[Red](#,##0);0; ??

If I do that, then the (Blank) fields show up blank instead of as
zero.

Thanks,

Jeremy

 

[Peo Sjoblom]

You have to realize that if you create a pivot report based on a data source
you cannot change the pivot report,
you have to change the source. How could you ever trust a pivot report if
you could go in and change the data
just like that. You can use a help column and just refer to the cells in the
report

=B5

copy down

now the empty cell will return a zero, then do the average on the help
column

 

[BrianB]

Yes, that should work.

Is your data coming direct from an Access table ? If so it may be
showing Null (empty) rather than a numeric zero. Have you formatted the
Access field as numeric ?

As an experiment, manually type some zeros into a few of the blank
cells in your Access table and refresh your pivot table to see if they
appear.

 

[jerschwab]

Yes, it's coming from Access 2000... and it's numeric.

The field it's coming from is actually from a Crosstab (counted from a
number field).

Will that make any differece?

 

[BrianB]

I guess it does make a difference <<grin>>.
I don't see any reason for connecting a PT to a crosstab.

I have learned from bitter experience that we have to *keep things
simple for Excel* - so, if I need to link, I even go so far as to make
a special table in access to link the PT to -keeping the query in
Access.