How to Highlight Cells Containing Specific Text in Google Sheets

Posted June 16, 2016 by Ashley Blood in Google Sheets

Google Sheets

Google Sheets has a built-in conditional formatting tool that allows you to set rules that will pick out cells that meet your criteria and format them according to your specifications. You can use conditional formatting to format Non-Blank Cells, values above or below thresholds you set, or even custom formulas. In this tutorial, I will walk you through how to highlight cells containing specific text. I will use an example list of locations and create a conditional formatting rule that will highlight all the cells that contain the word “Walmart.”

1.Highlight the range of cells to which you want to apply the conditional formatting. Go to Format>Conditional Formatting.Google Sheets Conditional Formatting

2.The Conditional format rules panel appears on the right side of the screen. Click Add new rule.Google Sheets conditional formatting add new rule

3.Check to see if the ranges in the Apply to range box are correct. If not, click in that area to type in the correct range or select it. You can also add multiple ranges to which the rule applies. This is helpful because it means that, once you set up this rule, you can use it throughout your workbook by adding ranges to the rules.Google Sheets Apply Conditional Formatting

4.Now use the Format cells if… box to create your rule. For the purposes of this tutorial, I will choose Text Contains and type in Walmart under that. Other options for text conditional formatting include:

  • Text does not contain
  • Text starts with
  • Text ends with
  • Text is exactlyGoogle Sheets Hightlight if Text Contains

5.Now set the format options. You can choose a default option or manually select the font color, cell background color, and text effects (bold, italics, underline, and strikethrough). Unfortunately, you cannot currently add or change borders using conditional formatting in Google Sheets. I will keep the default formatting, which is a light green highlight.Google Sheets Highlighting Style

6.Your rules are shown real time in your spreadsheet. When everything is working to your liking, click Done.Google Sheets highlight cells containing string

 

About Ashley Blood

Ashley is a mom, engineer, writer, and lover of gadgets and doing things efficiently.
View more articles by Ashley Blood

The Conversation

Follow the reactions below and share your own thoughts.