In this article, I will show you how to apply conditional formatting based on the text in another cell in Google Sheets. Follow the simple steps below to learn how.
Apply Conditional Formatting if Another Cell Contains Text
For the purpose of this demonstration, let’s consider an example sheet. Click here to access the sheet and follow along.
In this example, our dataset consists of movie titles and genres. We will apply conditional formatting to check if a cell in the Genre column contains the text “Comedy” and apply formatting to the adjacent movie title cell if the condition is met.
1. Select the Cell to Apply Conditional Formatting
Click to highlight the cell. This will be the target cell where the conditional formatting will be applied.
In our example, let’s select the first cell in the movie title column containing “Film 1”.
2. Select Format > Conditional Formatting
In the top toolbar, select Format. In the dropdown menu, click on Conditional Formatting.
3. Expand the Cell Format if… option and select “Custom Formula”
On the right side of the screen, the conditional formatting rules will appear.
In the “Conditional formatting rules” section, there is a dropdown menu titled “Cell Format if…”.
Click on that menu to expand it. At the bottom of this menu, you will find the option to set a “Custom Formula”. Click on it to select.
4. Enter the Custom Formula: =Target_Cell="Text_Value"
Under the “Cell Format if…” dropdown, a new input box will appear. This is where we will enter the custom formula:
=Target_Cell="Text_Value"
Explanation of the formula:
- Target_Cell: This is the cell we are checking to see if it contains the text value.
- “Text_Value”: This is the text that the formula will search for when checking the target cell. The text value must be enclosed in double quotation marks (” “).
In our example, we want to check if the target cell contains the text “Comedy”.
So, the custom formula for our example, using this explanation, will be:
=C4="Comedy"
5. Define Your Formatting Style
Once the conditional formatting rules are set, modify the options under the “Formatting style” label. You can change the text style, text color, and cell color.
In this example, let’s change the cell color to blue if the conditions are met.
6. Select “Done” to See Your Results
To apply the conditional formatting according to your specified conditions, select the “Done” button. The conditional formatting will be applied, and the results will be displayed if the criteria are met.
As you can see in our example, cell C4 contains the text “Comedy”, so cell B4 has turned blue.
You can choose to apply the conditional formatting to other cells by clicking the blue square at the bottom right corner of the cell and dragging it to other cells.
The custom formula will automatically adjust to the new cell information.
In our example, we can now see that the formatting of cells B5 and B7 has also changed to blue based on the text in cells C5 and C7.
Cell B6 remains unchanged because C6 does not contain the text value “Comedy”.
If you enjoyed this article, you might also like our article on how to create alternating colored rows in Google Sheets or our article on how to apply conditional formatting to an entire row in Google Sheets.
And if you’re interested in learning how to send a CSV file via email to Google Sheets, we recommend checking out our detailed guide.
Automate contract renewal reminders from your spreadsheet with ease using our contract renewal reminder software.
Note: This article has been adapted for the purpose of this exercise to align with the bolamarketing.com brand and does not contain any outside information not relevant to the core content.
