getting rid of (blank) in Pivot Table

bubbafish

Board Regular
Joined
Jan 20, 2004
Messages
120
In my pivot table I have a lot of cells without any data. The report looks like garbage with all the (BLANK) sohowing up in the report.

I've tried condtional formatting where if the cells = (BLANK) it woulf format them as white -did not work

I tried the pivot table options and clicking on the box "for empty cells show" and set it to 0 then empty but that did not work.


any Ideas???

Thanks
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I don't understand what you're trying to show? Are you trying to show a 0 instead of an empty cell? Have you tried unclickin (blank) under the Column Labels filter?
 
Upvote 0
Drag the field that contains the blank cells into the page area of the pivot, then double click on it and hide items (BLANK) then drag the field back to whereever you want it to be (row area or column area).
 
Upvote 0
Every column in my pivot table might contain a (BLANK) or no data. If I tell it to hide them by clicking the blank off it hides the whole row. I want the pivot table to look just like it is only where there is no data rather than showing a (BLANK).....I don' want to see that word in it.
 
Upvote 0
One way is to go to your data source, highlight the entire area, CTRL+G, ALT+S, ALT+K, type a space (spacebar), CTRL+ENTER.

Then you can refresh your pivot table and the (blank) will be blank.
 
Upvote 0
Thanks! I always forget about the cntl enter to change all the cells at one time.

I just wish the Pivot table Options to set empty cells to show them as " " would work. Sounds like that is what it was designed to do!

Thanks again..
 
Upvote 0
FYI,

I just got the condtional formatting to work
if cell value is ="(blank)"

format font color white.


guess I will nvver know why:
Pivot table Options, set empty cells to show them as " "
 
Upvote 0
One way is to go to your data source, highlight the entire area, CTRL+G, ALT+S, ALT+K, type a space (spacebar), CTRL+ENTER.

Then you can refresh your pivot table and the (blank) will be blank.

This is awesome. Only needed it just now, but it worked. Only thing I'd add for those looking at the steps literally, after 'ALT+K' you need to click on the 'OK' button. Then, wait while Excel locates all the blanks in the Highlighted area, then hit the space bar/type a space (spacebar) and do CTRL+ENTER.

Awesome Awesome Awesome!

I couldn't use Conditional Formatting because the area of concern within the Table was included in a Concatenation, which would have still included the "white" font word '(blank)' that I was trying to avoid.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top