You can use the custom formula function in Google Sheets to apply conditional formatting to cells based on whether or not another cell contains specific text.
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 contains information about various basketball players:
Suppose we’d like to highlight each of the cells in the Team column based on the text in the corresponding cells of the Rating column.
To do so, we can highlight the cells in the range A2:A11, 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:
=C2="Great"
Once you click Done, each of the cells in the Team column that has a value of “Great” in the Rating column will be highlighted with a green background:
You can also apply conditional formatting to cells that have other text values by clicking Add another rule:
You can then repeat the process to specify background colors for the team names that have “Good” or “Bad” in the Rating column:
Each of the cells in the Team column will receive conditional formatting based on the corresponding value in the Rating column:
From the output we can see:
- Each Team with a Rating of “Great” has a green background color.
- Each Team with a Rating of “Good” has a yellow background color.
- Each Team with a Rating of “Bad” has a red background color.
If any Team had a Rating value other than these three values, it would simply keep a white background color.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Google Sheets: Conditional Formatting If Date is Before Today
Google Sheets: Conditional Formatting with Multiple Conditions