Excel Formula to Highlight a Row If a Cell Contains

Formulas & functions · Updated November 18, 2026

With the search term in E2, the value to check in column A, and rows 2–100 to format across A:D, use =AND($E$2<>"",ISNUMBER(SEARCH($E$2,$A2))) as a Conditional Formatting formula. It highlights each entire row when its column A cell contains the search text, without highlighting every row when E2 is blank.

What the formula does

Excel’s Conditional Formatting feature can apply a fill, font color, or other format to a range when a formula evaluates to TRUE. In this example, the formula checks column A on each row and applies the result to the corresponding cells in A:D. If A2 contains the text entered in E2, the rule highlights A2:D2; if A3 does not contain it, row 3 is left alone.

SEARCH looks for one text string inside another and returns the position where it begins. If it finds the search text, ISNUMBER turns that position into TRUE. If it does not find the text, SEARCH returns an error and ISNUMBER returns FALSE. The AND check also requires E2 to be nonblank, which prevents an empty search box from matching every cell.

SEARCH is not case-sensitive: searching for “north” also matches “North” and “NORTH.” That is usually useful for names, notes, and status fields. The dollar signs keep the search term fixed at E2 and keep the checked column fixed at A, while the row number changes as Excel evaluates each row.

Set up the conditional formatting rule

For the stated layout—search term in E2, checked values in A2:A100, and the rows to highlight in A2:D100—create the rule as follows:

  1. Select A2:D100. Start the selection at row 2, because the formula’s row reference is based on that first row.
  2. On the Windows ribbon, choose Home > Conditional Formatting > New Rule.
  3. In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
  4. Enter =AND($E$2<>"",ISNUMBER(SEARCH($E$2,$A2))) in the formula box.
  5. Choose Format, select the fill or other formatting you want, and confirm the dialogs with OK.

Because A2:D100 is selected when the rule is created, Excel applies the formula to that range. To review or adjust it later, use Home > Conditional Formatting > Manage Rules. Check the “Applies to” range as well as the formula; a correct formula applied to the wrong cells can appear not to work.

In Excel for Mac, select the range and use Home > Conditional Formatting > New Rule; the dialog’s labels or layout can differ slightly by release. Excel for the web also supports formula-based conditional formatting, though its controls may be arranged differently. The core principle is the same: select the rows to format, enter the rule, and choose the formatting.

Understand the cell references

The formula uses $E$2 for the search term because it should remain the same while Excel checks every row. Both the column and row are locked. It uses $A2 for the checked cell: the dollar sign locks the column to A, but the row number is allowed to change. Consequently, the rule checks A2 for the first row, A3 for the next, and so on.

The selected range and the formula must agree. If you select A2:D100, the formula should refer to row 2. If instead your data begins in row 5 and you select A5:D100, use a formula whose checked-cell reference begins with row 5. Excel shifts relative references to match each row in the “Applies to” range. A mismatch can cause the highlight to be offset by one or more rows.

To check a different column, change the column reference in the formula. For example, if the text to search is in column C but you still want to highlight A:D, use a checked-cell reference to C on the first row. Keep the search-term reference fixed, and make the checked cell’s row relative.

Choose the right kind of match

For the same layout—search term in E2, values in A2:A100, and formatting across A2:D100—the main formula searches for a substring anywhere in the cell. If E2 contains “late,” it can match “Late shipment,” “Shipment late,” or “delayed” only if the exact letters “late” occur together in that order. It does not require the entire cell to equal the search term.

Use FIND instead of SEARCH when capitalization matters. With the same layout and selected range, this formula highlights matches that have the same letter case as E2:

=AND($E$2<>"",ISNUMBER(FIND($E$2,$A2)))

For an exact cell match rather than “contains,” use this formula with the same layout:

=AND($E$2<>"",$A2=$E$2)

That exact-match rule does not highlight a cell such as “Late shipment” when E2 is “Late”; the whole cell must equal the search term. Decide whether the task calls for a partial match or equality before choosing the formula.

