Highlight Cells Containing Specific Text in Google Sheets

by

To highlight cells containing specific text, select the range, open Format > Conditional formatting, and choose Text contains. Enter the word or phrase, choose a style, and click Done.

Use a custom formula when the search term should come from another cell or when uppercase and lowercase letters need different treatment.

Highlight cells with the Text contains rule

Use this method when every matching cell should contain the same fixed word or phrase.

In this example, the tasks are in A2:A6. The rule should highlight any task containing urgent, including URGENT: client reply.

Here are the steps to highlight cells that contain urgent:

  1. Select A2:A6. Select only the cells you want to color.
  2. Choose Format > Conditional formatting.
  3. Under Format cells if, choose Text contains.
  4. Enter urgent in the value field.
  5. Choose a fill or text style, then click Done.

Google Sheets highlights Send urgent agenda and URGENT: client reply. The observed rule treated uppercase and lowercase letters as matches.

Text contains rule highlights two task cells containing urgent in Google Sheets

Text contains looks for the text anywhere in the cell. Choose Text is exactly when the whole cell must equal the criterion.

Wildcard characters change the match. In a Text contains rule, ? matches one character and * matches any number of characters. Type ~? or ~* when you need a literal question mark or asterisk.

For a one-time manual color, use the fill color options and shortcuts. Conditional formatting updates automatically when a cell changes.

Highlight cells based on text in another cell

Use this method when you want to type a new search term in a cell instead of editing the formatting rule.

Keep the task list in A2:A6 and enter the search term in B2. The custom formula will read B2 for every task cell.

Here are the steps to make the text term editable:

  1. Select A2:A6, then open Format > Conditional formatting.
  2. Under Format cells if, select Custom formula is.
  3. Enter the formula below.
  4. Choose a style and click Done.
=ISNUMBER(SEARCH($B$2,A2))

The formula checks whether SEARCH finds the B2 text within the current task cell. ISNUMBER changes a found position into TRUE, which triggers the rule.

The $B$2 reference stays fixed on the search term. The A2 reference changes to A3, A4, and onward as Sheets evaluates each selected cell.

Google Sheets custom conditional-formatting formula that searches for the term in cell B2

Our absolute reference guide explains why dollar signs lock a reference. If the search term changes, the highlighted cells update without changing the rule.

Highlight cells when case matters

Use this version when urgent should match lowercase urgent but should ignore uppercase URGENT.

Keep the same range and search term cell as the previous method. Replace the custom formula with the one below.

Here are the steps to make the rule case-sensitive:

  1. Open the existing custom conditional-formatting rule.
  2. Replace its formula with the formula below.
  3. Click Done and check the changed highlights.
=ISNUMBER(FIND($B$2,A2))

FIND returns a position when the case matches exactly. With lowercase urgent in B2, Send urgent agenda matched and URGENT: client reply did not.

FIND returns FALSE for uppercase URGENT when the search term is lowercase urgent

Keep the range and formula aligned

A custom conditional-formatting formula must start with the top-left cell of its selected range. For A2:A6, use A2 in the formula.

If you change the range to C5:C20, start the cell reference at C5. A mismatched starting reference can color the wrong cells.

To color an entire row from a value in one column, use our row-highlighting tutorial. That method deliberately applies the rule across several columns.

Other Google Sheets articles you may also like

Sources: Google conditional formatting help and Google SEARCH documentation.

Leave a Comment