AND, OR, or NOT in Conditional Formatting in Google Sheets

The use of AND, OR, or NOT in conditional formatting is useful to form clever formatting rules. I am explaining this in detail in this new iteration of the Google Sheets tutorial.

The soul of conditional formatting is criteria based highlighting of a cell or a cell range. That makes these logical operators an essential part of it.

With conditional formatting, we can visualize Gantt Charts, Heat Maps, etc. Also, it’s useful in easily identifying duplicates in spreadsheets.

With the recent addition of filter or sort by color, we get one more advantage. We can filter or sort conditionally formatted cells.

There may be more benefits to conditional formatting. But I am using the conditional formatting in Google Sheets mostly for the above purposes.

This tutorial elaborates on how to use AND, OR, or NOT in conditional formatting in Google Sheets. I am Going to use these operators individually in formulas.

How to Use AND, OR, or NOT in Conditional Formatting

Sample Data:

Among the three, the logical operator OR is most common in custom formatting rules. So let me begin with how to use OR in conditional formatting rule.

Also, check the use of AND, OR, NOT Logical Operators in Spreadsheet Formulas.

How to Use OR in Conditional Formatting in Google Sheets

Column A in the above sample data covers the daily expenses of one of our salespeople. In that, I only want to highlight the “Fuel Expn.” and “Toll Expn.”

OR Conditional Formatting Formula:

=OR(A2="Fuel Expn.",A2="Toll Expn.")

Select the range A2:A18 and then from the menu Format, Conditional formatting, use my above OR conditional formatting formula as below.

How to Use NOT in Conditional Formatting in Google Sheets

The purpose of the use of the NOT logical operator is quite opposite to the OR logical operator in highlighting.

I am using the following formula to highlight cells in column A where the text strings are not “Fuel Expn.” or “Toll Expn.”.

NOT Conditional Formatting Formula:

=NOT(OR(A2="Fuel Expn.",A2="Toll Expn."))

When you compare both the above formulas, you can find the similarity that the same above first OR formula used here but wrapped with NOT.

This formula highlights the cells in column A if not matching “Fuel Expn.” or “Toll Expn.”

In the use of AND, OR, or NOT in conditional formatting, the above two (OR and NOT) are easy to learn. But the use of AND in the highlighting rule is different. Unlike the above two, ‘normally’ the AND operator involves two columns.

How to Use AND in Conditional Formatting in Google Sheets

I am considering the expense data again. I want to highlight column A if the text string is “Fuel Expn.” (in column A) and the amount (in column B) is>=100.

AND Conditional Formatting Formula:

=AND(A2="Fuel Expn.",B2>=100)

Hope you can follow the above steps to apply this rule too.

Additional Conditional Formatting Tips

Time to wind up this tutorial. But I think I should clarify one thing. I have said, normally AND operator requires two columns in highlighting rules. But there are exceptions.

In this example I am highlight texts in column A if it’s equal to “Fuel Expn.” and entered in even rows.

Here is the example to AND operator in the same column in conditional formatting rule.

AND in the Same Column in Formatting

Formula:

=AND(A2="Fuel Expn.",ISEVEN(ROW(A2)))

That’s all about the use of AND, OR, or NOT in conditional formatting. Enjoy!

17 COMMENTS

Gary August 7, 2022 At 12:23 am

I would like to highlight a row based on a cell in the row having at least one of two specific words.

- Prashanth August 8, 2022 At 11:18 am
  
  Hi, Gary,
  
  You can use Regexmatch. Here is an example (and also check my answers to other comments).
  
  =regexmatch(D8,"(?i)apple|orange")
  
  It will return TRUE either apple or orange is present.
  
Doug July 27, 2022 At 7:49 am

This is very helpful. Thank you.

Can you do a cell reference, to a cell with a logical operator in it, within conditional formatting?

For instance, you want to test =A1>A3, but rather than typing the > directly into the conditional formatting formula, you have a > in cell A2.

