Fix WPS Spreadsheets Conditional Formatting Highlighting the Wrong Date
Question details
The user needs to correct a conditional-formatting rule that highlights the wrong date because its formula references column B instead of the first column in the formatted range.
- Product
- WPS Spreadsheets conditional formatting
- Device & OS
- not provided
- Scenario
- Formatted rows start in column A, and the rule is intended to identify dates related to weekends or bank holidays.
- Observed behavior
- The rule highlights the day before a weekend or bank holiday incorrectly because the formula uses B$2 rather than the corresponding cell in column A.
Check the Applies to range in Conditional Formatting Rules Manager and identify its top-left cell. The formula must use references that align with that first cell, especially when the format is applied across entire rows.
Edit the conditional-formatting formula to reference column A
Use this method when the formatted range begins in column A but the existing formula evaluates a date in column B.
Conditional-formatting formulas are evaluated relative to the top-left cell of the Applies to range. If the formatted rows begin in column A, the date reference should use A$2 rather than B$2 so each row is evaluated against the correct date cell.
In WPS Spreadsheets, select a cell in the affected formatted range, then go to Home > Conditional Formatting > Manage Rules.
In the rules list, select the rule that highlights dates before weekends or bank holidays, and click Edit Rule.
In the formula box, replace the incorrect B$2 reference with A$2 while keeping the rest of the formula unchanged.
Check the Applies to box and make sure the range begins in column A if the formula uses A$2. Click OK, then click Apply to update the worksheet.
Match formula references to a different applied range
Use this method if the rule must begin in another column, such as column B, instead of formatting rows that start in column A.
Correct Date-Based Conditional Formatting in WPS Spreadsheets
WPS Spreadsheets lets you manage formula-based conditional-formatting rules from one rules manager. Edit the formula and its applied range together to ensure weekend and bank-holiday checks evaluate the correct date column.
- 1. Open the spreadsheet in WPS Spreadsheets: Open the workbook and select a cell within the rows that are being highlighted incorrectly.
- 2. Manage the formatting rule: Select Home > Conditional Formatting > Manage Rules to view the rules assigned to the selected range.
- 3. Edit the formula reference: Choose the affected rule, click Edit Rule, and change B$2 to A$2 when the formatted range begins in column A.
- 4. Verify the applied range: Confirm that the Applies to range starts at the same column used by the formula reference, then click Apply and OK.
Frequently Asked Questions
Why does conditional formatting highlight the row before the expected date?
The formula may be referencing the wrong column or may not be aligned with the top-left cell in the Applies to range. Open Home > Conditional Formatting > Manage Rules and compare the formula reference with the first column of that range.
Should I use A$2 or $A2 in a conditional-formatting formula?
Use the reference type required by how the rule is copied across the range. A$2 keeps row 2 fixed while allowing the column to adjust; $A2 keeps column A fixed while allowing the row to adjust. For this rule, use A$2 when that is the intended formula pattern and the range begins in column A.
How can I see which cells a conditional-formatting rule affects?
Select a formatted cell, then go to Home > Conditional Formatting > Manage Rules. The Applies to field shows the range controlled by the selected rule.
Can WPS Spreadsheets edit conditional formatting created in Excel?
Yes. Open the .xlsx or .xls file in WPS Spreadsheets, then use Home > Conditional Formatting > Manage Rules to review and edit supported conditional-formatting rules.
