Copy Conditional Formatting with Relative Formulas in WPS Spreadsheet
Question details
The user needs to copy a conditional formatting rule so relative references adjust correctly for each destination cell instead of remaining fixed.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user created a conditional formatting rule with a formula and wants to apply it across multiple cells or ranges.
- Observed behavior
- When copying, formulas with absolute references such as =$A$1>0 remain fixed, so the rule appears unchanged instead of adjusting to =B1>0, =C1>0, and similar relative destinations.
Open the workbook in WPS Spreadsheet and confirm that the conditional formatting rule uses the intended relative references, such as =A1>0, before copying.
Edit the Conditional Formatting Formula to Remove Unnecessary Dollar Signs
Use this when the rule contains absolute references and you want references to adjust as you copy the formatting to other cells.
Dollar signs before the column letter or row number fix that part of the reference. Removing them lets WPS Spreadsheet shift the reference relative to each destination cell.
Select the formatted cell, go to Home > Conditional Formatting > Manage Rules.
Select the rule and click Edit Rule, then update the formula from =$A$1>0 to =A1>0, keeping only the parts that must remain fixed.
Click OK, then select the source cell and use Home > Format Painter or copy and paste formatting to the destination range.
Use Format Painter to Copy an Existing Relative Rule
Use this when the source rule already uses relative references and you only need to apply it to other cells or ranges.
Fix Relative Conditional Formatting References in WPS Spreadsheet
Edit your conditional formatting rule in WPS Spreadsheet to remove fixed dollar signs, then copy the rule with Format Painter so references adjust position-by-position.
- 1. Open Manage Rules: Launch WPS Spreadsheet, open your workbook, select the formatted cell, and go to Home > Conditional Formatting > Manage Rules.
- 2. Edit the formula: Choose the rule and click Edit Rule. Replace =$A$1>0 with =A1>0, or keep only the dollar signs that must stay fixed.
- 3. Confirm the rule: Click OK to save the rule and verify that the preview updates to reflect the relative condition.
- 4. Copy the formatting: Select the source cell and click Home > Format Painter, then drag over the destination cells or range.
- 5. Check destinations: Reopen Manage Rules for a destination cell and confirm that the reference changed from =A1>0 to =B1>0, =C1>0, or the expected relative address.
Frequently Asked Questions
Why does my conditional formatting formula stay as =$A$1>0 after copying?
The dollar signs make the column and row absolute. Remove the $ signs from the parts that should shift, for example use =A1>0, then copy the rule with Format Painter.
How do I open Manage Rules in WPS Spreadsheet?
Select the cell that contains the rule, go to Home > Conditional Formatting > Manage Rules, select the rule, and click Edit Rule.
Can I keep one part of the reference fixed while copying?
Yes, use a mixed reference such as =$A1>0 to fix column A while allowing the row to adjust, or =A$1>0 to fix row 1 while allowing the column to adjust.
Does WPS Spreadsheet support Excel conditional formatting rules?
Yes, WPS Spreadsheet opens and saves .xlsx workbooks and supports compatible conditional formatting rules, including formula-based rules.
