Conditional formatting in Google Sheets allows for clever formatting rules using logical operators like AND, OR, and NOT. This tutorial will dive into the details of how to use these operators effectively.
The essence of conditional formatting lies in criteria-based highlighting of cells or cell ranges. This makes logical operators an essential component of the process. Whether visualizing Gantt Charts, Heat Maps, or easily identifying duplicates in spreadsheets, conditional formatting is a powerful tool. Additionally, the recent addition of filtering or sorting by color adds another advantage to its functionality.
This tutorial will focus on how to use AND, OR, and NOT in conditional formatting in Google Sheets. We will explore each operator individually in formulas.
How to Use AND, OR, or NOT in Conditional Formatting
Sample Data:
Before diving into the specific operators, let’s start with some sample data to work with. In the example, we will focus on the daily expenses of one of our salespeople.
How to Use OR in Conditional Formatting in Google Sheets
Among the three logical operators, OR is the most commonly used in custom formatting rules. To demonstrate its usage, let’s highlight the “Fuel Expn.” and “Toll Expn.” entries in column A.
OR Conditional Formatting Formula:
=OR(A2="Fuel Expn.", A2="Toll Expn.")To apply this conditional formatting rule, select the range A2:A18 and navigate to Format > Conditional formatting.
How to Use NOT in Conditional Formatting in Google Sheets
The purpose of the NOT logical operator is the opposite of the OR logical operator when it comes to highlighting. This operator allows us to highlight cells in column A that do not contain the text strings “Fuel Expn.” or “Toll Expn.”
NOT Conditional Formatting Formula:
=NOT(OR(A2="Fuel Expn.", A2="Toll Expn."))This formula highlights the cells in column A that do not match “Fuel Expn.” or “Toll Expn.”
How to Use AND in Conditional Formatting in Google Sheets
Unlike OR and NOT, the use of the AND operator in highlighting rules is different. Normally, the AND operator involves comparing values in two columns. Let’s consider the expense data again and highlight column A when the text string is “Fuel Expn.” in column A and the corresponding amount in column B is greater than or equal to 100.
AND Conditional Formatting Formula:
=AND(A2="Fuel Expn.", B2>=100)To apply this rule, follow the same steps as above.
Additional Conditional Formatting Tips
Before we conclude, I would like to address one thing. Typically, the AND operator requires two columns in highlighting rules. However, there are exceptions. For example, we can highlight entries in column A that are equal to “Fuel Expn.” and entered in even rows.
AND in the Same Column in Formatting
Formula:
=AND(A2="Fuel Expn.", ISEVEN(ROW(A2)))That’s all you need to know about using AND, OR, or NOT in conditional formatting in Google Sheets. Enjoy exploring the possibilities this powerful tool offers! For more information and tutorials, visit Crawlan.com.
