Apply conditional formatting based on text in a cell

Need to make certain text stand out, such as a product name, or a person’s name? Conditional formatting highlights the text you want to make easy to see.

Quick formatting

In this example, we used conditional formatting to highlight “Northwind Traders Olive Oil” with a green background and dark green text.

Instances of Northwind Traders Oil highlighted in green by using conditional formatting

  1. Select the cells you want to apply conditional formatting to. Click the first cell in the range, and then drag to the last cell.
  2. On the Home tab, click Conditional Formatting > Highlight Cells Rules > Text that Contains.
  3. In the Text that Contains box, on the left, enter the text you want highlighted.
  4. On the right, select the color format for the text, and then click OK.

The selected text is highlighted throughout the worksheet.

Advanced formatting

Use these steps when you don’t necessarily have easily identifiable text to search for. In this example, we used conditional formatting to highlight words that end with “es” with bold red text.

Product names ending with "es" are formatted in bold red text

  1. Select the range of cells you want to apply conditional formatting to.
  2. On the Home tab, click Conditional Formatting > Manage Rules.
  3. In the Conditional Formatting Rules Manager box, click New Rule.
  4. In the New Formatting Rule box, click Format only cells that contain.
  5. Under Format only cells with, select Specific Text in the drop-down box.
  6. Select ending with in the next drop-down box.
  7. In the last drop-down box, enter the example text es.
  8. Click Format, and then click the Font tab.
  9. In the Color box, select a color for the highlighted cells.
  10. In the Font style box, select Bold.
  11. Click OK until you’ve closed the dialog boxes.

The cells with text ending in “es” are highlighted throughout the worksheet.

More about conditional formatting

 
 
Applies to:
Excel 2013