You can use the custom formula function in Google Sheets to apply conditional formatting to cells based on whether they contain “Yes” or “No” in them.
The following example shows how to use the custom formula function in practice.
Example: Conditional Formatting if Another Cell Contains Text in Google Sheets
Suppose we have the following dataset in Google Sheets that shows whether or not various basketball players are All-Stars:
Suppose we would like to use conditional formatting to apply a green background to each cell with a value of “Yes” and a red background to each cell with a value of “No” in in the All-Star column.
To do so, we can highlight the cells in the range B2:B13, then click the Format tab, then click Conditional formatting:
In the Conditional format rules panel that appears on the right side of the screen, click the Format cells if dropdown, then choose Custom formula is, then type in the following formula:
Then click Done.
Then click Add another rule:
Then click the Format cells if dropdown again, choose Custom formula is, then type in the following formula:
Once you click Done, these two conditional formatting rules will be applied to each cell in the range B2:B13:
From the output we can see:
- Each cell with a value of “Yes” in the All-Star column has a green background
- Each cell with a value of “No” in the All-Star column has a red background
Note: If any cell in the All-Star column had a value other than “Yes” or “No”, it would simply keep a white background color.
The following tutorials explain how to perform other common tasks in Google Sheets: