Excel Conditional Formatting: Find Partial Text Matches

Yes. In Excel for Microsoft 365, use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains to format cells containing a chosen text fragment. For a more flexible rule, select the range and use a formula such as =ISNUMBER(SEARCH("late",A2)), replacing the sample word and cell reference with your own.

What “partial text” means in a formatting rule

A partial-text match means a cell contains a target sequence of characters somewhere inside its value, rather than matching the entire value exactly. If the target is “late,” values such as “late,” “late delivery,” and “The order is late” can all qualify. A value such as “on time” does not. This is useful when cells contain longer descriptions, names, codes, comments, or status notes but you want to draw attention to one recurring word or phrase.

Conditional formatting changes how matching cells look; it does not replace their contents or create a separate list of matches. The formatting updates when a value changes, subject to the rule and workbook calculation. It can apply a fill color, font style, border, or other available format, so the result is visible while the original text remains intact.

Use Excel’s built-in Text that Contains rule

For a straightforward search using one fixed phrase, the built-in rule is usually the quickest approach. In Excel for Microsoft 365 on Windows:

Select the cells you want to check, such as A2:A100.
Choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
Enter the text fragment in the box, choose a formatting option or select Custom Format, and confirm with OK.

Excel creates a rule that highlights cells whose text contains the entered phrase. The format is applied to the cell, not just to the matching characters within it. For example, the rule can fill the whole cell containing “late delivery”; it does not color only the letters “late.” If you need to emphasize only a portion of text inside a cell, conditional formatting is not the right tool for that character-level formatting.

The built-in rule suits a fixed search term and a normal range of cells. It is also convenient if you do not want to write a formula. In current desktop Excel for Mac, the Conditional Formatting commands are available from the Home tab as well; wording or dialog layout can vary slightly by release. Excel for the web also offers conditional-formatting options, though its interface and available rule controls can differ from desktop Excel.

Use a formula for more control

A formula-based rule is useful when the search term should come from another cell, when you want to require more than one condition, or when you need to control case sensitivity. The formula must return TRUE for a cell to receive the format. In the example below, assume the text to check is in A2:A100, and the rule will be applied to that range. Select A2:A100, choose Home > Conditional Formatting > New Rule, select Use a formula to determine which cells to format, enter the formula, choose a format, and confirm.

With the first cell of the selected range at A2 and the target word fixed as “late,” use:

=ISNUMBER(SEARCH("late",A2))

SEARCH returns the position where it finds “late” in A2; ISNUMBER turns that result into TRUE. If SEARCH cannot find the word, it returns an error and ISNUMBER returns FALSE. Because A2 is a relative reference, Excel adjusts it for each row in the applied range: the next row is checked using A3, then A4, and so on. Keep the row and column references aligned with the top-left cell of the range you selected.

To let a user change the search phrase without editing the rule, put the phrase in E1. Assume the cells to format are A2:A100 and E1 contains the search phrase. Use:

=AND($E$1<>"",ISNUMBER(SEARCH($E$1,A2)))

The dollar signs keep the search-term reference fixed at E1 as the rule evaluates other rows. The first condition prevents a blank search cell from matching every value: searching for an empty string can otherwise produce a match. If you want to check a different column while formatting a row, adjust the relative reference. For example, a rule applied to A2:D100 that checks the note in column D for each row would use D2 as its row-relative reference.

Case sensitivity and search behavior

SEARCH is not case-sensitive. A formula looking for “late” therefore matches “Late,” “LATE,” and “late” within a cell. This normally fits status labels and ordinary text searches. If capitalization matters, use FIND instead. Assume the checked values are in A2:A100 and the rule begins at A2; this case-sensitive formula checks for the lowercase sequence “late”:

=ISNUMBER(FIND("late",A2))

FIND and SEARCH both return a position when a match exists, and both return an error when it does not; ISNUMBER converts that outcome into the TRUE/FALSE result conditional formatting needs. SEARCH also recognizes the wildcard characters * and ? in its search text. That can be useful for pattern matching, but it can surprise you if those characters are intended as literal symbols. FIND does not use those wildcard characters as wildcards.

The built-in Text that Contains rule is convenient for ordinary phrases, but choose a formula when case handling, a changing search value, or combined conditions matter. A phrase can appear anywhere in the cell: the rule does not distinguish a whole word from the same characters embedded in a longer word. For instance, searching for “art” can also match “carton.” If you need whole-word matching, a plain contains rule is not sufficient; the formula must account for the characters around the word, and the right approach depends on the punctuation and spacing in your data.

Choose the correct range and references

