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.
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.
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
From the top toolbar select Format. From the menu click Conditional formatting.
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.
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”
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.
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”.
If you enjoyed this article, you might also like our article on how to make alternating color rows in Google Sheets or our article on how to apply conditional formatting to an entire row in Google Sheets.
If you want to learn how to email CSV to Google Sheets, we also suggest checking out our detailed guide.
Use our contract renewal reminder software to easily automate contract reminders from your spreadsheet in just a few clicks.