Search another column or highlight fewer cells

The formula determines whether a row qualifies, while the “Applies to” range determines which cells receive formatting. To highlight the entire record, select all the relevant columns in each data row. To highlight only the checked cells, apply the rule just to A2:A100. To highlight only columns B:D when column A contains the match, apply it to B2:D100 while keeping the formula’s checked-cell reference on A.

For a different checked column, the same layout principle applies. If records occupy A2:D100, the search term is in E2, and the values to inspect are in C2:C100, select A2:D100 and base the formula on C2. The dollar sign before C keeps the check in that column while the row changes. Do not put dollar signs before the row number of the checked cell unless you intentionally want every formatted row to depend on one fixed record.

You can also put the search term in a different cell. Replace E2 with the cell you use, locking its reference with dollar signs. Keeping the term in a separate input cell makes the rule reusable: change the text in that cell and Excel recalculates which rows are highlighted.

Common problems and practical fixes

  • Every row highlights when the search box is blank: include the nonblank check, $E$2<>"", as in the main formula. Without it, an empty search string can be treated as a match.
  • The wrong rows highlight: align the formula’s starting row with the top row of the selected range. For A2:D100, use a row-2 reference such as $A2.
  • Only one cell changes color: expand the rule’s “Applies to” range to cover all columns in each record, such as A2:D100.
  • The rule checks the wrong field: make sure the formula’s checked-column reference points to the column that contains the text, not merely the first column in the range being formatted.
  • Unexpected partial matches appear: SEARCH looks for a substring. “art,” for example, can match “Carton.” Use an exact-match formula if the full cell must equal the search term.
  • Wildcards behave unexpectedly: SEARCH recognizes wildcard characters such as an asterisk and question mark. If the search term includes one and you intend it as a literal character, escape it with a tilde in the search text, or use a method appropriate to the specific text you need to find.

Also check whether another Conditional Formatting rule is affecting the same cells. In Manage Rules, inspect the order, ranges, and formatting of overlapping rules. A rule can be logically correct but its color may be obscured by another rule or be difficult to see against the worksheet’s existing formatting.

Formula rule versus built-in text rules

Excel has built-in Conditional Formatting options for highlighting cells that contain particular text. Those are convenient when you only need to format the cells that contain the text. A formula rule is more flexible when the requirement is to color the entire row based on one cell, to use a search term stored in another cell, or to make the match case-sensitive.

For a simple cell-only task, select the target cells and use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. For whole-row highlighting, choose New Rule and use a formula, because the rule needs to test one column while formatting a wider range. Both approaches apply formatting rather than changing cell contents; they do not insert text, move rows, or filter the list.

Frequently Asked Questions

Does the formula highlight a row if the text is anywhere in the cell?
Yes. SEARCH looks for the entered text anywhere inside the checked cell. It is a partial, case-insensitive match, not a requirement that the whole cell equal the search term.

How do I highlight a row if a cell contains one specific word?
Enter the word in the search cell and use the SEARCH-based rule. Be aware that it looks for the character sequence, so it may also match that sequence inside a longer word. For word-boundary matching, a more tailored formula or data-cleaning approach may be needed.

Can I use this to highlight rows based on column B?
Yes. Keep the same “Applies to” range for the full rows, but change the formula’s checked reference to column B and the first row of the range, such as $B2 when the selection begins at row 2.

Why does the formula start with an equals sign?
Conditional Formatting’s formula rule expects an Excel formula, which begins with =. Excel evaluates it separately for the cells in the selected range and formats cells where the result is TRUE.

Will the rule update when I change the search term?
Yes. When the formula points to a cell such as E2, changing E2 causes Excel to recalculate the rule. The formatting updates to reflect which checked cells contain the new text.

Does SEARCH distinguish uppercase and lowercase?
No. SEARCH is not case-sensitive. Use FIND in the same formula structure when case should matter; FIND returns a match only when the letter case agrees.

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.