.png)
Conditional Formatting If Another Cell Contains Text in Google Sheets
In this article we will show you how to apply conditional formatting based on another cell text in Google Sheets. Simply follow the steps below.
Apply Conditional Formatting If Another Cell Contains Text:
For the purposes of this demonstration an example sheet was created. Click here to access the sheet and follow along at home.
In this example, our dataset contains a Movie Title and a Genre. We will be applying conditional formatting to check if a cell in the Genre column contains the text “Comedy” and apply formatting to the movie title next to it if this condition is met.
1. Select the cell to apply the conditional formatting to
Click to highlight the cell. This will be the target cell and will have conditional formatting applied to it.
In our example we are going to select the first cell in the movie title column containing Movie 1
2. Select Format > Conditional formatting
From the top toolbar select Format. From the menu click Conditional formatting.
3. Expand the Format cells if dropdown and select “Custom formula is”
On the right hand side of the display the conditional formatting rules will show.
In the Format rules section there is a dropdown menu labelled Format cells if..
Expand this drop down menu by clicking on it. At the very bottom of this menu is the option to set Custom formula is, click it to select it.
4. Enter the custom formula: =Target_Cell=”Text_Value”
Underneath the format cells if.. drop down menu a new input box will display. Here we will enter the custom formula:
=Target_Cell=”Text_Value”
Formula Breakdown:
Target_Cell: This is the cell we are checking to see if it has the text value present.
“Text_Value”: This will be the text the formula will look for when checking the target cell. The text value has to be enclosed in double quotations (“ ”).
In our example we want to check if the target cell contains the text “Comedy”
We originally highlighted cell B4, our target cell to check will be the cell next to it C4.
The custom formula for our example using this breakdown is:
=C4=”Comedy”
4. Set your formatting style
With the format rules set, change the options underneath the Formatting style label. You can change the text style, color and cell color.
For this example we want to change the cell color to blue if the conditions are met.
5. Select done to see your results
To set the conditional formatting to your specified conditions select the Done button. The conditional formatting will be applied and results will display if the criteria is met.
As you can see in our example cell C4 contains the text “Comedy” so cell B4 has changed to the color blue.
We can choose to apply the conditional formatting to the other cells by clicking on the blue square at the bottom right hand corner of the cell and dragging over any other cells.
The custom formula will automatically be adjusted to the new cell details.
In our example we can now see that the format of cells B5 and B7 have also changed to cell color blue based on the text in cells C5 and C7.
Cell B6 has remained unchanged as C6 does not contain the text value “Comedy”.
