Excel Conditional Formatting for Cells Containing Text

Yes. In Microsoft Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, enter the text to find, choose a format, and select OK. For more control—such as testing whether a cell contains any text or matching text case-sensitively—create a rule with a formula instead.

What “contains text” means in Excel

There are two common meanings behind the question. You might want to highlight cells that contain a particular word or phrase, such as “Overdue,” anywhere in the cell. Or you might want to highlight every cell whose value is text, whatever that text says. Excel can do either, but the simplest method depends on which meaning you intend.

The built-in Text that Contains rule searches for the characters you enter within a cell’s text. If you enter North, it can match “North region” and “Headquarters North,” not just a cell whose entire value is exactly “North.” By contrast, a formula using ISTEXT checks the value type rather than looking for a particular word.

Conditional formatting changes how a cell looks when a condition is true; it does not replace or edit the cell’s value. A fill color, font color, border, or other format is applied as a visual cue, while the underlying text remains available for formulas, sorting, and filtering.

Use the built-in Text that Contains rule

For a quick partial-text match, the built-in rule is usually the easiest choice. In Excel for Microsoft 365 on Windows:

Select the cells you want to check. For example, select A2:A100 to check entries in column A.
Choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
Type the word or phrase to look for in the box.
Choose one of the suggested formats, or select Custom Format to set options such as the fill, font, and border.
Select OK, then check a few matching and nonmatching cells.

If the selected range starts at A2, Excel applies the condition to the cells in that range. You do not need to include a formula or an equals sign in the text box. This is useful for labels, notes, status fields, product names, and other lists where a particular phrase signals something worth noticing.

In Excel for Mac, the Conditional Formatting commands are also on the Home tab; choose Conditional Formatting and then the text-related highlight rule. In Excel for the web, select the range and use Home > Conditional Formatting. The available dialogs can look different across platforms, so if you do not see the same preset, use a formula-based rule where available.

Highlight every cell whose value is text

To highlight text regardless of its wording, use a formula rule. Suppose the values to check are in A2:A20. Select A2:A20, choose Home > Conditional Formatting > New Rule, select Use a formula to determine which cells to format, and enter:

With values in A2:A20 and A2 as the top-left cell of the selected range, this formula returns TRUE for text values and FALSE for numbers, dates stored as numbers, and genuinely empty cells: =ISTEXT(A2)

Choose Format, set the appearance you want, and select OK. In the rule formula, refer to the first cell in the selected range, not the whole range. Excel adjusts the relative reference as it evaluates the other cells: the rule checks A3 for the next row, then A4, and so on.

A cell that looks like a number is not necessarily a numeric value. For example, an entry stored as text may be recognized by ISTEXT, while a number with a custom number format is still numeric. Dates in Excel are generally stored as numbers too, so ISTEXT does not identify a date merely because it is displayed in a date format.

Highlight cells that contain a specific phrase

Use the built-in rule when you want a simple, case-insensitive search for a phrase. A formula gives you more flexibility, including the option to make the match case-sensitive or to combine conditions.

With text to check in A2:A20 and A2 as the first selected cell, this formula returns TRUE when the cell contains “late” anywhere in its text; the SEARCH function is not case-sensitive: =ISNUMBER(SEARCH("late",A2))

For example, it matches “Late delivery,” “The order is late,” and “late.” It does not match “On time.” SEARCH returns a position number when it finds the text and an error when it does not; ISNUMBER turns that result into the TRUE or FALSE value a conditional-formatting formula needs.

For a case-sensitive match, use FIND instead. With text in A2:A20 and A2 as the top-left cell, this formula returns TRUE only when the exact lowercase string “late” appears: =ISNUMBER(FIND("late",A2))

FIND distinguishes uppercase and lowercase, so it matches “late” but not “Late.” SEARCH is generally the more convenient choice for ordinary labels, where capitalization is not meaningful. Use FIND when capitalization is part of the data’s meaning, such as a code or identifier.

Match an entire cell instead of a partial phrase

The Text that Contains rule is a partial match. If you want a cell to match a complete value, a formula using equality is clearer. For a selected range beginning at A2, this formula returns TRUE only when the cell’s value is exactly “Approved”: =A2="Approved"

