PivotTable (blank) cells won't display NULL!

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

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 wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
> > 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
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>

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

Abdul Malik Said wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
>
>>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 wrote:
>>
>>>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
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
>
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html