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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Guitarfool5931

Board Regular
Joined
Oct 6, 2008
Messages
83
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?
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750
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).
 

bubbafish

Board Regular
Joined
Jan 20, 2004
Messages
120
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.
 

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,750

ADVERTISEMENT

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.
 

bubbafish

Board Regular
Joined
Jan 20, 2004
Messages
120
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..
 

bubbafish

Board Regular
Joined
Jan 20, 2004
Messages
120
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 " "
 

Number4

Board Regular
Joined
May 20, 2013
Messages
71
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,607
Messages
5,765,396
Members
425,281
Latest member
tmoreira001

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
Top