Highlight Overdue Unclosed Rows in WPS Spreadsheets with Conditional Formatting
Question details
Create a conditional formatting rule that highlights an entire row when its date in column H is overdue, the date is not blank, and the Closed By value in column K is not Yes.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user needs to visually identify records that are past their date but have not been closed.
- Observed behavior
- Rows should be highlighted only when column H contains a date earlier than today and column K does not contain Yes.
Confirm that column H contains real spreadsheet dates rather than text values and that the Closed By status is stored in column K. Select the full row range you want to highlight before creating the rule.
Apply a formula-based rule to highlight overdue unclosed rows
Use this rule when the due date is in column H and the Closed By status is in column K, starting with row 9.
The dollar signs lock the H and K columns while allowing the row number to change for every row in the selected range. This lets one rule evaluate and format the entire row correctly.
In WPS Spreadsheets, select the complete table range to highlight, such as A9:K200. Start the selection on the same row used in the formula.
Go to Home > Conditional Formatting > New Rule, then choose the option to use a formula to determine which cells to format.
Enter =AND($H9<>"",$H9<TODAY(),$K9<>"Yes"). Replace 9 with the first row number of your selected range if your data starts on a different row.
Select Format and choose a fill color or other formatting style, then click OK to save the rule and apply it to the selected range.
Adjust the formula for different status values or starting rows
Use this option if your worksheet uses a different first data row or a status value such as Closed instead of Yes.
Track overdue open items with WPS Spreadsheets
WPS Spreadsheets lets you create formula-based conditional formatting rules to flag overdue items across complete rows. Apply a TODAY-based formula to your table and keep open work visible automatically as dates change.
- 1. Open your spreadsheet: Launch WPS Spreadsheets and open the worksheet containing your due date and Closed By columns.
- 2. Select the data rows: Drag to select the full range of rows that should receive the overdue highlight.
- 3. Create a new rule: Choose Home > Conditional Formatting > New Rule and select a formula-based rule.
- 4. Add the overdue condition: Enter =AND($H9<>"",$H9<TODAY(),$K9<>"Yes") and adjust 9 to match your first selected row.
- 5. Set the highlight style: Choose a fill color in the Format options and confirm the rule to flag overdue unclosed records.
Frequently Asked Questions
Why does the formula include $H9<>""?
The condition $H9<>"" prevents blank date cells from being treated as overdue. A row must contain a date in column H before it can be highlighted.
How do I highlight the whole row instead of only the due date cell?
Select the complete row range, such as A9:K200, before you create the conditional formatting rule. Keep $H9 and $K9 in the formula so each selected cell evaluates the date and status for its own row.
What should I change if my first data row is row 2?
Replace every 9 in the formula with 2: =AND($H2<>"",$H2<TODAY(),$K2<>"Yes"). The formula row must match the first row of the selected Applies To range.
Can the rule ignore Yes regardless of capitalization?
Yes. Spreadsheet comparisons generally treat Yes, YES, and yes as equivalent in this formula. If values include extra spaces, clean the status column or use a trimmed helper value.
How can I edit the conditional formatting range later?
Go to Home > Conditional Formatting > Manage Rules, select the rule, and update its Applies To range or formula reference as needed.
