How to Stop Conditional Formatting from Extending to Other Columns in WPS Spreadsheets
Question details
The user needs to stop a duplicate-value conditional formatting rule applied to column B from affecting columns C and D.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user applied a duplicate-value conditional formatting rule to column B, but the formatting is also appearing in adjacent columns C and D.
- Observed behavior
- Conditional formatting extends beyond the intended column B range into columns C and D.
Open the workbook in WPS Spreadsheets, select the sheet containing the conditional formatting, and use Home > Conditional Formatting > Manage Rules to review the current Applies To ranges before making changes.
Clear and Reapply the Duplicate Values Rule on the Intended Column B Range
Use this solution to remove the existing overlapping rules and create a clean duplicate-values rule that affects only column B.
Conditional formatting usually extends because the rule's Applies To range includes more cells than intended, or because leftover rules from previous formatting remain in the worksheet.
Clearing the affected rules and reapplying duplicate values to a fixed column B range avoids hidden overlapping rules.
Go to Home > Conditional Formatting > Manage Rules in WPS Spreadsheets.
In the Conditional Formatting Rules Manager, select any rule whose Applies To range includes columns C or D, then click Delete Rule.
Close the dialog, select only the intended cells in column B, for example B2:B100, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Choose the formatting you want and click OK.
Open Home > Conditional Formatting > Manage Rules again and confirm that Applies To contains only the column B range.
Audit and Delete Leftover Formatting Rules with the Rules Manager
Use this solution if you have several conditional formatting rules and need to identify which Applies To ranges are causing the unwanted formatting before creating a new rule.
Stop Conditional Formatting From Extending to Other Columns in WPS Spreadsheets
Use the Conditional Formatting Rules Manager in WPS Spreadsheets to remove unwanted rules and reapply Duplicate Values only to column B, so formatting stays exactly where you intend.
- 1. Open Manage Rules: Open your workbook in WPS Spreadsheets and go to Home > Conditional Formatting > Manage Rules.
- 2. Delete rules affecting columns C and D: In the Rules Manager, select each rule where Applies To includes C or D and click Delete Rule.
- 3. Select only column B: Close the manager, select the intended column B range, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- 4. Reapply duplicate values: Choose a formatting style and click OK.
- 5. Verify the range: Open Manage Rules again and confirm Applies To only lists the column B range.
Frequently Asked Questions
Why is a duplicate-values rule applied to column B also formatting columns C and D?
This usually means the rule’s Applies To range includes C and D, or another rule is active in those columns. Open Home > Conditional Formatting > Manage Rules and check the Applies To column.
How do I see the Applies To range for a conditional formatting rule in WPS Spreadsheets?
Select the sheet or affected cells, open Home > Conditional Formatting > Manage Rules, and check the Applies To field for each rule.
Can I delete a conditional formatting rule from only one column?
Yes. In the Conditional Formatting Rules Manager, select the rule that includes the column you want to clear and click Delete Rule, then reapply formatting only to the intended range.
What should I do if conditional formatting keeps expanding after I reapply it?
Save, close, and reopen the workbook, then open Manage Rules and verify the Applies To range. Avoid applying the rule to entire rows or columns when only column B should be affected.
Does WPS Spreadsheets support conditional formatting created in Microsoft Excel?
Yes, WPS Spreadsheets opens and preserves conditional formatting in .xlsx and .xls files, and the Rules Manager works with those workbook formats.
