PivotTable (blank) cells won't display NULL!

[Abdul Malik Said]

I have the following properties set in my pivotTable:


.DisplayNullString = True
.NullString = "NULL"

But it still shows null values in the pivottable as (blank). I hate this
(blank) but it won't go away. Even if I manually edit the table options and
make sure the box is checked that says "For Empty Cells Show: " with "NULL"
in the box.... it still says (blank) in every empty cell!

These are db null values, which I would really prefer to just display
nothing for. No matter how I check or uncheck the "For Empty Cells"
checkbox, it doesn't change the behaviour of the pivottable. I am using
Excel 2002 sp3.

Any ideas why this isn't working?

Malik

 

[Debra Dalgleish]

That setting will affect empty cells in the data area, but not the row
or column areas.

You could hide the (blank) entries with conditional formatting:

Select the cells in the pivot table (at its maximum size)
Choose Format>Conditional Formatting
Leave the first dropdown as Cell Value Is
From the centre dropdown, choose Equal to
In the text box, type (Blank)
Click the Format button
On the Font tab, choose white (or appropriate colour)
to match the cell background
Click OK, click OK

 

[Abdul Malik Said]

Debra,

You immediately saw the problem that I failed to see: my values are not in
the data area of the pivot table! Kind of silly that the table does not have
an option to turn off the (blank)s for the rows and columns areas, but at
least now I know why they wouldn't turn off for me.

Your suggested solution also helps to see a way around it. Thanks a lot!

Malik

 

[Debra Dalgleish]

You're welcome. Thanks for letting me know that the suggestion helped.