I want to highlight rows where a cell meets a criteria. I have simple table

|     A      |  B   |  C
1| Row number | Name | Val
2|     1      |  Mih |  3
3|     2      |  izz |  4
4|     3      |  don |  2
5|     4      |  ron |  1
6|     5      |      |  
7|     6      |      |  

The rows 6 and 7 (Excel rows, or 5,6 from Row number column), the Val is considered as 0.

I highlighted rows (I chose Use a formula to determine which cell to format with bold and green background color) by formula $C2<3 ... it will highlight rows 6 and 7 (latest two)

I want to exclude those Val is 0.

I tried like =IF(AND($C2<2,$C2>0), TRUE, FALSE) but is not working means I got error from Excel.

Also I tried =AND(C2<2,C2>0), same thing.

excel warning

How to achieve that ?

  • In the statement: "Also I tried =AND(C2<2,C2>0), same thing." By same thing do you mean it highlighted rows 6 and 7 or that you got an error? – Rey Juna Nov 1 '18 at 16:04
  • I tried =AND($C2<2,$C2>0) and it worked for me. Meaning that it highlighted row 5 only. – Rey Juna Nov 1 '18 at 16:10
  • @ReyJuna: excel does not like that form. See updated question – Snake Eyes Nov 2 '18 at 13:11
  • 1
    Use semicolon as separator i.e instead of =AND(C2<2, C2>0) use =AND(C2<2;C2>0) Most likley in your version of Windows ; is the list separator. Windows Control Panel Region & Language - Additional Settings - List Separator – patkim Nov 2 '18 at 13:57
  • @pat2015: Yes, it works, please post as answer and I'll accept it ! – Snake Eyes Nov 2 '18 at 14:05

Sometimes Excel may throw this error while working with Formula in a message box.

There's a problem with this formula. Not Trying to type a formula. When the first character is (=) or minus (-) sign, Excel thinks it's a formula..

and so on.

You feel sure that your formula is correct and still excel does not understand it. One of the reasons for this message is that the list separator in your formula is not what the list separator is set in your version of Windows.

To check the list separator character in your version of Windows, go to Control Panel --> Region & Language --> Additional Settings --> List separator.

enter image description here

Ensure that you are using the same list separator in your Excel formula. Some regional settings default to comma (,) while in other regions semicolon (;)is used.

In the above question, the formula was correct but Excel did not understand it as a formula as the list separator was ; instead of ,

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.