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

Using the AND, OR, and NOT logical functions in conditional formatting helps you create powerful, criteria-based highlighting rules in Google Sheets.

These functions allow you to apply formatting based on single or multiple conditions, making it easier to analyze data, build dashboards, and visually identify patterns such as duplicates, thresholds, or specific categories.

Table of Contents

Quick Answer: AND, OR, NOT in Conditional Formatting (Google Sheets)

Use the following formulas in Format → Conditional Formatting → Custom formula is:

OR (any condition is TRUE):

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

AND (all conditions must be TRUE):

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

NOT (reverse a condition):

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

Sample Data

The sample data contains:

Column A: Expense heads
Column B: Amounts
Range: A1:B18 (headers in row 1)

Sample expense data in Google Sheets with account heads and amounts for conditional formatting examples

So the “Apply to range” will be:

A2:A18
B2:B18
or A2:B18 (for full row highlighting)

Using the OR Function in Conditional Formatting

Use the OR function when any one of multiple conditions should be TRUE.

Example: Highlight Specific Expense Types

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

👉 Highlights cells if the value is either Fuel Expn. OR Toll Expn.

Conditional formatting sidebar in Google Sheets showing OR formula to highlight Fuel and Toll expenses

Apply to Entire Row

To highlight the full row:

Change Apply to range → A2:B18

Add More Conditions

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

Using the NOT Function in Conditional Formatting

The NOT function is used to reverse a condition.

Example: Highlight Everything Except Specific Values

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

👉 Highlights all cells except Fuel and Toll expenses.

Google Sheets data highlighting values not equal to Fuel and Toll expenses using NOT and OR conditional formatting formula

Using the AND Function in Conditional Formatting

Use AND when all conditions must be TRUE.

Example: Multiple Conditions Across Columns

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

👉 Highlights rows where:

Expense = Fuel
Amount ≥ 100

AND + OR Combined (Important Use Case)

This is a very common real-world scenario.

Example: Multiple Categories + Condition

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

👉 Highlights rows where:

Expense is Fuel OR Toll
AND amount is greater than 100

Using AND in the Same Column

Although AND often uses multiple columns, it can also work within a single column.

Example: Condition + Row Logic

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

👉 Highlights:

Only “Fuel Expn.”
Only in even-numbered rows

Google Sheets highlighting Fuel expenses in even rows using AND and ISEVEN conditional formatting rule

Common Mistakes (Why Conditional Formatting Fails)

❌ Forgetting $ (absolute references like $A2)
❌ Applying the formula to the wrong range
❌ Missing quotes around text values
❌ Using incorrect row references
❌ Mixing relative and absolute references incorrectly

These small issues often cause rules to fail or behave unexpectedly.

Additional Benefits of Conditional Formatting

With these logical functions, you can:

Build Gantt charts
Create heat maps
Identify duplicates or anomalies
Use Filter by color or Sort by color for better analysis

Conclusion

Using AND, OR, and NOT in conditional formatting gives you precise control over how your data is highlighted in Google Sheets. Whether you’re applying single conditions, combining multiple criteria, or reversing logic, these functions help you create flexible and dynamic formatting rules.

This tutorial is part of The Ultimate Guide to Conditional Formatting in Google Sheets, where you can explore more advanced techniques, real-world examples, and practical use cases to enhance your spreadsheet workflows.

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,