I have created a Google spreadsheet. It has actual values and calculated values. I want the background to be green if the calculated value is less than the actual value and red if it is more.

It's a pain to individually format each cell and pasting the conditional formatting is not working as I expect. Is there an easier way?

Here's what I managed to do so far.

migrated from stackoverflow.com Jun 2 '14 at 14:29

This question came from our site for professional and enthusiast programmers.


As for other formulae, conditional formatting rules are required to start with =. So for green I adjusted to:

=$G2 <= $B2  

and for red to:

=$G2 > $B2  

Range for both of G2:G5 is fine if you just want the highlighting to apply to ColumnG but if to apply to ColumnsC:G then change Range: to C2:G5.

  • 1
    Ah, I missed the leading =. I accept. – Stephen Rasku Jun 5 '14 at 2:29

First of all, let me rephrase what I understand from your question: you want to change the color of calculated value cells based on the comparison of the calculated value with the actual value, and you try to find a easier way than changing conditional formatting for each cell.

To do that, you can use custom formula of conditional formatting.

Let say actual values are in A2 to A5, and the calculated values are in C2 to C5 and D2 to D5.

  1. Select the C2 cell, choose conditional formatting.
  2. Choose the option "Custom formula is", then enter =C2<$A2 in the field beside it.
  3. Select the formatting that you want.
  4. Enter C2:D5 in the Range field.

Hope this help.

  • It didn't work. My actual values are in B2:B5 and my calculated values are in G2:G5. I adapted your suggestions to this. I set up two rules: if D2 <= $B2 then the set background to green for D2:G5; otherwise if D@ > $B2 set background to red for D2:G5. Nothing is red or green and every calculated cell should be one of those two colors. Are you able to copy my spreadsheet in the original question and edit it? – Stephen Rasku May 31 '14 at 5:25

Your Answer

