Highlight Cells That Contain Specific Text

To highlight cells containing text in Excel, use conditional formatting: select the range, choose Text that Contains, type the word and pick a colour. When you need more control, such as colouring the whole row or matching capitals exactly, a short formula rule with ISNUMBER and SEARCH does the job. This guide shows both on one small order list, so you can spot problem rows at a glance.

The sample data#

All the examples use this order list in A1:C7. The goal is to make every order with "delayed" in its note stand out.

Three notes contain the word: rows 3, 5 and 7. Notice the mix of capitals and the word sitting at the start, middle or end of the text. A good rule has to catch all three.

How to highlight cells containing text with Text that Contains#

The built-in Text that Contains rule is the quickest method and needs no formula. It works the same way on Windows and Mac.

1. Select C2:C7.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
3. Type delayed in the box.
4. Pick a format from the list, such as Light Red Fill with Dark Red Text, and click OK.

Excel colours C3, C5 and C7. The rule is not case-sensitive, so "Delayed" and "delayed" both match. It also matches part of a word: a search for deliver would colour both "Delivered" and "Out for delivery". Pick a search word that is specific enough.

You can also type a cell address instead of a word. Click in the box and then click a cell, such as G1. Excel stores it as =$G$1, and the highlight follows whatever you type in G1.

How to highlight cells with a formula rule#

A formula rule gives you full control. You write a test that returns TRUE or FALSE for the top-left cell, and Excel applies it to every cell in the range.

1. Select C2:C7.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter the formula below, click Format, choose a fill colour and click OK twice.

=ISNUMBER(SEARCH("delayed",C2))

Here is what each part does:

• SEARCH("delayed",C2) looks for the word inside C2. If it finds it, it returns the position as a number: in C3 it returns 1. If not, it returns a #VALUE! error.
• ISNUMBER(...) turns that into TRUE (a number came back) or FALSE (an error came back).

Conditional formatting colours every cell where the formula is TRUE. Write the formula for the first row only (C2) and leave out the $ before the row number, so Excel moves it down to C3, C4 and so on. The SEARCH function guide explains its arguments, and the article on ISNUMBER and SEARCH covers this pair in more depth.

How to highlight the whole row#

To colour the entire row, select all the data and lock the column you check with a $.

1. Select A2:C7 (not the header row).
2. Add a new formula rule with:

=ISNUMBER(SEARCH("delayed",$C2))

The $ before C keeps every cell in the row looking at column C. Without it, the cell in column A would check column A, and the row would be only partly coloured. The row number stays relative, so row 5 checks C5. The guide to highlighting a whole row based on a cell value explains this $ pattern step by step.

How to make the highlight case-sensitive#

Use FIND instead of SEARCH when capitals matter. FIND works the same way but only matches the exact case.

=ISNUMBER(FIND("Delayed",C2))

With this rule, only C3 ("Delayed at customs") is coloured. C5 and C7 use a lower-case "delayed", so FIND returns an error and the cell stays plain. Text that Contains has no case-sensitive option, so a formula rule is the only way.

Other text rules you can use#

The same pattern answers many "highlight if" questions. Swap the formula in the rule:

The G1 version is a handy search box: type a word in G1 and matching cells light up. The $G$1<>"" part matters, because SEARCH("",C2) returns 1 for every cell, so an empty G1 would colour the whole range.

If you want to count the matches rather than colour them, see how to count cells with text. To pull the matching rows into a new list, see how to FILTER rows that contain text. And for repeated values rather than a specific word, highlighting duplicates uses a similar rule.

Why isn't my highlight working?#

When nothing is coloured, or the wrong cells are, the cause is usually one of these:

1. The formula points at the wrong row. The formula must refer to the first row of the selected range. If you selected A2:C7 but wrote $C3, every highlight shifts down one row.
2. A $ in the wrong place. $C$2 locks the row too, so every row checks C2 only. Use $C2 for rows and plain C2 for single cells.
3. A stray space in the search word. "delayed " with a trailing space won't match C7, where "delayed" is the last word. Check the word you typed in the rule or the input cell.
4. An empty search cell. A rule that points at an empty cell matches everything. Add the AND($G$1<>"",...) check.
5. Another rule wins. Open Home > Conditional Formatting > Manage Rules to see every rule on the sheet, their order and the range each one applies to. Fix or delete old rules you don't need.

Practise it#

Try the exercise on this page first: test the rule in a cell before you add it, which is a good habit for any formula rule. Then practise conditional formatting in a live spreadsheet, where you build highlight rules and see them apply, and practise FIND and SEARCH to get comfortable with the text tests these rules depend on.