Formatting Issues

How to Highlight an Entire Excel Row Based on a Cell Value

Nimra Malik Oct 1, 2026 868 views

Question details

The user wants to automatically highlight entire rows in a spreadsheet when a specific cell in that row matches a designated value.

How to Highlight an Entire Excel Row Based on a Cell Value

Product: Excel
Device & OS: not provided
Scenario: Formatting large datasets where rows need visual emphasis based on a status, indicator column, or specific condition.
Observed behavior: To format the whole row rather than just a single cell, the user must apply a conditional formatting rule using a mixed cell reference (locking the column while allowing the row to adjust).

Before you start

Identify the specific column that contains the indicator values (e.g., Column C) and take note of the first row of your actual data (e.g., Row 2) to ensure your formula references the correct starting point.

Solution 1Recommended

Use Conditional Formatting with a Mixed Reference Formula

By creating a formula-based conditional formatting rule and using a dollar sign ($) to lock the column reference, Excel will apply the format across the entire row.

To highlight an entire row instead of a single cell, the formatting rule needs to evaluate the indicator column consistently across every cell in that row. Using a mixed reference, such as $C2, locks the evaluation to Column C but allows the row number to adjust as the rule applies down the spreadsheet.

Select the Data Range

Highlight all the cells and columns that you want to be formatted. Make sure to keep the first row of your data (for example, row 2) as the active row in your selection.

Open Conditional Formatting

Navigate to the Home tab on the ribbon, click on 'Conditional Formatting' in the Styles group, and select 'New Rule' from the dropdown menu.

Choose Formula Option

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

Enter the Mixed Reference Formula

In the formula box, type your condition. If your indicator value is in column C and your data starts in row 2, type: =$C2=1 (or your desired condition). The dollar sign ($) fixes the column while the row changes dynamically.

Set Formatting and Apply

Click the 'Format' button, choose your desired fill color or text styling, click 'OK' to close the format dialog, and then click 'OK' again to apply the rule.

Use Conditional Formatting with a Mixed Reference Formula

Formula Variations: You can customize the formula based on your indicator column. For example, if your values are in Column D, use =$D2=1. You can also use functions like =IF($B2<200,1,0) to trigger the highlight.

Efficient Data Formatting

Easily Highlight Rows with WPS Spreadsheet

WPS Spreadsheet provides robust support for formula-based conditional formatting, allowing you to visually organize your data by highlighting entire rows based on specific cell values just as you would in Excel.

1. Select Your Data Range: Open your worksheet in WPS Spreadsheet and highlight the entire data range you wish to format, starting from your first data row.
2. Access Conditional Formatting: Go to the Home tab, click on 'Conditional Formatting', and select 'New Rule'.
3. Apply Formula Rule: Choose 'Use a formula to determine which cells to format', enter your mixed reference formula (e.g., =$C2=1), and select your desired Fill color.
4. Confirm and Save: Click 'OK' to instantly apply the formatting across the designated rows.

100% compatible with Microsoft Excel conditional formatting rules and syntaxIntuitive interface for managing complex formatting logicSeamlessly handles large datasets with dynamic cell referencesFree and lightweight alternative for all your spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is only the first cell in my row highlighting instead of the entire row?

This happens when the column reference in your formula is not locked. Ensure you place a dollar sign before the column letter (e.g., =$C2 instead of =C2). This forces Excel to always look at Column C when deciding whether to highlight any cell in that row.

Can I highlight a row based on a text value instead of a number?

Yes. If your indicator column contains text, enclose the target text in double quotation marks within your formula. For example, if you want to highlight rows where Column C says 'Complete', use the formula: =$C2="Complete".

How do I edit or delete the conditional formatting rule later?

Go to Home > Conditional Formatting > Manage Rules. In the dropdown at the top, select 'This Worksheet' to see all rules. Select your rule from the list and click 'Edit Rule' to change the formula or formatting, or 'Delete Rule' to remove it entirely.

Will this formula work if my data is formatted as an official Excel Table?

Yes, but table structure references (like [@ColumnName]) do not work for row highlighting in conditional formatting. You must still use standard mixed references, such as =$C2=1, even if the data is inside an Excel Table.

Document & File Issues