Formatting Issues

How to Fix Excel Conditional Formatting References Across Multiple Rows

Huda Qurayshi Oct 21, 2026 868 views

Question details

User needs to apply conditional formatting to multiple rows in one column based on the corresponding values in another column.

How to Fix Excel Conditional Formatting References Across Multiple Rows

Product: Excel
Device & OS: not provided
Scenario: Applying a conditional formatting rule to column B when the corresponding cell in column C equals zero.
Observed behavior: The conditional formatting formula keeps referencing the exact same absolute cell for every row instead of dynamically adjusting to the current row.

Before you start

Ensure you know the exact range of cells you want to format (e.g., B2:B100) and identify the specific column containing your trigger condition.

Solution 1Recommended

Use Mixed References in the Conditional Formatting Formula

By adjusting absolute ($) and relative references, you can lock the criteria column while allowing the formula to evaluate each row dynamically.

When conditional formatting applies the exact same cell reference to an entire range, it is because absolute references (like $C$2) lock both the column and the row. To fix this, you must use a mixed reference.

A mixed reference like $C2 locks the column (C) but leaves the row (2) relative. As the formatting rule evaluates down the range, it automatically checks C3, C4, C5, and so on.

Select the target range

Highlight the range you want to format. For example, select cells B2 through B100.

Open Conditional Formatting

Go to the Home tab on the Excel ribbon, click Conditional Formatting, and select New Rule from the dropdown menu.

Select formula option

Choose the option labeled 'Use a formula to determine which cells to format'.

Enter the mixed reference formula

In the formula box, enter =$C2=0. Ensure there is a dollar sign before the C, but no dollar sign before the 2.

Apply formatting style

Click the Format button, choose your desired fill color or font style, and click OK to apply the rule.

Use Mixed References in the Conditional Formatting Formula

Dynamic Formatting Achieved: The formatting rule will now dynamically check the corresponding row in Column C for each cell in Column B.

Advanced Spreadsheet Formatting

Apply Conditional Formatting Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced formulas and conditional formatting features, making it incredibly easy to manage dynamic row highlighting for large datasets.

1. Select your data range: Open your workbook in WPS Spreadsheet and select the target range in your formatting column.
2. Access the Conditional Formatting tool: Navigate to the Home tab, click the Conditional Formatting icon, and select New Rule.
3. Input the dynamic formula: Choose 'Use a formula to format cells', type =$C2=0, and verify the correct placement of the dollar sign.
4. Set style and apply: Click Format to define your custom highlight colors, confirm your changes, and immediately view the dynamically updated rows.

100% compatible with Microsoft Excel conditional formatting and formulasUser-friendly rule manager for updating or clearing complex formattingFree, lightweight, and fast processing for data-heavy workbooks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel conditional formatting formula shift incorrectly?

This usually happens when you omit the absolute reference (the $ sign) where it is needed. If you apply a formula like =C2=0 to a wide range, both the row and column will shift. Using =$C2=0 locks the column so it doesn't shift sideways, while allowing the row number to adjust.

How can I edit an existing conditional formatting rule without starting over?

Go to Home > Conditional Formatting > Manage Rules. Select 'This Worksheet' from the top dropdown, find your specific rule, and click 'Edit Rule' to modify the formula or change the applied cell range in the 'Applies to' box.

Does complex conditional formatting slow down my spreadsheet?

Yes, applying volatile or overly broad conditional formatting rules across entire columns (such as B:B, which contains over a million rows) can significantly slow down your file. It is best practice to apply rules only to the specific data range you are actively working with.

Document & File Issues