Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable (blank) cells won't display NULL!
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 FormatConditional 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable (blank) cells won't display NULL!
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" wrote in message ... 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 FormatConditional 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable (blank) cells won't display NULL!
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" wrote in message ... 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 FormatConditional 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking sheets how do I display the zeros (0) as blank cells | Excel Worksheet Functions | |||
Generating blank or null cells that the Histogram Data Analysis tool will ignore | Excel Worksheet Functions | |||
COUNTIF says Null = Blank but Blank < Null | Excel Worksheet Functions | |||
Chart to display only bars for values that are > 3 & blank cells | Excel Worksheet Functions | |||
Skipping Blank Or Null Cells In a Lookup Function | Excel Worksheet Functions |