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
Sergei,
Ok, wow that worked perfectly! I understand how the custom formatting change reads and works. However, could you school me a little bit on how your new formula works in the cells G156:R156 please? I am not sure what the "-1E-20" is doing or how it works that replaces the show blank "".
Thank you in advance,
Chris
Hi Chris,
These cells are not blanks or numbers, values into them are returned by formula. Formula in your variant returns some positive number or text (actually space or empty string). Within the chart empty strings are interpreted as zeroes when we apply number formats. The idea for the workaround is to have numbers only. Instead of empty string formula returns quite small negative number (1e-20 in this case) which from visualization point of view is equivalent of nothing.
After that we apply custom number format which for negative numbers shows empty string, for positive ones applies desired number format. I assume here what you don't have real negative numbers in data set.
Yukontornado69 , thank you. But that's not the universal solution, just workaround for this concrete case. For another data that could be something else.
Sergei,
That is a very interesting way to solve this problem! Meaning that I would have NEVER gotten to that answer, lol. I would have danced around a solution for a very long time. I appreciate your skills and I hope that I can reach out to you in the future to steal more of your knowledge!