Excel Conditional Formatting Based on Text: How to Set It Up

How-to basics · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains and enter the text to match. For more control—such as highlighting an entire row based on one cell—create a formula-based rule instead.

What text-based conditional formatting does

Conditional formatting changes how cells look when they meet a condition; it does not change their contents. A rule can apply a fill color, font color, border, or other available format when a cell contains particular text. This makes it useful for bringing attention to statuses such as Late, Paid, or Review, or to comments containing a keyword.

The built-in text rule is a quick choice when the cell you want to format is also the cell that contains the text. For example, it can highlight each cell in a status column that contains the word Pending. A formula rule is more flexible: it can test one column while formatting a different column or a whole row, and it can combine text tests with other conditions.

Conditional formatting is a visual aid, not a data-cleaning tool. If a cell contains a misspelled status or an unexpected space, a rule may not behave as intended. The cell’s underlying value remains unchanged even when a color or font style appears.

Use the built-in Text that Contains rule

For a straightforward match, use the ribbon rule rather than writing a formula:

  1. Select the cells to format, such as C2:C100. Avoid selecting the entire worksheet unless you have a specific reason; a focused range is easier to manage.
  2. Choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
  3. Enter the text to look for in the dialog box, choose a suggested format or a custom format, and select OK.

Suppose the selected range contains task notes and you enter urgent. Cells containing that text are formatted, including text such as Urgent: call the supplier. The rule looks for the entered text within the cell rather than requiring the entire cell to equal it. For an exact match, use a formula-based rule described below.

The Highlight Cells Rules menu also includes text-related options such as text that begins with and text that ends with. These can suit standardized codes: for example, a prefix rule can distinguish records beginning with a particular department code. The exact options available in a dialog depend on which rule you choose.

Match an exact status or format a whole row

To color a row according to its status, select the entire area that should receive the format, then base the rule on the status column. For example, assume a table occupies A2:F100 and its status is in column C. Select A2:F100, choose Home > Conditional Formatting > New Rule, and select Use a formula to determine which cells to format. Enter this formula:

With statuses in C2:C100 and A2:F100 selected, use =$C2="Late" to format each row whose status is Late.

Choose Format in the New Formatting Rule dialog to set the appearance, then confirm the rule. The dollar sign before C locks the test to column C as Excel applies the rule across the row. The row number is not locked, so Excel checks C2 for the first row, C3 for the next, and so on. If your selection starts on row 5 instead, make the formula refer to C5.

For an exact match in a single column, select C2:C100 and use the same formula, =$C2="Late". In this situation, the column lock is not essential, but it keeps the example consistent and is useful if the range is later expanded across columns. Excel’s ordinary text equality comparison is not case-sensitive, so Late and LATE match the same rule. Extra spaces or additional words can prevent an exact match.

Highlight text that appears anywhere in a cell

A formula is helpful when a cell should be formatted if it contains a word or phrase as part of a longer entry. Assume comments are in C2:C100. Select that range, create a formula-based rule, and use:

With comments in C2:C100 selected, use =ISNUMBER(SEARCH("urgent",C2)) to format a cell when urgent appears anywhere in its text.

SEARCH returns the position where the text is found and ISNUMBER turns a successful search into TRUE, which is what the conditional-formatting rule needs. SEARCH does not distinguish uppercase from lowercase, so it finds urgent, Urgent, and URGENT. It can also find the word inside a longer string, such as urgently; if you need to match a whole word rather than a sequence of characters, a simple contains rule may be too broad.

If letter case matters, use FIND instead. With comments in C2:C100 selected, =ISNUMBER(FIND("Urgent",C2)) matches that capitalization but not URGENT. FIND and SEARCH are both valid worksheet functions; choosing between them depends on whether case-sensitive matching is important.

Match a prefix or one of several approved values

For structured identifiers, you may want to test the start of the text rather than search anywhere in the cell. For example, with invoice codes in A2:A100 selected, =LEFT(A2,3)="INV" formats codes whose first three characters are INV. LEFT returns the specified number of characters from the start of a text value. If the selected range begins on a different row, adjust the reference to the first cell in that range.

When formatting should depend on whether a status appears in an approved list, keep the allowed values in a separate range. Suppose the statuses to recognize are in H2:H10, and the data statuses are in C2:C100. With C2:C100 selected, =COUNTIF($H$2:$H$10,C2)>0 is TRUE when the value in C2 appears in the list. The dollar signs keep the list range fixed as Excel evaluates the rule for each row.

A maintained list is easier to update than a long formula containing many separate conditions. It also makes the accepted status values visible to other people using the workbook. Check that the list and data use consistent spelling and spacing; a near-match is not automatically the same value.

Choose between a built-in rule and a formula

