0

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. – doubleunary Jun 3 at 20:22
  • @doubleunary since the sheet was previously working, I wouldn't know how to reproduce it! – Mr. Boy Jun 3 at 20:24
  • @doubleunary though possibly I could clone it and anonymise any sensitive data – Mr. Boy Jun 3 at 20:34
  • That would help, so please do that. – doubleunary Jun 3 at 20:44
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 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. – doubleunary Jun 3 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. – doubleunary Jun 3 at 20:40
  • As I said, this is the only formatting rule – Mr. Boy Jun 3 at 20:41

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.