In this article we will show you how to apply conditional formatting with multiple conditions in google sheets. Simply follow the steps below.
For the purpose of the following examples a sample data set has been created. Follow along by clicking this link to access.
When we consider multiple conditions some users may want to use multiple rules while some users want to use multiple conditions in their custom formula. We will explain both in this article.
In Google Sheets we can write a single custom formula that specifies multiple conditions, in the example below we will use this method to apply conditional formatting to all cells where the two conditions are met.
In our example we have a list of 8 people, the amounts of money owed and if they have paid. We want to apply formatting to the names of anybody matching the following two conditions:
Paid = “Yes”
Owed: >30 (Amount greater than 30)
We will demonstrate how to apply conditional formatting to any cells matching both these conditions using a single custom formula.
Select the cell or cell range you want to include in the conditional formatting by clicking and dragging over the cells to highlight them.
To highlight separate cells press and hold the Ctrl key (Cmd ⌘ for mac) and click on the individual cells.
In our example we will select all cells containing data except the header row.
From the file menu select Format then click Conditional formatting.
The Conditional format rules menu will now display on the right hand side of the display.
In the section labeled Format rules expand the Format cells if… drop down menu. Scroll to the bottom and select Custom formula is.
An input box will appear just below the “Format cells if” dropdown menu, here you can enter the custom formula in the following format:
=AND/OR ($Cell_Ref>Value,$Cell_Ref=Value)
Formula Breakdown:
=AND/OR: The AND function specifies that ALL conditions need to be met whereas the OR function specifies that any of the conditions need to be met for the formatting to be applied.
() Formula references need to be enclosed in brackets at the start and the end following the function.
$Cell_Ref: This is the cell reference of the column the formula will check starting from the row number specified in your selection. It is important to know the row number must start at the same number as your selection, you must use the $ symbol before the cell reference if you want to include the entire column.
>: This is the greater than operator, it is used to compare the Value with the Cell_Ref value.
Value: The value is what the formula will check against. Any text based values must be enclosed in double quotations (“ ”) numerical values do not require any quotations.
Our example formula:
=AND($B2>200,$C2="yes")
Our example implemented below will highlight the cells meeting both conditions of values over 200 in Column B AND the text value “yes” in column C.
The formatting style section gives you the option to edit the data in a variety of ways. Text can be amended to be bold, italic, underlined, stricken through as well as the color of the text and the cell can be changed.
In our example we have changed the cell background to green.
Click the done button to apply the formatting.
As can be seen in our example, the formatting has only been applied to the cells where the value of the B column is greater than 200 and the value of the C column is Yes.
Another method of conditional formatting with multiple conditions is to use multiple rules.
In this example we will use multiple conditions to apply formatting to the cells where the text value in Column C = “no” using one rule. Then a second rule for cells where the text value in Column A = “John”.
Select the cell or cell range you want to include in the conditional formatting by clicking on the bottom cell and dragging over the cells to highlight them.
To highlight separate cells press and hold the Ctrl key (Cmd ⌘ for mac) and click on the individual cells.
In our example we will select all cells containing data except the header row.
In the file menu click Format then select Conditional formatting.
The Conditional format rules menu will now display on the right hand side of the display.
In the section labeled Format rules expand the Format cells if… drop down menu. Scroll to the bottom and select Custom formula is.
Enter the new custom formula in the format below:
$Cell_Ref=Value
Formula breakdown:
$Cell_Ref: Cell reference of the column the formula will check starting from the row number selected The row number must start at the same number as your selection, you must use the $ symbol before the cell reference if you want to include the entire column.
=Value: The value is what the formula will check against. Any text based values must be enclosed in double quotations (“ ”) whereas numerical values do not require any quotations.
In our example we want to highlight any cells where the text value is “no” in Column C
Our example formula is:
=$C2="no"
The formatting style section has options to edit the data in many ways. Text can be amended to be bold, italic, underlined, stricken through as well as the color of the text and the cell can be changed.
In our example we will change the cell color to red.
The formatting has been applied to the cells where the value of the C column is the text ‘no’.
We must now add another rule.
Below the done button, click Add another rule.
The new formula will generate as a copy of the first one, in the text box type in the new custom formula in the format below:
$Cell_Ref=Value
Formula breakdown:
$Cell_Ref: Cell reference of the column the formula will check starting from the row number selected The row number must start at the same number as your selection, you must use the $ symbol before the cell reference if you want to include the entire column.
=Value: The value is what the formula will check against. Any text based values must be enclosed in double quotations (“ ”) whereas numerical values do not require any quotations.
In our example we want to highlight any cells where the text value is “John” in Column A
Our example formula is:
=$A2="John"
Use the formatting style section to edit the data in different ways. Text can be amended to be bold, italic, underlined, stricken through as well as the color of the text and the cell can be changed.
In our example we will change the cell color to green to highlight this data from the other rule.
Click the done button to apply the formatting.
As can be seen in our example, the formatting from our first rule has been applied to the cells where the value of the C column contains the text “yes”
We can also see that the formatting from the second rule has been applied to the cells where the value of A column contains the text ‘John’.
If you enjoyed this article, you might also like our article on how to apply conditional formatting to an entire row in Google Sheets or our article on how to change row color based on cell value in Google Sheets.
If you want to learn how to do mail merge in Google Sheets, we also suggest checking out our detailed guide.
