14

I'm trying to use conditional formatting to highlight a cell if it's value is not empty, AND if it's value is not equal to -------.

I've tried every normal formula I use to check if a cell is empty or contains a specified string with no success, how do you do this with conditional formatting?

Edit: This is for a range of cells, not a single cell.

12

Writing a formula that satisfies your criteria is a matter of breaking down what your criteria are and implementing corresponding Sheets functions.

  1. You have established that the formatting should be applied only if a cell is not empty. The EQ function tests whether or not one value (such as a referenced cell's) is the same as another. Since we want to test against an empty cell, we will use "" (the empty string) in our EQ function. So EQ(A1,""). But you want it to return TRUE if the cell is NOT empty, so we will enclose this expression within the NOT function.

NOT(EQ(A1,""))

  1. You also established that the formatting should be applied if the cell's value is not -------. Once again, we can use EQ for this. EQ(A1,"-------"). And again, we'll wrap it in the NOT function to meet your criterium.

NOT(EQ(A1,"-------"))

  1. Finally, you want BOTH of these criteria to be met if the formatting is to be applied. So we'll use the AND function, inputting the two formulae we put together above as the arguments. Your final formula is:

=AND(NOT(EQ(A1,"")),NOT(EQ(A1,"-------")))

Make sure that, when you are creating your conditional formatting rule, you set the condition field to "Custom formula is," or it won't work.


ADDENDUM: Normal Human has offered an alternate formula that is both shorter and easier on the eyes than mine. It utilises logical operators in place of some of Sheets' logical functions and so is not quite as easy to follow without knowledge of these operators. The logic is exactly the same, however. (<> is the operator for "not equal to.")

=AND(A1<>"", A1<>"-------")

  • While this works for a single cell, I need to do this over an entire range, not on a per cell basis. That's where I'm running I to problems. I should have specified this. – Douglas Gaskell Oct 19 '15 at 3:06
  • Not a problem! Replace "A1" in the formula I wrote with your desired range. Are you having trouble with how to specify the range in a formula? If so, what is the range? – SightSpirit Oct 19 '15 at 3:26
  • Maybe I am entering the range incorrectly. I am entering it like D6:I101, is this incorrect? This: NOT(ISBLANK(D6:I101)) should apply the formatting style to any non-blank cell, and it does not. – Douglas Gaskell Oct 19 '15 at 3:42
  • 1
    I think =AND(D6<>"", D6<>"-------") would be more straightforward... @douglasg14b do you have = in front of the formula? – user79865 Oct 19 '15 at 4:16
  • 2
    Thanks for the help! turns out a cell that has it's value set to "" returns FALSE when you test it with ISBLANK(). I needed to just test for "" directly. – Douglas Gaskell Oct 20 '15 at 1:38
2

It is dependent on localisation, but functions operator delimiter in you country may be ";" (semicolon) not ",". If it's so in your country the correct answer will be:

=AND(A1<>"";A1<>"-------")

I spent bunch of time to figure that out in my country, so I wrote it here for those, who have the same problem.

BTW You may also consider using "$" with column name or row number if you plan to use this formula in different columns/rows.

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.