Pivot Table - "For empty cells show:" Option Not Working

[annieexcel]

Hi everybody - I'm trying to replace blank cells in a set of data with 0 in order to utilize the Pivot Table functions. For some reason I cannot get this option to work. I've simply clicked on Pivot Table Options - checked the "For empty cells show:" box and entered 0. I'm stumped as to why this isn't working. Does anyone have any direction for me to troubleshoot with? Would really appreciate it! Thank you!


Version I'm using: 2010

 

[annieexcel]

Thankfully my pivot table is still working without the blanks being replaced by zero's. Still if anyone has any idea I'd love to troubleshoot that issue still - so if anyone has any ideas they would be much appreciated!

 

[sheetspread]

Are you sure they're blank and not "" strings? Test with the isblank() function.

 

[annieexcel]

Thank you! you're right. That formula is coming back "False" ... I'm surprised because nothing is in the cells when I look at them. Since they're not blank do you think I just shouldn't worry about it? The pivot table seems to be working so I guess this isn't an issue. I sort of wish that it had 0's though so it was a bit easier to look at.

Thanks again!

 

[sheetspread]

Since everything seems to work ok regardless, the following steps are optional:

Those fake blanks often accompany imported data. If there's no other text in the range (just numbers and ""s) you can highlight the range, F5(Goto)-Special-Text, type 0, ctrl-enter.

Or to just convert the "" to actual blanks, highlight the whole range, alt-f11, ctrl-G, type selection.value=selection.value, press enter.

 

[annieexcel]

You've been so helpful I really appreciate it!!! I tried to do the first option mentioned however there are other values besides numbers (notably times and dates) ..this is what I see in that option: https://imgur.com/a/OTBX5rI

I'm not sure if this is a version thing that I have no area to enter "0".

 

[sheetspread]

You would enter 0 in the formula bar. Times and dates are numbers unless they're stored as text (test with the isnumber() function). If they are stored as text because the original source was an external database, etc., you can convert to numbers by typing 1 in an unused cell, copy, and paste special multiply to the range.