Formatting Issues

How to Highlight Entire Rows Based on Cell Value in Excel

Maira Mehtab Sep 22, 2026 872 views

Question details

The user needs to automatically format a spreadsheet so that entire rows turn gray when a specific column contains the word 'Closed', and specific cells within that column turn red when they contain 'Open'.

Product: Excel
Device & OS: not provided
Scenario: Tracking project or task statuses using a spreadsheet and needing visual cues to quickly identify completed or open items.
Observed behavior: The rows and cells need to dynamically update their background color to gray or red based on the text value entered into the designated status column.

Before you start

Ensure your data range is organized in a clear tabular format without merged cells, as merged cells can sometimes disrupt how conditional formatting rules are applied across rows.

Solution 1Recommended

Apply Conditional Formatting with Custom Formulas

Use custom formula rules in the Conditional Formatting menu to apply different colors to your rows and cells based on specific text conditions.

To highlight an entire row based on a single cell's value, you must use a mixed reference in your formula (locking the column with a dollar sign). To highlight only the specific cell, use a relative reference.

Select the Entire Data Range

Click and drag to select your entire data range (e.g., A2:Z300). Make sure to start from the top-left cell of your data, excluding the header row.

Create the Rule for Gray Rows

Navigate to the Home tab on the ribbon, click 'Conditional Formatting', and select 'New Rule'. Choose 'Use a formula to determine which cells to format'.

Enter the Formula for 'Closed'

In the formula bar, enter =$Q2="Closed". The dollar sign before the Q locks the column reference, ensuring the entire row is evaluated based on column Q. Click 'Format', choose a gray fill color, and click OK.

Create the Rule for Red Cells

Next, select only the cells in Column Q (e.g., Q2:Q300). Go back to 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine which cells to format'.

Enter the Formula for 'Open'

Enter the formula =Q2="Open". Click 'Format', choose a red fill color, and click OK. Your spreadsheet will now dynamically update colors based on the status.

Absolute vs. Relative References: Using $Q2 (mixed reference) highlights the entire row because the column is locked. Using Q2 (relative reference) only highlights the specific cell where the condition is met.

Efficient Data Formatting

Easily Highlight Rows and Cells with WPS Spreadsheet

WPS Office provides an intuitive Conditional Formatting tool that allows you to dynamically style your data based on cell values. It handles complex rules effortlessly and is highly compatible with Microsoft Excel formulas.

1. Open Data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you want to format.
2. Highlight the Target Range: Select the data area you want to format, such as A2 to Z300.
3. Access Conditional Formatting: Go to the Home tab, click on 'Conditional Formatting', and choose 'New Rule'.
4. Apply Custom Formula: Select the formula option, input =$Q2="Closed", set your desired gray background color, and click OK to apply.

Free and lightweight Office suiteFully compatible with Microsoft Excel conditional formatting and custom formulasUser-friendly interface for managing multiple formatting rulesAvailable across Windows, Mac, Linux, iOS, and Android

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows?

This usually happens if the row number in your formula does not match the first row of your selected range. For example, if your selection starts at A2, your formula must reference row 2 (e.g., =$Q2="Closed"). If you reference row 1 instead, the highlighting will be shifted by one row.

How do I remove or edit conditional formatting rules?

To edit or remove rules, select the affected cells, go to the Home tab, click 'Conditional Formatting', and choose 'Manage Rules'. From there, you can select the specific rule to edit its formula or formatting, or click 'Delete Rule' to remove it.

Can I apply multiple conditional formatting rules to the same cells?

Yes, you can apply multiple rules to the same range. If rules conflict, the one positioned higher in the 'Manage Rules' dialog box will take priority. You can adjust the priority order using the up and down arrows in the manager.

Why do I need a dollar sign ($) in the formula for highlighting whole rows?

The dollar sign creates an absolute column reference. It locks the condition to evaluate only that specific column (e.g., Column Q) while allowing the formatting to be applied across all columns in that row. Without the dollar sign, Excel would evaluate each column's cell individually against the condition.

Document & File Issues