Excel Conditional Formatting Formula If Text

Formulas & functions · Updated November 22, 2026

The core formula for conditional formatting based on text in Excel is =ISNUMBER(SEARCH("text", A1)) for partial matches, or =A1="exact text" for exact matches, applied via Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. This article explains how to write these formulas, where to place them, and how to handle common text-matching scenarios.

Why Use a Formula Instead of Built-In Text Rules?

Excel’s built-in conditional formatting for text (Home > Conditional Formatting > Highlight Cells Rules > Text that Contains) only handles simple “contains” logic. A formula gives you full control: exact matches, case-sensitive matches, matches that ignore extra spaces, or formatting based on text in another cell. For example, you might want to highlight an entire row when a cell in column B says “Overdue.” Built-in rules can’t do that; a formula can.

Formulas also let you combine conditions with AND/OR, check for multiple text values, or use wildcards in ways the basic dialog doesn’t support. The trade-off is that you must understand relative references (no dollar signs) because the formula applies to the top-left cell of the selected range and then shifts.

How to Write the Formula Rule (Step by Step)

  1. Select the range you want to format (e.g., A2:A100). The active cell (usually A2) is the reference point.
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose “Use a formula to determine which cells to format.”
  4. In the formula box, enter your formula. It must start with = and return TRUE to apply formatting.
  5. Click Format, set your fill/font, then OK twice.

Example: To highlight cells in A2:A100 that contain the word “urgent,” select A2:A100, create a new rule, and enter =ISNUMBER(SEARCH("urgent", A2)). The formula references A2 (the active cell), not the whole range. Excel applies it relative to each row.

Exact Match vs. Partial Match: Which Formula to Use

For an exact match (cell equals “Paid” and nothing else), use =A2="Paid". This is case-insensitive by default, so “paid” and “PAID” both match. If you need case-sensitive exact matching, use =EXACT(A2,"Paid").

For a partial match (cell contains “Paid” anywhere), use =ISNUMBER(SEARCH("Paid", A2)). SEARCH is case-insensitive and allows wildcards like * and ?. For case-sensitive partial match, replace SEARCH with FIND: =ISNUMBER(FIND("Paid", A2)).

Important: SEARCH and FIND return a number (position) or a #VALUE! error. ISNUMBER converts that into TRUE/FALSE. Without ISNUMBER, the formula would error and not format.

Conditional Formatting Based on Text in Another Cell

Often you want to format a cell based on text in a different column. For example, highlight column A if column B says “Overdue.” Select A2:A100, then use the formula =$B2="Overdue". The dollar sign fixes the column B reference so that when the rule moves across columns, it always looks at B. The row number (2) is relative, so it checks each row’s B cell.

To highlight an entire row, select A2:Z100 and use =$B2="Overdue". The mixed reference (absolute column, relative row) is the key. If you used =B2="Overdue" without the dollar sign, the rule would check the cell directly to the left of each cell, which is usually wrong.

Common Mistakes and How to Fix Them

  • Formula references the wrong cell. The formula must reference the active cell (usually the first cell in the selection). If you select A2:A100 and type =A1="x", it checks the row above, causing misalignment.
  • Missing dollar signs. When formatting a range that spans multiple columns, use $B2 to lock the column. Forgetting the dollar sign makes the rule check different columns for each cell.
  • Using quotes incorrectly. Text must be in straight double quotes: "text". Curly quotes or single quotes will break the formula.
  • Case sensitivity surprises. SEARCH and = are case-insensitive. If you need case-sensitive, use FIND or EXACT.
  • Leading/trailing spaces. “Paid ” (with a space) won’t match “Paid”. Use =TRIM(A2)="Paid" to ignore extra spaces.

Advanced Text Formulas: Wildcards, Multiple Conditions, and Blanks

You can combine text checks with AND/OR. For example, to highlight cells that contain “red” or “blue”: =OR(ISNUMBER(SEARCH("red", A2)), ISNUMBER(SEARCH("blue", A2))). To check that a cell starts with “INV”: =LEFT(A2,3)="INV" or use =COUNTIF(A2,"INV*") (COUNTIF supports wildcards). To highlight blank cells: =A2=""; for non-blank: =A2<>"".

For text that appears in a list, use COUNTIF: =COUNTIF($D$2:$D$10, A2)>0 highlights A2 if its value is found in D2:D10. This is a powerful pattern for matching against a dynamic list.

Frequently Asked Questions

Why is my conditional formatting formula not working?

Most likely the formula references the wrong cell or lacks the ISNUMBER wrapper. Check that the formula returns TRUE when you test it in a worksheet cell. Also ensure the range selection starts at the active cell; if you select A2:A100, the formula must reference A2, not A1 or A100.

How do I highlight a row based on text in a cell?

Select the entire row range (e.g., A2:Z100), then use a formula with a mixed reference like =$B2="Overdue". The dollar sign before B locks the column, while the row number changes relative to each row. This highlights the whole row when column B contains “Overdue”.

Can I use wildcards in conditional formatting formulas?

Yes, with COUNTIF or SEARCH. For example, =COUNTIF(A2,"*urgent*")>0 works for partial matches. SEARCH also supports * and ? as wildcards, but it’s less common. For exact wildcard patterns, COUNTIF is clearer.

How do I make conditional formatting case-sensitive for text?

Use FIND instead of SEARCH for partial matches: =ISNUMBER(FIND("Paid", A2)). For exact matches, use =EXACT(A2,"Paid"). These are the only built-in ways to enforce case sensitivity.

Does conditional formatting work with text in another sheet?

Yes, but you must reference the other sheet directly, e.g., =Sheet2!B2="Overdue". The sheet name with a space needs single quotes: ='My Sheet'!B2. Note that the rule will apply relative to the active cell, so ensure the reference is correct.

Why does my formula work in a cell but not in conditional formatting?

Conditional formatting formulas ignore absolute references differently. In a cell, =A2="x" works. In conditional formatting, if you select A2:A100 and enter =A2="x", it works. But if you enter =A1="x", it checks the row above. Also, conditional formatting cannot use array formulas or functions that return arrays (like FILTER) in older Excel versions; stick to simple scalar functions.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.