Formula Errors

How to Fix Excel Conditional Formatting Row Reference Errors

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to correct an Excel conditional formatting rule that fails to highlight differing values between two columns.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Comparing two columns using conditional formatting to highlight differences.
Observed behavior: The conditional formatting fails to highlight the correct cells because the row references in the formula do not match the starting row of the 'Applies to' range.

Before you start

Verify the exact starting row of your target data range and ensure your columns do not contain hidden leading or trailing spaces that might affect the comparison.

Solution 1Recommended

Align Formula Row References with the 'Applies to' Range

Ensure the row number in your custom conditional formatting formula perfectly matches the first row of your selected range.

When using a custom formula for conditional formatting, Excel applies the formula relatively across the selected range. If the formula references row 2 but the 'Applies to' range starts at row 2090, Excel calculates the offset incorrectly, leading to the wrong cells being highlighted.

Open the Conditional Formatting Rules Manager

Navigate to the Home tab on the Excel ribbon, click on Conditional Formatting, and select Manage Rules.

Check the 'Applies to' Range

Locate your rule and look at the 'Applies to' column. Note the exact starting row number of this range (for example, if the range is L2090:L423129, the starting row is 2090).

Edit the Formatting Rule

Select the rule and click Edit Rule. In the formula box, update the row numbers to match the starting row of your 'Applies to' range.

Apply the Correct Formula

For a case-insensitive comparison starting on row 2, use =$A2<>$B2. For a case-sensitive comparison on the same row, use =NOT(EXACT($A2,$B2)). Click OK and then Apply to save your changes.

Relative vs. Absolute References: Ensure you lock the column with a dollar sign (e.g., $A2) so that the formula only checks the specific columns you intend to compare, while leaving the row relative.

Efficient Data Formatting

Compare Columns Easily with WPS Spreadsheet

WPS Spreadsheet offers an intuitive Conditional Formatting tool that makes comparing data across columns straightforward, minimizing formula offset errors.

1. Select the Target Range: Open your dataset in WPS Spreadsheet and highlight the column where you want the conditional formatting to appear.
2. Open Conditional Formatting: Go to the Home tab, click on Conditional Formatting, and select New Rule.
3. Enter the Comparison Formula: Choose 'Use a formula to determine which cells to format'. Input your formula (e.g., =$A2<>$B2) ensuring the row number exactly matches your selection's starting row.
4. Set the Format and Apply: Click Format to pick a highlight color or text style, click OK, and the formatting will instantly apply to mismatched values.

Fully compatible with Microsoft Excel conditional formatting rulesIntuitive Rules Manager for easy troubleshooting and editingLightweight application that processes large datasets smoothlyFree to use for everyday spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Excel conditional formatting highlighting the wrong rows?

This usually happens when the row referenced in your custom formula does not match the first row of the range specified in the 'Applies to' field. Excel offsets the formula based on this difference, causing incorrect rows to be highlighted.

How do I make conditional formatting case-sensitive in Excel?

By default, standard logical operators (like =A1<>B1) are not case-sensitive. To enforce case sensitivity, use the EXACT function wrapped in a NOT function, such as =NOT(EXACT($A2,$B2)).

Do I need to use absolute references in conditional formatting formulas?

It depends on what you are comparing. When comparing two specific columns, you should lock the columns using a dollar sign (e.g., $A2), but leave the row relative (no dollar sign before the number) so it can evaluate each row individually down the applied range.

Excel & Data Problems