In this tutorial, you will learn how to use conditional formatting with multiple conditions in Google Sheets.
How To Use Conditional Formatting with Multiple Conditions in Google Sheets
Quick Navigation
Conditional formatting is a powerful tool in Google Sheets that can be used to highlight cells that meet certain conditions. For example, you may want to highlight cells that are greater than a certain value, or cells that contain a certain word.
You can also use conditional formatting with multiple conditions. This can be helpful if you want to highlight a cell if it meets multiple criteria. For example, you could format a cell if it is greater than 5 AND less than 10. Or, you could 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 to highlight cells based on multiple conditions in Google Sheets.
Step 1
First, select the range that you want to add conditional formatting to. In this example, we’ll select the range A2:E52.
Step 2
Next, select the Conditional formatting option under the Format drop-down menu.
Step 3
Click on the drop-down menu under Format rules to reveal 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 a rule for conditional formatting.
Step 5
Next, we will specify the custom formula to use as a rule for formatting. In this example, we want to highlight orders with a shirt size of either “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 indicating the custom formula, you may now adjust how the cell will be highlighted using the options under Formatting style. Click on 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’re highlighting orders for either S or XS shirts.
Step 7
The prior example shows how you can add OR logic to your rules. However, we can also use AND logic by replacing the plus symbol with an asterisk.
For example, the custom formula above highlights cells if their shirt size is an S and the order quantity is over 10.Step 8
The resulting conditional formatting will apply the AND logic appropriately. All highlighted cells must follow both conditions.
Step 9
Instead of using the “*” symbol, we can also use the built-in AND function. The AND function only returns TRUE if all the conditional statements in its arguments also return TRUE.
Similarly, we can use the OR function to replace the plus symbol.
Summary
This guide should be everything you need to use conditional formatting with multiple conditions in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.