Forum Discussion
Solved
Conditional Formatting degrees
how do I get the condition to change color if a value is less than 90°? The formatting doesn't seem to recognize or understand the values in °.
- You could use text to number tool each time or use a helper column that will strip the degree symbol and convert to number or use custom formula in conditional formatting like:
=(--LEFT(K2,LEN(K2-1))<90
or maybe
=(--SUBSTITUTE(K2,"°",""))<90
So the data in these cells is imported from another program into an Excel Template. Is there a way to format these cells in the template to automatically remove the "°"?
You could use text to number tool each time or use a helper column that will strip the degree symbol and convert to number or use custom formula in conditional formatting like:
=(--LEFT(K2,LEN(K2-1))<90
or maybe
=(--SUBSTITUTE(K2,"°",""))<90
=(--LEFT(K2,LEN(K2-1))<90
or maybe
=(--SUBSTITUTE(K2,"°",""))<90
- Yes that's it! now I format based on true/false. Thank you so much.
actually in Conditional formatting - new rule - there is a "Use a formula to determine which cells to format" and you can just enter the formula there
just make sure the formula is based on the upper left cell of the Applied To range so the above formula is based on highlighting and applying to a range starting in K2 and going down but if you highlight the whole column (K:K) then the first cell is K1 and you should use K1 in that formula instead of K2. (it is treated as if you entered the formula in that cell and copied/filled down/right accordingly so if you start in K1 but enter K2 then every cell will get highlighted based on the value of the cell below it.)