Use AND in Excel Conditional Formatting to Format Entire Rows in WPS Spreadsheet
Question details
The user needs to apply conditional formatting to entire rows based on two conditions: the warning date is today or earlier and the release date cell is blank.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user tracks deadlines in a spreadsheet and wants a row to stand out only when the warning date in column F is today or earlier and the release date in column L is blank.
- Observed behavior
- The current conditional formatting rule only evaluates the warning date, so rows are highlighted even after a release date has been entered.
Confirm that the warning date column contains actual date values and the release date column uses blank or empty cells, then note the first row number of the range you plan to format because the formula must use that row number.
Use an AND formula to apply conditional formatting to entire rows
Use this when you need one rule that checks both a due date and a blank release date before highlighting the row.
The formula locks columns F and L with dollar signs but leaves the row number relative, so WPS Spreadsheet evaluates each row independently against columns F and L.
Select the complete range you want to format, for example A3:L100, starting with the first data row.
Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
In the formula field, type =AND($F3<=TODAY(),$L3="") and adjust the row number 3 to match the first selected row.
Click Format, choose a fill color, and select OK to close the formatting dialogs.
Use ISBLANK in the AND formula for an explicit blank-cell check
Use this variation when release date cells are truly empty and you prefer a clearer blank-cell test.
Highlight Entire Rows with AND-Based Conditional Formatting in WPS Spreadsheet
Use WPS Spreadsheet's formula-based conditional formatting to highlight a row only when the warning date is today or earlier and the release date is blank.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook that contains the warning date and release date columns.
- 2. Select the rows to format: Select the data range such as A3:L100; the first selected row number determines the row reference in the formula.
- 3. Create a formula-based rule: Click Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
- 4. Enter the AND formula: Type =AND($F3<=TODAY(),$L3="") and adjust $F3 and $L3 to the correct first row.
- 5. Apply highlight formatting: Click Format, choose the row color, and select OK to apply the rule.
Frequently Asked Questions
Why does my condition still highlight rows that already have a release date?
A single warning-date rule ignores the release column. Replace it with =AND($F3<=TODAY(),$L3="") so WPS Spreadsheet checks both the due date and the blank release date before formatting.
What do the dollar signs mean in =AND($F3<=TODAY(),$L3="")?
$F and $L lock the formula to those columns while the row number remains relative. Each selected row is evaluated against its own F and L cells.
What row number should I use in the formula?
Use the number of the first row in your selected range. For example, if the selected range starts at row 3, use $F3 and $L3.
Can I use ISBLANK instead of ="" in the rule?
Yes. Use =AND($F3<=TODAY(),ISBLANK($L3)) if the release date cells are truly empty. If they contain formulas returning empty text, ="" is more reliable.
Will this conditional formatting rule work in Microsoft Excel?
Yes. Formula-based conditional formatting with AND, TODAY and empty-cell checks works in both WPS Spreadsheet and Microsoft Excel, so the same .xlsx rule can be shared.
