You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I have a table of various figures (training compliance) ranging from blank cells, 0% to 100%
I want to highlight those cells which are 0% up to 90%.
If I do that simply using the - format cells that on contain - it highlights blank cells as well as those with zero
Any ideas? Thanks
there is a setting in the options that treats empty cells as not zero which if memory serves me correctly fixes that.
Do you know where to find the setting to treat empty cells as not zero? As that would be handy, I only know of the one that makes zero values not print not print on screen/paper.
Explicitly making the contents nothing, rather than being blank fixes the conditional formating, e.g. a formula like =IF(A1>0,A1,""), or doing a find and replace and leave the find field blank and put a space in the replace field.
Easy way to do this - add another conditional formatting rule that sets blank cells to no format. Put it first in the order list and tick stop if true.
Do you know what Kelrons solution sounds familiar. I think i am getting two problems confused.
there is a setting in the options that treats empty cells as not zero which if memory serves me correctly fixes that.
I'm not sure that's the same thing. In Preferences/View is an option "Zero Values" but I think that's just to show "true" zero values as a zero rather than a blank. I might be wrong though.
There's an option for graphs to skip empty cells. Not sure if you can apply it universally.
I’m not sure that’s the same thing. In Preferences/View is an option “Zero Values” but I think that’s just to show “true” zero values as a zero rather than a blank. I might be wrong though.
Yeah you're right. I think i also got confused with showing zero in empty cells.
e.g. a formula like =IF(A1>0,A1,””)
How does Excel get away with being so terrible that people resort to hacks like that?
Anyway, here's another hack so you can do it all in one conditional statement if you prefer.
=AND(NOT(ISBLANK(A1)),A1<90)
This highlights a cell as TRUE if <cell> is not blank and is < 90.
Or just set the font to black bold for everything, then conditionally format anything above 0.9 to white/grey text. The only stuff you'll see is between 0 and 0.9...?