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. – Vidar S. Ramdal Mar 3 '14 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. – Punchlinern Mar 3 '14 at 18:07
  • Can't reproduce your findings either. Can you share the doc with us? – Jacob Jan Tuinstra Mar 3 '14 at 18:16
  • @Punchlinern - It worked for you? Are you using the new Google Sheets? productforums.google.com/forum/#!topic/docs/… – Kirkland Mar 3 '14 at 18:18
  • @Kirkland - I could have sworn it did, but it doesn't any more. Yes, I'm using the new Google Spreadsheets. – Punchlinern Mar 3 '14 at 18:19

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.

  • This worked for me. Thanks! – Kirkland Mar 3 '14 at 18:47

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, privacy policy and cookie policy

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