Conditional Formatting Based on Text in Excel: Rules

Yes. In Excel for Microsoft 365, select the cells you want to format, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, enter the text, and choose a format. For more precise conditions—such as matching a whole cell, checking another column, or combining tests—create a formula-based rule instead.

What text-based conditional formatting does

Conditional formatting changes the appearance of cells when their contents meet a condition. A rule can apply a fill color, font color, bold type, borders, or other available formatting. The formatting is dynamic: if a cell’s value changes so it no longer meets the rule, Excel removes that rule’s appearance and applies any other rules that still match.

Text conditions are useful for making categories and exceptions easier to spot without changing the underlying data. For example, a task list might highlight rows marked “Overdue,” an order table might color cells containing “Returned,” or a support log might flag entries that include “urgent.” The cell still contains its original value; conditional formatting is a visual layer, not a replacement for editing or sorting the data.

Excel can test whether a cell contains a phrase, equals a specified value, or meets a more complex formula. The simplest choice is a built-in rule. Formula-based rules offer more control, including formatting an entire row based on text in one column.

Use the built-in Text that Contains rule

For a straightforward substring match, the built-in rule is usually the quickest option:

Select the cells to check. For example, select A2:A100 if status text is in that range.
On the Home tab, choose Conditional Formatting > Highlight Cells Rules > Text that Contains.
Type the text to find, such as urgent.
Choose one of the offered formats, or select Custom Format to set a fill, font, border, or number format.
Select OK to apply the rule.

If the rule applies to A2:A100, a cell such as “Urgent: call customer” will be highlighted because it contains the search text. This rule looks for the text within the cell rather than requiring the entire cell to equal it. That makes it suitable for descriptions, notes, and longer labels.

To use the same rule on another range, select that range before creating it. To change the rule later, open Home > Conditional Formatting > Manage Rules. In the Rules Manager, choose the relevant worksheet or selection in the “Show formatting rules for” list, then edit or delete the rule as needed.

Choose between contains, equals, and begins with

The right condition depends on what counts as a match. Highlight Cells Rules includes several text-oriented choices, and they are not interchangeable:

Text that Contains finds a sequence of characters anywhere within the cell. Searching for late can match “late,” “Late delivery,” and “The plate is damaged.”
Equal To checks whether the cell’s value matches the specified text, rather than merely containing it. It is a better fit when a status cell should be exactly “Paid.”
A Date Occurring and numeric rules are separate kinds of conditions; they are not substitutes for text rules just because a date or number is displayed in a particular way.

For a “starts with” condition, a formula-based rule is a dependable way to express the requirement. With the text to test in A2:A100 and the rule applied starting at A2, use =LEFT(A2,5)="Order" to match cells whose first five characters are “Order.” The reference A2 is relative, so Excel adjusts it for each cell in the applied range. Change 5 to the length of the prefix you need.

Text comparisons in ordinary Excel formulas are generally not case-sensitive. As a result, an exact test for “Paid” also matches “PAID.” If capitalization matters, use a case-sensitive formula rule instead of relying on a standard text rule.

Create a formula-based text rule

A formula rule is useful when the built-in choices cannot describe the condition, or when the format should depend on a different cell. Select the range to format, choose Home > Conditional Formatting > New Rule, then select “Use a formula to determine which cells to format.” Enter a formula that returns TRUE for cells that should receive the format. Choose Format, set the appearance, and select OK.

For an exact match, assume status values are in A2:A100 and the rule applies to that same range, beginning at A2. Use =$A2="Paid". The dollar sign fixes the column A while the row number adjusts for each cell. This highlights cells whose status is Paid, not cells that merely include those letters in a longer phrase.

For a partial match, assume descriptions are in A2:A100 and the rule is applied to that range. Use =ISNUMBER(SEARCH("urgent",A2)). SEARCH returns the position where the text is found; ISNUMBER turns that result into TRUE when the phrase is present and FALSE when it is not. SEARCH is not case-sensitive, so it matches “urgent,” “Urgent,” and “URGENT.”

To make the same check case-sensitive, with descriptions in A2:A100 and the rule applied starting at A2, use =ISNUMBER(FIND("URGENT",A2)). FIND distinguishes capitalization, so this example matches “URGENT” but not “Urgent.” FIND and SEARCH both return an error when the text is absent; ISNUMBER makes the resulting test FALSE rather than displaying that error in the cell.

Format a whole row based on text in one column

One common request is to shade an entire record when its status column has a particular value. Suppose a task table occupies A2:F100 and the status is in column C. Select A2:F100 before creating the formula rule. With that selection and its top-left cell at A2, enter =$C2="Overdue". Choose a format and confirm. Each row is evaluated using its own column C value, and the matching format is applied across columns A through F.

The reference pattern matters. $C2 locks the status column so it remains C as the format extends across the row, while leaving the row number flexible so Excel checks C2, C3, C4, and so on. If you instead wrote C2="Overdue", the reference could shift across columns as Excel evaluates cells in the selected row. If you wrote $C$2="Overdue", every row would depend on C2, which is rarely the intended result.

For a row to be highlighted when its description contains a word, assume descriptions are in column B and the table is A2:F100. Select A2:F100 and use =ISNUMBER(SEARCH("return",$B2)). This checks column B for each row, without requiring the word to be the whole cell value.