Many rules that appear not to work are actually checking the wrong cells. Before creating a formula rule, decide what should be formatted and what cell should be inspected. For a range A2:A100, A2 is the correct starting reference if each cell is checked individually. If the intended range starts at A1, a formula referring to A2 will be offset by one row.

Format the same cell you inspect: Apply the rule to A2:A100 and use A2 in the formula.
Format a row based on one column: Apply the rule to the row range and use a relative row reference with a fixed column, such as $D2, when column D holds the status for each row.
Keep a lookup cell fixed: Use an absolute reference such as $E$1 so copying the rule’s evaluation across the range does not move the search phrase.

To review the range and rule, open Home > Conditional Formatting > Manage Rules. In the Rules Manager, inspect the Applies to range and the formula. If the rule is intended for a new or larger data area, adjust Applies to or recreate the rule with the correct selection. When multiple rules affect the same cells, their order and settings such as Stop If True can affect the final appearance. A matching rule may be working but its fill or font can be hidden by a higher-priority rule or another format.

Practical examples

For a delivery log, suppose A2:A200 contains free-text notes and you want to highlight any note mentioning “delayed.” The built-in Text that Contains rule can use the fixed term delayed. It will catch phrases such as “delayed by weather” and “customer reports a delayed shipment,” without requiring the wording to be identical.

For a review tracker, suppose B2:B80 contains comments and E1 contains a word the reviewer chooses. Apply the formula rule to B2:B80 using the E1-based formula above, changing the cell references to match that layout. The reviewer can type “urgent,” “refund,” or another phrase into E1 and the highlighted cells update. If E1 is empty, the guard in the formula prevents every comment from being highlighted.

For a code list, suppose C2:C500 contains identifiers and uppercase and lowercase letters must be treated differently. A case-sensitive FIND rule can check the exact character sequence. Before relying on the result, consider whether identifiers contain punctuation or whether a short sequence might appear in unrelated codes. A match is based on characters, not on the meaning of the value.

Common problems and fixes

Nothing is highlighted: Check spelling, spaces, and the cell range. A trailing space in the search phrase is significant. For a formula rule, verify that the formula reference matches the first cell in Applies to.
Every cell is highlighted: If the search phrase is in a cell, make sure it is not blank. Add the blank check shown above if users may clear that cell.
Unexpected cells match: The search may be finding the phrase inside a longer word, or SEARCH may be matching without regard to capitalization. Use FIND for case-sensitive matching, or choose a more specific phrase.
Formatting seems inconsistent: Review competing rules in Manage Rules, check their order and Applies to ranges, and confirm that the chosen format is visually noticeable.
A rule works for some rows but not others: Look for inconsistent data, such as leading or trailing spaces, and verify that the formula uses relative and absolute references intentionally.

Conditional formatting does not clean or standardize the underlying text. If inconsistent spacing or spelling is the real problem, correct or normalize the source values as a separate step; otherwise, a literal substring rule may miss values that a person considers equivalent.

Conditional formatting versus finding or filtering

Use conditional formatting when the goal is to make matching cells stand out while keeping the full dataset visible. Use Find when you want to locate an occurrence quickly, and use filtering when you want to show only rows that meet a condition. In an Excel table, a text filter such as Text Filters > Contains can narrow the displayed rows, but it serves a different purpose from highlighting: filtering hides nonmatching rows from the current view rather than simply changing their appearance.

These tools can complement each other. For example, conditional formatting can flag every row containing “review” in a large register, while a filter can temporarily show only those rows for review. Neither method changes the cell’s stored text. If you need a permanent classification, a separate helper column or data-cleaning step may be more appropriate than relying on color alone.

Frequently Asked Questions

Can conditional formatting highlight text anywhere in a cell?
Yes. Text that Contains and a SEARCH-based formula both find the phrase within a longer cell value. They format the cell as a whole, not only the matching characters.

Is Text that Contains case-sensitive?
The standard text-contains behavior is not case-sensitive. For a case-sensitive formula rule, use FIND inside ISNUMBER instead of SEARCH.

Can the search word come from another cell?
Yes. Reference the cell containing the term in a formula rule, and make that reference absolute, such as $E$1. Add a check that the search cell is not blank if a blank should match nothing.

Can I highlight a row when one column contains a phrase?
Yes. Apply the rule to the row range and write the formula to inspect the relevant column on that row. Use a fixed column and relative row reference, such as $D2, when the first row in the applied range is row 2.

Why does searching for a word match part of another word?
A contains rule looks for a sequence of characters, not a complete word. Searching for “art,” for example, can match “carton.” Use a more distinctive phrase or build a formula that checks word boundaries appropriate to your data.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.