Fix Excel Conditional Formatting Across a Full Range
Question details
Apply a conditional formatting rule consistently across four columns using values from the same row.

- 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.
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.
Lock the comparison columns in the Excel rule
Use mixed references to keep the comparison columns fixed while allowing the row number to change.
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.
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.
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.
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.

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. Open the spreadsheet: Open WPS Spreadsheet and use File > Open to select your workbook.
- 2. Select the range: Select all cells you want the rule to format, such as CU5:CX100.
- 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. Choose formatting: Click Format, choose the appearance for matching cells, and confirm the rule with OK.

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.




