how to remove the " (blank)" from a pivot table

**exodus125** · 01-20-2010, 11:53 AM

im getting "(blank)" for cells with no data. I want it just to be blank. I know theres a setting that controls this (at least I think there is) but for the life of me I cant seem to find it. Any help will be appreciated.

**exodus125** · 01-20-2010, 12:01 PM

bahh i think i figured it out. jsut did a find "(blank)" then replace with " " before I go ahead and save my work, someone let me know if thats the correct way , or if theres something easier.

**NBVC** · 01-20-2010, 12:04 PM

Right click the table, select Select and then Entire Table.

Go to Format|Conditional Formatting... Select Cell Value Is and then Equal To and then enter formula: ="(blank)"

Click Format and from Font tab select font colour to match your background colour.

Click Ok

Click Ok to close and apply.

**exodus125** · 01-20-2010, 12:15 PM

Originally Posted by NBVC

Right click the table, select Select and then Entire Table.

Go to Format|Conditional Formatting... Select Cell Value Is and then Equal To and then enter formula: ="(blank)"

Click Format and from Font tab select font colour to match your background colour.

Click Ok

Click Ok to close and apply.

would just replacing the "(blank)" with " " be bad? I think I rather replace it with a blank space, vs changing it to a color to match the background.

**NBVC** · 01-20-2010, 12:19 PM

The difference is, that my method allows you to refresh the table and still have the conditional format apply automatically. With your way, you need to do that everytime manually unless you apply a macro of some sort to do it upon refresh...

**exodus125** · 01-20-2010, 12:28 PM

I refreshed and the rows stayed with the " "

**NBVC** · 01-20-2010, 12:30 PM

Originally Posted by exodus125

I refreshed and the rows stayed with the " "

Get the Pivot table to add more items.. so that the "(blanks)" row moves down.. does it still work?

**exodus125** · 01-20-2010, 12:36 PM

yes, the way I have it set up right now, I am using about 500 rows. The pivot table data is 1000 rows, so I quess until I reach 1000 i wont have to worry about that. Even though I will probably never reach more than 1000 rows.

The weird thing is that what caused the (balnk) to change? it would make sense that if I add info on a row, that same cell int eh row that would have appeared (blank) would do it again and id have to replace it again, but it does not. I think the (blank) may be from adding a new row to the pivot table (which I did) but after doing the find replace it fixes it for all the cells within the pivot table for some reason.