Make Relative References Update in WPS Spreadsheets Conditional Formatting Rules
Question details
The user needs conditional formatting formulas in WPS Spreadsheets to use relative row references so each row in the Applies to range is evaluated against the correct cells.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is creating or editing a conditional formatting rule that must check values in specific columns and apply formatting row by row across a multi-row range.
- Observed behavior
- When the formula uses relative row references without a dollar sign before the row number, WPS Spreadsheets starts from the top-left cell of the Applies to range and automatically uses the matching row for every other row.
Open the workbook in WPS Spreadsheets and identify the exact range that should receive the formatting. Confirm the first row of the Applies to range because the conditional formatting formula must be written from that cell's perspective.
Use relative row references with fixed column references in a conditional formatting formula
Use this when you want the rule to update row numbers automatically while still checking the same columns for each row.
Conditional formatting formulas are evaluated from the top-left cell of the Applies to range. A dollar sign before a row number locks that row; removing it makes the row relative, so the next row in the range uses the next row number.
For example, if the rule starts in row 4 and must check columns AP and AF, write the formula from the perspective of row 4 and leave the row numbers relative.
In WPS Spreadsheets, select the cells that should receive the conditional formatting, such as AF4:AF100.
Go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and click the formula box.
Type a formula based on the top-left cell of the selected range, such as =AND($AP4="Exempt",$AF4<80). Keep the column letters fixed with $ and the row numbers relative without $.
In the Applies to field, verify that the range shows the intended rows, such as =$AF$4:$AF$100, then click OK.
Edit an existing conditional formatting rule to fix locked row references
Use this when a current rule is not updating rows correctly and you need to inspect or adjust the formula and Applies to range.
Create Relative Conditional Formatting Rules in WPS Spreadsheets
Build Excel-compatible conditional formatting rules in WPS Spreadsheets using relative row references and fixed column references, so your formatting updates correctly across the entire Applies to range.
- 1. Open your workbook: Launch WPS Spreadsheets and open the Excel workbook that contains the conditional formatting rule.
- 2. Select the formatting range: Select the cells that should be formatted, for example AF4:AF100.
- 3. Open the New Rule dialog: Go to Home > Conditional Formatting > New Rule and select Use a formula to determine which cells to format.
- 4. Enter relative references: In the formula box, enter =AND($AP4="Exempt",$AF4<80), keeping column references locked and row references relative.
- 5. Apply the rule: Verify the Applies to range is $AF$4:$AF$100 and click OK to save the rule.
Frequently Asked Questions
Why does my conditional formatting rule use the wrong row?
Conditional formatting formulas start from the top-left cell of the Applies to range. If row numbers are locked with $, they stay the same for every row. Remove $ before the row number to make it relative and update row by row.
Should column references be locked in conditional formatting?
Lock column references when the rule must always compare the same columns, such as $AP4 and $AF4. If the compared columns should also shift across the range, remove $ before the column letters as well.
Can I apply a formula-based conditional formatting rule to a whole column?
Yes. Set the Applies to range to the target rows or entire column area, and write the formula based on the first cell in that range. WPS Spreadsheets adjusts relative references for the remaining rows.
What does the formula =AND($AP4="Exempt",$AF4<80) do?
It applies formatting when column AP in the current row equals "Exempt" and column AF in the same row is less than 80. The columns are fixed, while the row number updates along the Applies to range.
How do I edit the Applies to range in WPS Spreadsheets?
Go to Home > Conditional Formatting > Manage Rules, select the rule, and click Edit Rule. You can then change the Applies to field and adjust the formula reference style.
