I am using the new Google Spreadsheet application. Here is my data...

  • Cell B3 = 60
  • Cell B5 = 47
  • Cell B6 = 52
  • Cell B7 = 75.1
  • Cell B8 = 68.4
  • Cell B9 = 56

I setup this conditional formatting rule...


For some reason that I do not understand, cell B6 turns red. All the other cells are formatting the way I expect them to be. When I change the "Range" in the conditional formatting rule to only "B6", it does not turn the cell red. Am I misunderstanding the "Range" field? Is this a defect or by design?

Can anyone explain this?

  • I don't think you can use the =sum with Greater than - it needs to be a constant number (e.g. 60). Try replacing Greater than with Custom formula is, and experiment with that. I haven't done much with the new Google Spreadsheets myself, so take it for what it's worth. See this SO post. Mar 3, 2014 at 17:49
  • 2
    It should work, at least it does for me. I don't see the point in using =SUM on only one cell, though. @VidarS.Ramdal =SUM does work with Greater than. Mar 3, 2014 at 18:07
  • Can't reproduce your findings either. Can you share the doc with us? Mar 3, 2014 at 18:16
  • @Punchlinern - It worked for you? Are you using the new Google Sheets? productforums.google.com/forum/#!topic/docs/…
    – Kirkland
    Mar 3, 2014 at 18:18
  • @Kirkland - I could have sworn it did, but it doesn't any more. Yes, I'm using the new Google Spreadsheets. Mar 3, 2014 at 18:19

2 Answers 2


Something seems to be off with the conditional formatting. One solution, anyway, as Vidar suggested, is to use Custom formula is instead of Greater than in the dropdown. Use the formula =B5>$B$3 with your range.


I actually figured out a more direct way than the answer I originally selected as the correct answer. Conditional formatting can use Greater than with a formula. I was just using the wrong formula...

This is wrong... Greater than =sum(B3)

This is right... Greater than =$B$3

Using the $ to separate the column and row selector stops it from being dynamic. Thus, B3 will not become B4, B5, B6, etc, as the formula gets applied to each row. It will always point to the value of B3, even if B3 is itself based on a formula (aka: not static).

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.