I have column B with different values on different rows and I want to set a rule that , for example cell b3 turns red if the value is smaller than that of B2 and green if it's bigger. I know I need to use conditional formatting, but not sure how?

I tried the solution here (How to conditional format in Google sheets based on cell directly above it?) but that didn't work.

So what I want to say is: "if the cell's value is lower than that of the cell above it, color red, if the cell value is higher than that of the cell above it, color green".

2 Answers 2



You need 3 custom format settings, all applide to the range B3:B. The First is set to

Format cells if...     Cell is empty
and the fill is set to None

The second and third are Custom Formula where the Formula are:

For red:


and for green:



enter image description hereYou need 2 conditional format rules. Both applied to cells B2:B, with Custom formula is selected. In the first, set the formula to:


and set the color to the Red you want to see. In the second, change the formula to


and select the green color you wish.

  • I'm afraid that doesn't work. Firstly, it doesn't take into account that there are 2 header rows and secondly, it doesn't seem to look at the right cells at it sometimes colors red cells that are smaller than the previous one, and sometimes those that are bigger than the previous one. Mar 6, 2017 at 16:30
  • Then make a minor change to the values. Apply to range B3:B and change the custom formula to =B3<B4 and =B3>B4 It is working in my test sheet. I added an image to my post showing the changes.
    – Karl_S
    Mar 6, 2017 at 16:39
  • I think there's a misunderstanding. Wat I need is the cell to turn green if its value is ANY value higher than the value in the previous cell and red if it's ANY value lower than the value in the previous cell. That's not the case in your image. Mar 6, 2017 at 18:41
  • See the updated answer.
    – Karl_S
    Mar 6, 2017 at 19:28

This is what I did, hope this helps

enter image description here enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.