Conditional formatting with absolute values

Question

Hi all,

I want to get a different coloring based on the absolute value of B3 (i.e if the value of B3 is above 5% and below -5%, then the color should be red, but if within the range of -5% and 5%, then green). Picture is for depicting the situation

Could anyone help me on this?

Many thanks!

Answer 1

Hi Elshan. I am an Excel user like you.

You can do this by entering two formulas as Conditional Formatting rules. This one will give you the rule for absolute value less than or equal to 5% formatted green:

=ABS($B2)<=5%

Make another rule with this formula and format Red:

=ABS($B2)>5%

Highlight all cells/columns you want colored with the conditional formatting before entering each rule.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 2

Many thanks! This worked well!