Formatting Issues

Fix Excel Conditional Formatting Across a Full Range

Ayan MasoodAyan Masood Nov 18, 2026 869 views

Question details

Apply a conditional formatting rule consistently across four columns using values from the same row.

Fix Excel Conditional Formatting Across a Full Range
Product
Microsoft Excel
Device & OS
not provided
Scenario
A formula-based rule compares values in two columns and applies formatting across a four-column range.
Observed behavior
The rule works in some columns but not the final column because relative references shift across the applied range.
Before you start

Identify the first row of the range to format and the two columns you want to compare. The example below assumes the range starts on row 5 and compares columns CU and BK.

Solution 1Recommended

Lock the comparison columns in the Excel rule

Use mixed references to keep the comparison columns fixed while allowing the row number to change.

1
Open the rule manager

Select a cell in the formatted range, then go to Home > Conditional Formatting > Manage Rules. Choose the current worksheet from the rule display list if needed.

2
Edit the formula

Select the affected rule and click Edit Rule. In the formula box, enter =$CU5<>$BK5 if row 5 is the first row of the applied range. Replace the column letters and row number to match your worksheet.

3
Check the applied range

In Manage Rules, check Applies to and include all four columns and the intended rows, such as =$CU$5:$CX$100. Adjust this example to match your actual range.

4
Apply and test

Click Apply or OK, then check a row where the values in the two comparison columns differ. All cells in that row within the applied range should receive the rule's formatting.

Lock the comparison columns in the Excel rule
How the references work: The dollar signs fix columns CU and BK. The row number remains relative, so the rule compares CU6 with BK6 on the next row.
Format spreadsheets with WPS

Apply the same conditional formatting rule in WPS Spreadsheet

Use a formula-based conditional formatting rule in WPS Spreadsheet to compare fixed columns and format a full range.

  1. 1. Open the spreadsheet: Open WPS Spreadsheet and use File > Open to select your workbook.
  2. 2. Select the range: Select all cells you want the rule to format, such as CU5:CX100.
  3. 3. Create the rule: Go to Home > Conditional Formatting > New Rule and choose the option to use a formula. Enter =$CU5<>$BK5, adjusting the references for your sheet.
  4. 4. Choose formatting: Click Format, choose the appearance for matching cells, and confirm the rule with OK.
Apply one comparison rule across multiple columnsUse mixed references to keep comparison columns fixedOpen and save Microsoft Excel XLSX and XLS filesWork in a familiar, easy-to-use spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula work in one column but not another?

Excel adjusts relative column references across the applied range. Edit the rule under Home > Conditional Formatting > Manage Rules and add dollar signs before the column letters that must stay fixed.

What does =$CU5<>$BK5 compare?

It checks whether the values in columns CU and BK differ on the current row. Both columns stay fixed, while the row number changes as the rule is applied down the range.

How do I format each cell based on its own value instead?

For a range starting at CU5, use =CU5<>$BK5 if each cell should be compared with column BK on the same row. Leave CU relative so it shifts across the range, and lock BK to keep the comparison column fixed.

Where can I check whether the last column is included?

In Excel, open Home > Conditional Formatting > Manage Rules and inspect the rule's Applies to field. Extend the range to include the final column if it is missing.