How to do conditional formatting in google sheets if another cell contains text?

By /

In Google Sheets, you can set up conditional formatting to change the background color of a cell if another cell contains a certain text. To do this, select the cell where you want the conditional formatting to be applied and click on Format in the menu bar. Then, click on Conditional formatting and enter the cell reference that contains the text you want to use for the condition. Set the background color that you want and click Save. The cell will be formatted only when the cell reference contains the text you specified.

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:

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.

x

Related terms:

  1. Excel: Apply Conditional Formatting if Cell Contains Partial Text
  2. What is Excel: Apply Conditional Formatting if Cell Contains Text
  3. Excel: Apply Conditional Formatting if Cell Contains Formula
  4. How to apply conditional formatting to a cell based on the value in another cell?
  5. How to Check if Cell Contains Text from List in Google Sheets
  6. Google Sheets: How to Calculate Average If Cell Contains Text
  7. How to use conditional formatting based on checkbox in Google Sheets?
  8. How to do conditional formatting with multiple conditions in Google Sheets?
  9. Can I do conditional formatting if another cell is not empty?
  10. How do I use conditional formatting if a cell is less than or equal to a value?