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
Is the ° symbol part of the value? If so, the values are text, not numbers, and you should convert them to numbers:
Select the values.
Press Ctrl+H to activate the Replace dialog.
Enter ° in the 'Find what' box and leave the 'Replace with' box empty.
Click 'Replace All', then close the Replace dialog.
Apply the custom number format 00.00° to the selected cells.
The conditional formatting rule(s) should now work as intended.
- 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- Yes that's it! now I format based on true/false. Thank you so much.
