Highlight IF a Cell Contains a Specific Text with Conditional Formatting in Excel

In Excel, just like using a formula, you can use conditional formatting to highlight a cell if it contains a specific text. There are two ways to do this:

• Pre-Defined Option
• Custom Formula

And in this tutorial, we are going to learn both methods.

Cells That Contain a Text

1. Select the range on which you want to apply the conditional formatting.
   
2. Afterward, go to the Home Tab > Conditional Formatting > Highlight Cells Rule > Text That Contains.
   
3. Next, in the dialog box, enter the text you want to look for in the data and select the formatting you want to apply to the matched cells.
   
4. In the end, click OK to apply the conditional formatting.

And when you click OK, it will highlight all the cells where the text “Data” is there.

Highlight Cells That Contain Text using Custom Formula

And the same formatting can be done with the help of a custom formula, and there is one plus point about this method.

You can specify a cell for the text that you want to look for in the data. It makes it a dynamic method; you don’t need to open the conditional formatting option to change it.

1. Select the Data and go to the Home Tab > Conditional Formatting > New Rule.
   
2. In the new rule dialog box, click on the “Use the formula to determine which cell to format”, and enter this formula in the formula input bar =AND($C$1<>””,SEARCH($C$1,A1)>0)
   
3. Next, click “Format…” to specify the format you want to apply to the matched cells.
   
4. In the end, click OK in the dialog box, and again OK to apply the formatting.

When you change the text in cell A1, it will change the conditional formatting in the data.

The formula that we have used text two conditions:

• If Cell C1 is blank or not.
• Is the specified text in the data or not?

When both conditions are true, it highlights the cells which contain that text.