WPS Spreadsheet: Copy Conditional Formatting Rules with Changing References
Question details
The user needs to copy a conditional formatting rule to other rows while automatically changing the referenced cell pair.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A conditional formatting rule references cells such as S15 and S16, and the copied rule should reference S17 and S18 in the next row.
- Observed behavior
- Copying the rule does not automatically produce the required sequential reference pairs.
Confirm the first formatted row and the starting reference pair, such as row 3 with source cells S15 and S16. Check that the target cells use the same conditional formatting pattern before editing the rule.
Use ROW and INDEX to create dynamic reference pairs
Use this method when each copied row should advance the source references by two rows, such as S15/S16 followed by S17/S18.
ROW calculates how far the conditional formatting formula has moved from its starting row. INDEX then uses that offset to return the matching cells from column S.
For a rule beginning in row 3, use the following expressions in place of the fixed references: =INDEX($S:$S,2*(ROW(A3)-ROW(A$3))+15) and =INDEX($S:$S,2*(ROW(A3)-ROW(A$3))+16).
In WPS Spreadsheet, select the rows or cells that should receive the copied conditional formatting rule.
Go to Home > Conditional Formatting > Manage Rules, select the rule, and click Edit Rule.
In the rule formula, replace the fixed S15 and S16 references with =INDEX($S:$S,2*(ROW(A3)-ROW(A$3))+15) and =INDEX($S:$S,2*(ROW(A3)-ROW(A$3))+16), while keeping your original comparison or logical operator.
In the Manage Rules dialog, check the Applies to range, extend it to the required rows, and click Apply or OK.
Copy the finished rule after making references relative
Use this option when the rule only needs standard relative references and the source cells advance at a consistent rate.
Build Dynamic Conditional Formatting Rules in WPS Spreadsheet
WPS Spreadsheet lets you edit conditional formatting formulas and use ROW with INDEX to create reference pairs that update automatically as the rule is applied to additional rows.
- 1. Open your spreadsheet: Launch WPS Office, open the workbook, and select the cells that use conditional formatting.
- 2. Edit the rule: Choose Home > Conditional Formatting > Manage Rules, select the target rule, and click Edit Rule.
- 3. Add dynamic references: Use ROW and INDEX expressions such as =INDEX($S:$S,2*(ROW(A3)-ROW(A$3))+15) and =INDEX($S:$S,2*(ROW(A3)-ROW(A$3))+16) in the rule formula.
- 4. Apply the rule to more rows: Update the Applies to range in Manage Rules, then click Apply or OK to test the sequential references.
Frequently Asked Questions
How do I make a conditional formatting reference change when I copy the rule down?
Edit the rule through Home > Conditional Formatting > Manage Rules > Edit Rule, then use ROW and INDEX instead of fixed references. For a rule starting in row 3, expressions using ROW(A3)-ROW(A$3) calculate the movement from the starting row.
Why does the next copied row need a different INDEX row number?
The formula uses 2 times the row offset, so each new formatted row advances the source by two rows. A rule starting with S15 and S16 therefore uses S17 and S18 on the next row.
Can I keep column S fixed while allowing the row to change?
Yes. Use $S:$S in INDEX to keep the lookup column fixed while the row number is calculated dynamically. The ROW expression determines which row from column S is returned.
Where can I change the range that receives the conditional formatting rule?
Open Home > Conditional Formatting > Manage Rules, select the rule, edit the Applies to field, and enter the complete destination range before clicking Apply or OK.
