keepcalculator

06 Numbers and Powers

Highlight Rows That Contain: Excel & Google Sheets Guide

Card No. 06·51966 · Updated · Standard formula, shown below

This guide explains how to highlight rows that contain a specific word, number, or value in both Microsoft Excel and Google Sheets. You’ll learn the exact steps using conditional formatting with a formula, plus how to adapt the rule for partial matches, multiple criteria, and entire-row highlighting.

Why Locking the Column Matters

The $ before the column letter is the single most important detail. If you write =ISNUMBER(SEARCH("urgent", B2)) without the dollar sign, the rule will still work on the first row, but when applied to row 3, the reference shifts to C3, then D4, and so on. The result is that only the first row is checked correctly; all others compare against the wrong column. Always lock the column you’re testing, but leave the row number relative so each row is evaluated against its own cell.

Common Variations and Use Cases

You can extend the basic formula to handle many real-world scenarios:

  • Multiple words (OR logic): highlight rows that contain “red” OR “blue”: =OR(ISNUMBER(SEARCH("red",$A2)), ISNUMBER(SEARCH("blue",$A2)))
  • Multiple words (AND logic): highlight rows that contain both “red” AND “blue”: =AND(ISNUMBER(SEARCH("red",$A2)), ISNUMBER(SEARCH("blue",$A2)))
  • Check any cell in the row: use =SUMPRODUCT(--ISNUMBER(SEARCH("text",$A2:$D2)))>0 – this checks all columns in that row.
  • Highlight rows that start with “X”: =LEFT($A2,1)="X"
  • Highlight rows that end with “ed”: =RIGHT($A2,2)="ed"
  • Highlight rows where a number is greater than 100: =$B2>100

These formulas work identically in Excel and Google Sheets, though the menu paths differ slightly.

Step-by-Step in Excel

  1. Select the data range (e.g., A2:D100).
  2. Go to Home → Conditional Formatting → New Rule.
  3. Choose “Use a formula to determine which cells to format”.
  4. Enter your formula (e.g., =ISNUMBER(SEARCH("urgent",$B2))).
  5. Click Format, pick a fill color, and press OK twice.

To edit or delete the rule later, go to Conditional Formatting → Manage Rules.

Step-by-Step in Google Sheets

  1. Select the data range (e.g., A2:D100).
  2. Click Format → Conditional formatting.
  3. Under “Format cells if”, choose “Custom formula is”.
  4. Type your formula (e.g., =ISNUMBER(SEARCH("urgent",$B2))).
  5. Set the formatting style (fill color) and click Done.

Sheets also lets you add multiple rules for the same range, and the rules apply in order.

Common Mistakes and Troubleshooting

  • Forgetting the dollar sign: as explained, this breaks the formula for all rows except the first.
  • Applying the rule to the wrong range: if you select only column B, only column B will highlight, not the whole row. Select the full row range.
  • Using SEARCH vs FIND: SEARCH ignores case; FIND is case-sensitive. If you need exact case, use FIND.
  • Text with spaces or special characters: the formula treats them literally. If your text contains a quote, double it (e.g., "it''s").
  • Numbers stored as text: if your “numbers” are actually text (left-aligned), SEARCH still works, but exact match with = may fail. Use VALUE() or ensure consistent data types.

Alternatives to Conditional Formatting

If you only need to see which rows match, you can use a filter instead of highlighting. In Excel, select your data and press Ctrl+Shift+L, then use the search box in the column header. In Google Sheets, click the funnel icon in the toolbar. Filters are quicker for one-off checks but don’t persist visually like conditional formatting.

Another alternative is a helper column with a formula like =IF(ISNUMBER(SEARCH("urgent",B2)),"Yes",""), then filter on that column. This is useful if you need to copy or sort the matching rows.

Frequently Asked Questions

How do I highlight an entire row based on a cell in another column?

Use the formula with the column reference locked. For example, to highlight rows where column C has “paid”, select A2:D100 and use =ISNUMBER(SEARCH("paid",$C2)). The row highlights because the formula evaluates for each row independently.

Can I highlight rows that contain any of several words?

Yes, use the OR function. For “red” or “blue”: =OR(ISNUMBER(SEARCH("red",$A2)), ISNUMBER(SEARCH("blue",$A2))). Add more terms as needed.

Why is my formula not working in Google Sheets?

The most common cause is missing the dollar sign. Also, ensure you’ve selected “Custom formula is” and that the range you selected matches the row references in your formula. If your data starts at row 1, adjust the formula to $A1.

Does conditional formatting slow down large spreadsheets?

Yes, especially with many rules or whole-column references. Limit the range to your actual data (e.g., A2:D1000 instead of A:D). For very large datasets, consider using a filter or a pivot table instead.

How do I highlight rows where a cell is empty?

Use =ISBLANK($A2) for a specific column, or =COUNTBLANK($A2:$D2)>0 to check if any cell in the row is empty.

Can I use a cell reference as the search text?

Yes, instead of typing “urgent”, reference a cell like $F$1. Then you can change the word in F1 and the highlighting updates automatically. The formula becomes =ISNUMBER(SEARCH($F$1,$B2)).

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →