Fix Conditional Formatting with the Correct Relative Row in WPS Spreadsheets
Question details
The user needs to correct a conditional formatting rule in WPS Spreadsheets when the formula references a different row than the first row of the Applies to range, causing shifted or wrong-row formatting.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is editing a conditional formatting rule where the Applies to range begins in row 1 but the formula references row 2, so formatting appears shifted.
- Observed behavior
- The formatting appears shifted or applies to the wrong rows because the formula row does not match the first row of the Applies to range.
Open the workbook in WPS Spreadsheets, select the affected range, and note the first row and column shown in the Applies to field of the conditional formatting rule before editing it.
Edit the Conditional Formatting Formula to Match the First Row of the Applies to Range
Use this fix when an existing rule references row 2 or another row while the Applies to range starts in row 1, causing the formatting to appear shifted.
Conditional formatting formulas are evaluated relative to the first cell of the Applies to range. If the range begins in row 1 but the formula references row 2, WPS Spreadsheets compares each row with the next row's value, so the highlight can appear one row off or apply to the wrong names.
On the Home tab, click Conditional Formatting, then choose Manage Rules from the drop-down menu.
In the Conditional Formatting Rules Manager, select the rule that is formatting the wrong rows and click Edit Rule.
In the formula field, change the row number so it matches the first row of the Applies to range. For example, if the range starts in row 1 and the planner name is in column I, use =$I1="Peyton".
Click OK to close the rule editor, then click Apply or OK in the Rules Manager and check that the formatting now highlights the intended rows.
Recreate the Conditional Formatting Rule from the Correct First Row
Use this method if the existing rule has multiple edits or if you prefer a clean conditional formatting setup.
Use WPS Spreadsheets to Align Conditional Formatting Formulas with the Applies to Range
With WPS Spreadsheets, open Manage Rules and edit the conditional formatting formula so the row reference matches the first row of the Applies to range. This corrects shifted highlighting and keeps your workbook working like an Excel file.
- 1. Open the workbook: Open the affected workbook in WPS Spreadsheets and select the range with the conditional formatting issue.
- 2. Open Manage Rules: Go to Home > Conditional Formatting > Manage Rules.
- 3. Edit the rule formula: Select the rule, click Edit Rule, and change the formula to match the first row of the Applies to range, for example =$I1="Peyton".
- 4. Apply and confirm: Click OK, then Apply or OK in the Rules Manager, and verify that the correct rows are highlighted.
Frequently Asked Questions
Why does my conditional formatting highlight the wrong rows when the Applies to range starts in row 1?
This usually happens when the formula references a different row, such as row 2. WPS Spreadsheets evaluates the formula from the first row of the Applies to range, so the row reference must match that first row.
How do I open the Manage Rules dialog in WPS Spreadsheets?
Select the range, go to the Home tab, click Conditional Formatting, and choose Manage Rules from the drop-down menu.
Should I use =$I1 or =$I$1 for a row-based conditional formatting rule?
Use =$I1 when you want the column I reference to stay fixed while the row adjusts down the range. =$I$1 locks both column and row, so it only checks the value in I1 for every cell.
Can I fix the Applies to range instead of editing the formula?
Yes. In Manage Rules, edit the Applies to field so its first row matches the row number used in the formula, then click Apply and verify the formatting.
