Fix Conditional Formatting That Does Not Extend to New Rows in WPS Office Spreadsheets
Question details
The user needs to make conditional formatting rules in WPS Office Spreadsheets automatically apply when new rows are added, without manually recreating or re-applying the rule.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user added new rows to an existing WPS Spreadsheets data range and expected the existing conditional formatting rules, such as highlighting or color scales, to cover those rows automatically.
- Observed behavior
- The conditional formatting stays on the original range and does not extend to newly added rows, leaving new data unformatted.
Open the workbook in WPS Office Spreadsheets, select the range that already has conditional formatting, and go to Home > Conditional Formatting > Manage Rules. Note the current formula and the exact Applies to range before changing anything.
Use a relative row reference and extend the Applies to range
Use this when the rule formula locks the wrong cell or the Applies to range ends at the old last row.
Conditional formatting treats references relative to the active cell in the Applies to range. If the formula uses an absolute row like =$A$2, every row tests the same cell, so new rows never evaluate correctly. Make the row relative and cover the future rows explicitly.
Select the formatted area, go to Home > Conditional Formatting > Manage Rules.
Choose the rule and click Edit Rule. Change the formula to use a relative row reference, for example =$A2="Yes", so each row evaluates column A.
In the Applies to field, enter the full data area including future rows, such as =$B$2:$L$1000, then click OK.
Add a test row inside the new range and confirm the formatting appears automatically.
Convert the range to an Excel Table so rules expand automatically
Best when you regularly add rows and want WPS Spreadsheets to manage expanding formatting ranges for you.
Remove conflicting duplicate conditional formatting rules
Use this when multiple overlapping rules stop new rows from updating correctly.
Fix Conditional Formatting in WPS Office Spreadsheets
Open Manage Rules, correct the formula to use relative row references, and expand the Applies to range or convert the range to a table so conditional formatting updates automatically in WPS Office Spreadsheets. WPS gives you the same familiar workflow with free, lightweight spreadsheet tools.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx workbook, and select the range that has conditional formatting.
- 2. Open Manage Rules: Go to Home > Conditional Formatting > Manage Rules.
- 3. Correct the rule references: Edit the rule to use a relative row reference such as =$A2="Yes" and set Applies to the full data range.
- 4. Apply and test: Click OK, add a new row inside the range, and confirm the formatting appears.
- 5. Optional: Use a table: Select the range, go to Insert > Table, and apply the rule to a column so it expands automatically.
Frequently Asked Questions
Why does my conditional formatting stop after a certain row?
The Applies to range usually ends at the last row that was formatted, or the formula uses an absolute reference such as =$A$2. Open Home > Conditional Formatting > Manage Rules, select the rule, and extend the Applies to range or change the formula to a relative row reference.
How do I make conditional formatting automatically expand in WPS Spreadsheets?
Convert the range to a table with Insert > Table. Conditional formatting applied to table columns extends when you add rows immediately below the table.
What formula should I use to highlight an entire row based on column A?
Use a formula such as =$A2="Yes" or =$A2<>"". The $ locks column A, while the relative row number makes each row evaluate separately.
Can multiple conditional formatting rules cause new rows not to be formatted?
Yes. Overlapping rules can conflict. In Home > Conditional Formatting > Manage Rules, delete duplicate outdated rules and keep one clean rule covering the full range.
Does WPS Spreadsheets support Excel conditional formatting rules?
Yes. WPS Office Spreadsheets opens and saves .xlsx files and preserves conditional formatting rules created in Microsoft Excel.
