1

I have a google sheet with one conditional formatting rule defined: enter image description here

This is the only formatting rule in the sheet: enter image description here

As you can see, Q3 is not obeying the rule. And when I test, neither is Q2. At some point I've inserted new rows at the top, but then I would expect my conditional formatting to show it is missing those cells and it's not.

What is wrong and how do I fix it? If it's not clear, the point is a whole row should be green if the Q cell is empty.

4
  • Consider sharing a publicly editable sample spreadsheet. Jun 3, 2021 at 20:22
  • @doubleunary since the sheet was previously working, I wouldn't know how to reproduce it!
    – Mr. Boy
    Jun 3, 2021 at 20:24
  • @doubleunary though possibly I could clone it and anonymise any sensitive data
    – Mr. Boy
    Jun 3, 2021 at 20:34
  • That would help, so please do that. Jun 3, 2021 at 20:44

1 Answer 1

0

Your conditional formatting custom formula rule should work OK. Since it fails to color rows where column Q appears blank, chances are are that column Q really contains something, for example a space character. Try this rule that will give true when column Q is blank or contains whitespace:

=trim($Q1) = ""

4
  • You have it backwards I think. It is coloring row 3 when the cell definitely contains text (15/05/2021)
    – Mr. Boy
    Jun 3, 2021 at 20:23
  • The rule will return true when the cell in column Q is blank or only contains whitespace, and false when it contains any visible value. Try it as a regular formula in cell R1 to see how it works. When using it in a conditional format rule, make sure you apply the rule for a range that starts in row 1, such as A1:Q. Jun 3, 2021 at 20:33
  • Remove other formatting rules you may have defined, or make them give a different color, so that you know which rule is coloring the cells. Jun 3, 2021 at 20:40
  • As I said, this is the only formatting rule
    – Mr. Boy
    Jun 3, 2021 at 20:41

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.