That rule will not match “Approved with notes” because the entire cell value is not equal to the specified text. Excel’s ordinary text comparisons with the equals operator are not case-sensitive, so this formula treats “APPROVED” as equal to “Approved.” If case matters, combine an exact-length check with FIND. With the range beginning at A2, the following formula matches only the exact-case value “Approved”: =AND(ISTEXT(A2),EXACT(A2,"Approved"))

When testing multiple values, consider whether you mean “contains any of these words” or “contains all of these words.” Those are different conditions. For a short list, separate rules may be easiest to understand. A formula can also combine tests, but it should express the intended logic clearly so another person can maintain it.

Choose a rule that fits the job

These approaches solve related but distinct problems:

Text that Contains: Best for quickly highlighting a particular word or phrase, without writing a formula.
ISTEXT: Best for identifying any text value, independent of its wording.
SEARCH: Best for a formula-based partial match that ignores letter case.
FIND: Best for a formula-based partial match that respects letter case.
Equality: Best when the whole cell must equal a specific value rather than merely include it.

Start with the built-in rule if the requirement is simple and the rule is easy to explain. Choose a formula when the built-in dialog cannot express the condition, when case sensitivity matters, or when the condition combines text with another test—for instance, a phrase in one column and a status in another. For more complicated rules, test the formula in a spare worksheet cell first, then use the same logic in conditional formatting.

Relative references, ranges, and rule management

Formula-based formatting depends on the relationship between the formula and the range in Applies to. If your selected range is A2:A20, a reference to A2 is relative and adjusts for each row. If you select B2:B20 but want each cell’s format to depend on the text in column A, use a reference to A2 while keeping the column relative or fixed as needed.

For example, to format B2:B20 according to the corresponding entry in A2:A20, select B2:B20 and use this formula: =ISTEXT(A2) The row reference changes as the rule moves down the range. If you need the rule to always look at column A even when applied across several columns, use a dollar sign before the column letter, as in =ISTEXT($A2). The dollar sign fixes the column while allowing the row to adjust.

To inspect or repair rules, choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, check the range shown under Applies to and confirm that it includes the intended cells. If multiple rules affect the same cells, their order and the Stop If True setting can affect which formatting appears. A rule may be correct but seem ineffective if another rule applies a competing format or stops evaluation first.

Common problems and how to fix them

The formula refers to the wrong starting cell. If the selected range begins at A2, use A2 in the formula. Starting with A1 can shift the test by one row.
A number that looks like text is not highlighted. Check whether the cell contains a true text value or a numeric value with a display format. Formatting a number to look like text does not change its underlying type.
Nothing changes after creating the rule. Confirm the Applies to range, the formula’s first reference, and the chosen format. A very subtle fill or font color can also make a working rule hard to notice.
Extra spaces prevent an exact match. A value such as “Approved ” has a trailing space and is not exactly equal to “Approved.” If the source data is inconsistent, clean it or use a condition designed to allow for the inconsistency.
A formula-generated blank is treated unexpectedly. A formula that returns an empty string is not the same as a genuinely empty cell. Excel can treat that empty string as text for ISTEXT, so a rule may highlight it. If needed, add a test that excludes empty strings.
The match is too broad or too narrow. A contains search finds text within longer entries; equality requires the whole value to match. Recheck which meaning the rule should have.

Frequently Asked Questions

Is Excel’s Text that Contains rule case-sensitive?
It is generally case-insensitive for ordinary text searches. If the distinction between “Late” and “late” matters, create a formula rule using FIND rather than SEARCH.

How do I highlight cells that contain any text?
Select the range, create a formula-based conditional-formatting rule, and use =ISTEXT(A2) when A2 is the top-left cell of the selected range. This tests the type of the value, not whether it includes a particular word.

Can conditional formatting find part of a word?
Yes. The Text that Contains rule searches within the cell’s contents, and a formula using SEARCH can do the same. To require an exact whole-cell value, use an equality test instead.

Why does a cell with a number not count as text?
Excel distinguishes a value from how it is displayed. A numeric value stays numeric even if its number format makes it look like text. A number entered or imported as a text value may be recognized as text instead.

Does conditional formatting change the cell contents?
No. It changes the appearance when a condition is true. The cell’s value remains unchanged, and removing the conditional-formatting rule removes the visual effect rather than rewriting the 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.