This way, the user can see what logical operator is being used for conditional formatting and change it if needed.

So the conditional formatting formula would be something like =A1&A2&A3 or maybe =A1&"A2"&A3. But these do not work.

Do you know how to accomplish this?

- Prashanth July 27, 2022 At 5:51 pm
  
  Hi, Doug,
  
  We can accomplish this using a Query.
  
  =ifna(query({A1,TRUE},"Select Col2 where Col1"&A2&A3&"",0),FALSE)
  
  In cell A2, feel free to enter any comparison operators. But do remember to put an apostrophe when using the = sign.
  
Elmer Dalangin July 8, 2022 At 10:21 pm

I used this formula, but Google Sheet says it is invalid.

=AND(S3="Worse", R3=>0.01)

- Prashanth July 9, 2022 At 9:24 am
  
  Hi, Elmer Dalangin,
  
  It should be =AND(S3="Worse", R3>=0.01)
  
Jenny April 6, 2022 At 2:22 am

Just wanted to say thanks. This helps a lot.

Angela Duper September 5, 2021 At 8:55 pm

My students can answer 3, 6, or 9. I want to turn all cells red in that column with answers that are NOT 3, 6, or 9. (My first row of data is row 4 in column CG)

I tried this: =NOT(OR(CG4="3",CG4="6",CG4="9"))

But it’s not working. Any ideas what I’m doing wrong?

- Prashanth September 6, 2021 At 8:18 am
  
  Hi, Angela Duper,
  
  You have specified the numbers as text strings.
  
  You can try this.
  
  =NOT(OR(CG4=3,CG4=6,CG4=9))
  
ryan July 7, 2021 At 5:43 am

How do you format 3 consecutive rows based on the value of one of them? I have multiple (same style) headers on a dispatch spreadsheet that I would like to be able to color format based on their status. I can do/have done it for individual lines, but I’m stuck on how to do it for 3 consecutive rows. I can provide an example if needed. I just would not prefer to post my sheet

- Prashanth July 7, 2021 At 2:20 pm
  
  Hi, ryan,
  
  Feel free to share the sheet. I won’t publish that comment/reply.
  
Christopher Slagel July 9, 2020 At 11:15 pm

How would this work with checkboxes?

Normally I would do something like:

=COUNTIF($E2, "=TRUE")

to format a box based on a checkbox, but I’m currently trying to do an AND statement in this scenario:

Both F2 and G2 have checkboxes. I want G2 to turn red if F2 is true AND G2 is false. How might I accomplish this?

Thanks!!

- Prashanth July 10, 2020 At 11:14 am
  
  Hi, Christopher Slagel,
  
  Instead of Countif, you can simply use the formula $E2=TRUE.
  
  Regarding your new requirement, I mean the AND use, use this one.
  
  =and(F2=TRUE,G2=FALSE)
  
phil March 18, 2020 At 10:52 pm

Is it just I or the separator between conditions when using the custom formula should be a semicolon ‘;’ instead of a comma ‘,’? In my case I couldn’t enter the ‘,’ between elements in a condition.

- Prashanth March 19, 2020 At 10:42 am
  
  Please refer to Regional Settings.
  
loe January 3, 2020 At 3:55 pm

Hi,

Is it possible to refer the A2 in your example to a list of words?
=OR(A2="Fuel Expn.",A2="Toll Expn.")

- Prashanth January 4, 2020 At 7:19 am
  
  Hi, Loe,
  
  It’s possible not with OR logical function but with REGEXMATCH.
  
  Here is the alternative to the formula you have quoted in your comment.
  
  =not(regexmatch(A2,"Fuel Expn.|Toll Expn."))
  
  To use it in a list follow the below example.
  
  If I have my list in D2:D3 (D2 contains “Fuel Expn.” and D3 contains “Toll Expn.” I can rewrite the formula as below.
  
  =not(regexmatch(A2,textjoin("|",true,$D$2:$D$3)))
  
  Replace $D$2:$D$3 with the range containing your list.
  
  Best,