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

  • Thread starter Thread starter jerschwab
  • Start date Start date
J

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!!
 
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.
 
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
 
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
 
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.
 
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?
 
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.
 
Back
Top