By Jake Sheridan | Published on November 2, 2023
In this tutorial, you will learn how to use conditional formatting with multiple conditions in Google Sheets.
What is Conditional Formatting with Multiple Conditions in Google Sheets?
Conditional formatting is a powerful tool in Google Sheets that allows you to highlight cells that meet certain conditions. For example, you can highlight cells that are greater than a certain value or cells that contain a specific word.
You can also use conditional formatting with multiple conditions, which can be useful if you want to highlight a cell if it meets multiple criteria. For instance, you can format a cell if it is greater than 5 AND less than 10. Or you can format a cell if it is greater than 10 OR less than 5.
In this guide, we will show you how to use conditional formatting with multiple conditions in Google Sheets.
How to Highlight Cells Based on Multiple Conditions in Google Sheets
Here’s how you can highlight cells based on multiple conditions in Google Sheets.
Step 1
First, select the range to which you want to apply conditional formatting. In this example, we will select the range A2:E52.
Step 2
Next, select the Conditional formatting option from the Format menu.
Step 3
Click on the drop-down menu under Format rules to display additional options.
Step 4
Select the Custom formula is option from the drop-down list. This option allows users to specify a custom Google Sheets formula as the conditional formatting rule.
Step 5
Next, we will specify the custom formula to use as the formatting rule. In this example, we want to highlight orders with a shirt size of “S” or “XS”. We can use the formula =($A2=”S”)+($A2=”XS”) to return TRUE only when the value in column A is our preferred shirt size.
After specifying the custom formula, you can adjust how the cell will be highlighted using the options under Formatting style. Click Done to apply these conditional formatting rules to your range.
Step 6
The range should now highlight cells based on multiple conditions. In this particular case, we are highlighting orders for “S” or “XS” shirts.
Step 7
The above example shows how you can add an OR logic to your rules. However, you can also use an AND logic by replacing the plus sign with an asterisk.
For example, the above custom formula highlights cells if their shirt size is “S” and the order quantity is greater than 10.
Step 8
The resulting conditional formatting will apply the AND logic appropriately. All highlighted cells must meet both conditions.
Step 9
Instead of using the “*”, we can also use the built-in AND function. The AND function only returns TRUE if all conditional statements in its arguments also return TRUE.
By using the OR function, we can replace the plus sign as well.
Summary
This guide should provide you with everything you need to use conditional formatting with multiple conditions in Google Sheets.
Feel free to copy this example spreadsheet and try it out for yourself.
For more tips and tricks on Google Sheets, visit Crawlan.com.