Combine text conditions

Formula rules can combine conditions when a single text test is not enough. For example, you may want to highlight only overdue tasks assigned to a particular team. Suppose status is in C2:C100, team is in D2:D100, and the rule applies to A2:F100 starting at A2. Use =AND($C2="Overdue",$D2="North"). AND returns TRUE only when both tests are true.

To match either of two statuses, assume the same status layout and selected range. Use =OR($C2="Overdue",$C2="Blocked"). OR returns TRUE if at least one condition is true. For several categories, a long OR formula can become harder to maintain; consider keeping the category list in worksheet cells and using a lookup-based design if the list changes frequently.

Be deliberate about blanks. A text-search formula such as =ISNUMBER(SEARCH("urgent",A2)) does not find “urgent” in an empty cell, so it returns FALSE. For an exact match, a blank cell does not equal “Paid.” If you build a more complex formula with several tests, check how it behaves on empty rows so that unused parts of the range do not receive unexpected formatting.

Rule order, overlapping formats, and ranges

A cell can meet more than one conditional-formatting rule. For example, one rule may highlight every cell containing “urgent,” while another colors cells containing “customer.” A cell reading “Urgent customer reply” meets both. Excel evaluates rules in the order shown in Conditional Formatting > Manage Rules. Where rules apply conflicting formatting, priority can affect which format is visible. The Rules Manager lets you move rules up or down; it also offers “Stop If True” for rules where later rules should not be evaluated after a match.

Check “Applies to” in the Rules Manager if a rule misses cells or affects too many. A formula written for a range beginning in A2 should generally be applied to a range whose top-left cell is A2. If the formula refers to A2 but the applied range starts at A3, the relative reference may check the wrong row. For a row-based rule, the “Applies to” range should cover the full rows or columns you intend to format, not just the status cells.

Copying cells can also copy conditional formatting, so rules sometimes spread beyond the original table. Manage Rules is the place to inspect the rule’s scope and remove accidental duplicates. If you want to clear formatting without deleting cell contents, select the affected cells and choose Home > Conditional Formatting > Clear Rules, then choose whether to clear rules from the selected cells or the entire worksheet.

Common problems and practical fixes

The rule finds no matches: Check the spelling and spaces in the search text. A value that looks like “Paid” might contain a trailing space. For an exact formula test, use TRIM when appropriate; with text in A2 and a rule applied starting at A2, =TRIM(A2)="Paid" ignores leading and trailing ordinary spaces for the comparison.
The wrong rows are colored: Verify the formula’s row and column references and confirm the “Applies to” range begins at the cell assumed by the formula. Mixed references such as $C2 are often essential for full-row rules.
A phrase is matched unexpectedly: “Contains” can match part of a longer word. Searching for “art,” for instance, may find “carton.” Use an exact comparison or construct a formula that checks word boundaries if partial-word matches are not wanted.
The rule seems case-insensitive: That is expected for the usual text rule and SEARCH. Use FIND inside ISNUMBER for a case-sensitive partial match.
Text that looks identical behaves differently: Hidden spaces, nonprinting characters, or different punctuation can make values distinct. Inspect the source data or clean it before relying on exact comparisons.

Another source of confusion is the difference between a value and its display. A cell may show a formatted date or number, but the underlying value may not be text. If a condition depends on what the cell displays rather than its actual value, confirm the cell’s data type and choose a rule that tests the intended representation. Likewise, a formula that returns the text “Paid” can be tested by a text rule, even though the cell contains a formula rather than a manually typed label.

Excel desktop, Mac, and web differences

The instructions here target Excel for Microsoft 365 on Windows. Excel for Microsoft 365 on Mac also has conditional-formatting rules and formula-based formatting, though the ribbon layout and dialog appearance can differ slightly. Look for Conditional Formatting on the Home tab; the core approach—select the range, create the rule, and set its format—is the same.

Excel for the web also supports conditional formatting, but some advanced options and management details can differ from desktop Excel and may vary as the web app changes. If a workbook uses complex formula rules or you cannot find a needed control in the browser, open it in the desktop app to inspect or edit the rule. When sharing the workbook, remember that formatting is stored with the workbook, but recipients may interpret the data differently if their source text or rule scope has changed.

Frequently Asked Questions

Can conditional formatting highlight a cell if it contains one word?
Yes. Select the range and use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Enter the word and choose a format. For formula control, use ISNUMBER with SEARCH in a formula rule.

Can I highlight an entire row based on a text value?
Yes. Select the full table range, then create a formula rule that locks the condition column but leaves the row relative. For a table in A2:F100 with status in C, =$C2="Overdue" highlights each matching row.

Is Excel’s text matching case-sensitive?
Standard text rules and SEARCH are generally case-insensitive. For a case-sensitive partial match, use FIND inside ISNUMBER. For a case-sensitive exact comparison, use EXACT in a formula rule.

Can conditional formatting find text in a formula result?
Yes. If a formula in a cell returns text, conditional formatting evaluates the displayed result as the cell’s value. A text rule can match that result just as it can match text entered directly.

How do I remove a text-based formatting rule?
Use Home > Conditional Formatting > Manage Rules to select and delete the rule. To remove rules from a selected range quickly, use Home > Conditional Formatting > Clear Rules and choose the relevant clearing option.

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.