Conditional Formatting If Cell Contains Specific Text – Excel & Google Sheets

This tutorial will demonstrate how to highlight cells if they contain specific text using Conditional Formatting in Excel and Google Sheets.

 

 

Highlight Cells With Specific Text

Use a Formula

To highlight cells with certain text defined in another cell, you can use a formula in Conditional Formatting.

1. Select the range you want to apply formatting to.
2. In the Ribbon, select Home > Conditional Formatting > New Rule.

 

 

3. Select Use a formula to determine which cells to format, and enter the following formula (using the SEARCH and ISNUMBER Functions):

=ISNUMBER(SEARCH($F$10,B4)

 

 

4. Cell F10 needs to be locked as an absolute reference. You can do this by using the $ sign around the row and column indicators, or by pressing F4 on the keyboard.
5. Click on the Format button and select your desired formatting.

 

 

6. Click OK, then OK again to return to the Conditional Formatting Rules Manager.

 

 

7. Click Apply to apply the formatting to your selected range, then Close.

This formula returns TRUE when a cell contains the text stored in F9 and formats those cells accordingly.

Notes:

• To find case sensitive text in your cell, use the FIND Function instead of the SEARCH Function in your formula.
• You can also highlight all cells with any text in them.
• To simply find cells with specific text, see How to Find & Select Specific Cells.

Built-in Conditional Formatting Rule

Another option is to use one of the default rules that are in Excel to find if the text you require is in the selected cells.

1. Select the cells you require and then click on Home > Conditional Formatting > Highlight Cells Rules > Text that Contains

 

 

2. Select Cell F10 to indicate the text to find, and then select the formatting you wish to apply.

 

 

3. Click OK.

 

 

Highlight Cells With Specific Text in Google Sheets

Highlighting cells based on their text is similar in Google Sheets.

1. Highlight the cells you wish to format, then go to Format > Conditional Formatting.

 

 

2. The Apply to Range section is already filled in.

 

 

3. From the Format Rules section, select Custom formula is and type in the same formula used in Excel above.

 

 

4. Select the fill style for the cells that meet the criteria.

 

 

5. Click Done to apply the rule.

 

As with Excel, you can also apply Conditional Formatting by selecting “Text contains” rather than “Custom formula is” from the Format Rules section of the Conditional format rules box.