Forum Discussion
Office 365 Excel - Bar graph and zero values
Yes, chart converts empty string ("") to zero. As workaround, in G156:R156 you may use formula like
=IF(ISBLANK(I7),-1E-20,I7)
instead of
=IF(ISBLANK(I7)," ",I7)
and apply to these cells custom format
[<0]"";[>=0]0.0
I assume all your numbers are zero or positive ones.
That returns such cells formating
and the same for the chart
Hi Chris,
If you apply custom format like this
it shows zero in desirable format if the value is 0, and nothing if the cell is blank.
Sergei you are the man! I have been pulling my hair out with the custom formatting for this LOL. I was close but kept dancing around the correct answer! Again, thank you so much for your response. I am sure you read this and laughed at my struggle!
Have an amazing day,
Chris Ames
Chris, each of us every day finds something new in Excel, it's infinite. That's why I like it.
Glad to help.
Hi Sergei,
Well....upon further testing it did not fix my issue =( If I uploaded my sheet, would you be willing to take a look at it so you can see exactly what is happening?
Thank you in advance,
Chris
Hi Chris - sure, please submit the sample, with real file it's always easier to find the reason for the issue.