Use the built-in Text that Contains rule when the goal is simply to format the cells that contain a phrase. It is quick to set up and easy to inspect later. Choose a formula-based rule when the condition and the cells being formatted are different, or when the condition needs to combine tests.

  • Format matching cells: Use Text that Contains for a simple substring match in the selected range.
  • Format entire records: Use a formula that checks a specific column while the Applies to range covers the row or table.
  • Require an exact status: Use a direct equality test such as the Late example, rather than a contains search.
  • Check several allowed values: Use a list range with COUNTIF, especially if values will change over time.
  • Distinguish capitalization: Use FIND for a case-sensitive substring test; SEARCH ignores case.

For a formula rule, Excel evaluates the formula for the cells in the Applies to range. A formula returning TRUE applies the chosen format; FALSE does not. References therefore matter: a relative row reference should follow each record, while dollar signs should lock any column or range that must stay fixed.

Review rule order and the Applies to range

Conditional formatting can contain several rules for the same cells. If two rules set conflicting formats, rule order can affect the result. Open Home > Conditional Formatting > Manage Rules to review the rules, change their order, edit them, or inspect the range shown under Applies to. In the manager, check whether it is showing rules for the current selection or for the whole worksheet before drawing conclusions about what exists.

The Stop If True option can prevent lower-priority rules from being evaluated when a higher-priority rule is true. Use it deliberately: it can resolve overlapping formats, but it can also prevent another rule from contributing a format you expected. If a color does not appear, inspect the rule order, the format chosen for the rule, and its Applies to range before rebuilding it.

When applying a formula to a block such as A2:F100, write the formula relative to the top-left row of that block. A rule intended for row 2 should refer to row 2. A reference that starts one row too high or low can make each record display the status of a neighboring record.

Common problems and practical checks

If a rule appears to miss a value, first check whether the text actually matches the rule. Exact comparisons can be affected by leading or trailing spaces, while a contains test may match a fragment inside a larger word. Copying a value into a test cell or checking its spelling can reveal differences that are hard to see on the worksheet.

  • Nothing changes: Confirm that the selected range is included in Applies to, that the formula refers to the correct first row, and that the chosen format is visually noticeable.
  • Wrong rows are highlighted: Check the dollar signs and starting row in the formula. For a row-based rule, lock the test column when needed, but normally leave the row relative.
  • Unexpected cells match: A contains test may find a short sequence inside a longer word. Use exact equality when the whole cell must match.
  • Only one color appears: Review overlapping rules and their priority in Manage Rules. A higher-priority rule or Stop If True setting may affect other formats.
  • Copied data behaves differently: Imported text may contain inconsistent spaces, punctuation, or spelling. Standardizing the source values can be more reliable than adding increasingly complex rules.

Conditional formatting does not remove duplicate values, repair spelling, or convert text into another data type. It only changes presentation when the condition is met. If values need to be corrected or standardized, fix the data itself or use an appropriate worksheet formula or data-cleaning step separately.

Excel for Mac and Excel for the web

The instructions above are for Excel for Microsoft 365 on Windows. Excel for Mac also offers conditional formatting from the Home tab, including text rules and formula-based rules, though the wording or layout of dialogs can vary slightly by release. The underlying formula examples use standard Excel syntax. On Mac, use the Mac version of the ribbon and dialog rather than assuming every Windows shortcut applies.

Excel for the web supports common conditional-formatting tasks, but the available controls and editing experience may differ from the desktop application. If a workbook has a complicated collection of rules, formula logic, or overlapping formats, open it in the desktop version to inspect and manage the rules more comfortably. Avoid assuming that a visual difference means the cell value itself changed.

Frequently Asked Questions

Can Excel highlight a cell if it contains a word?
Yes. Select the range and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, then enter the word. Use a SEARCH-based formula when you need more control over the condition.

Can I highlight an entire row based on text in one column?
Yes. Select the rows or table area to format, create a formula-based rule, and reference the status cell in the first selected row. Lock the status column with a dollar sign when the rule spans multiple columns.

Does Text that Contains match uppercase and lowercase differently?
No, the built-in text match is not case-sensitive. For a case-sensitive formula test, use FIND; SEARCH is also case-insensitive.

Can conditional formatting match an exact word only?
Yes. Use a formula equality test, such as =$C2="Late" when the status is in column C and the range starts on row 2. A contains rule can match Late inside a longer entry, so it is not the best choice for exact status values.

Why does a formula rule highlight the wrong row?
The formula may refer to the wrong starting row, or its row reference may be locked when it should change. Confirm that the formula corresponds to the first row in Applies to, then check whether the column—not the row—needs to be absolute.

Does changing the format change the text in the cell?
No. Conditional formatting changes the cell’s appearance only. It does not edit the text, remove spaces, standardize spelling, or alter the stored value.

Related

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.