Formatting Issues

How to Highlight a Row in Excel When a Cell Contains Specific Text

Tauseeq Magsi Oct 1, 2026 868 views

Question details

The user needs to automatically highlight a specific range of cells across a row (e.g., A1 through D1) with a background color when a specific cell in that row (e.g., B1) contains a target word.

How to Highlight a Row in Excel When a Cell Contains Specific Text

Product: Excel
Device & OS: not provided
Scenario: Formatting a spreadsheet dataset to visually emphasize entire rows based on the text value of a designated column.
Observed behavior: When the target word "exam" is entered into cell B1, cells A1 through D1 should automatically fill with a yellow background.

Before you start

Before applying conditional formatting, ensure you know the exact range of data you want to highlight and verify that your target column does not contain misspelled variations of your keyword.

Solution 1Recommended

Use Formula-Based Conditional Formatting

Create a custom conditional formatting rule using a formula with an absolute column reference to evaluate a specific cell and apply the format across the entire row.

To highlight an entire row based on the value of a single cell, you must use a formula in the Conditional Formatting tool. The trick is to use an absolute column reference (like $B1). The dollar sign locks the condition to column B, so when Excel evaluates cells in columns A, C, or D, it still checks column B for the criteria. The row number remains relative (no dollar sign) so the rule can adapt to each subsequent row in your selected range.

Select the Target Range

Highlight the range of cells you want to format. For example, select A1:D1 for a single row, or A1:D100 to apply it to your entire dataset.

Open Conditional Formatting

Navigate to the Home tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule' from the drop-down menu.

Choose the Formula Option

In the New Formatting Rule dialog box, select the option that says 'Use a formula to determine which cells to format'.

Enter the Formula

In the format values field, enter the formula: =$B1="exam" (Ensure the row number in the formula matches the first row of your selected range).

Set the Highlight Color

Click the 'Format' button, go to the 'Fill' tab, choose the color yellow, and click 'OK' twice to apply the rule.

Use Formula-Based Conditional Formatting

Case Sensitivity: The standard equals (=) formula is not case-sensitive. It will highlight the row whether cell B1 contains 'exam', 'EXAM', or 'Exam'.

Powerful Spreadsheet Tool

Use WPS Spreadsheet for Advanced Conditional Formatting

WPS Spreadsheet provides a highly compatible and intuitive interface for applying complex conditional formatting rules, just like Microsoft Excel. You can easily highlight rows based on cell values to organize your data efficiently.

1. Select Your Data Range: Open your document in WPS Spreadsheet and select the range of cells you wish to apply the highlighting to (e.g., A1:D100).
2. Access Conditional Formatting: Go to the 'Home' tab on the top ribbon, click on 'Conditional Formatting', and select 'New Rule' from the menu.
3. Apply a Custom Formula: Select 'Use a formula to determine which cells to format'. Enter your formula, such as =$B1="exam".
4. Customize the Formatting: Click 'Format', navigate to the 'Patterns' or 'Fill' tab to pick your desired highlight color, and click 'OK' to save.

100% compatible with Microsoft Excel (.xlsx, .xls) formatsIntuitive conditional formatting rule manager for complex data setsLightweight software that runs smoothly on Windows, Mac, and LinuxFree to use for everyday data analysis and formatting tasks

microsoft office alternative - wps office

Frequently Asked Questions

How do I highlight a row if the cell contains the word alongside other text?

If you want to highlight the row when the cell contains a partial match (e.g., 'final exam'), use the SEARCH function in your conditional formatting formula instead. Enter the formula: =ISNUMBER(SEARCH("exam",$B1)). This will find the word anywhere within the cell's text.

Why is my conditional formatting highlighting the wrong rows?

This commonly occurs if the row number referenced in your formula does not match the topmost row of your selected range. If your selection starts at row 2 (e.g., A2:D100), your formula must also start at row 2, like =$B2="exam".

Can I highlight rows based on multiple conditions?

Yes, you can combine conditions using AND or OR functions. For example, to highlight a row if B1 is 'exam' and C1 is 'passed', you would use the formula: =AND($B1="exam", $C1="passed").

How do I clear or delete a conditional formatting rule?

To remove a rule, highlight your data range, go to the Home tab, click Conditional Formatting, and select 'Clear Rules'. You can choose to clear rules from the selected cells or from the entire sheet.

Document & File Issues