WPS Spreadsheet: Add an Exception to a Conditional Formatting Rule
Question details
The user needs to highlight an entire row based on a value in column V while excluding rows that contain a specific fee description in column L.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Create a conditional formatting rule for a complete target range using two conditions: column V must contain YES, and column L must not contain 058 - Miscellaneous Fees-Wtg to Bill.
- Observed behavior
- The conditional formatting rule needs an exception so excluded fee rows are not highlighted.
Identify the complete range that should be formatted and note its first row number. Copy the fee description exactly as it appears in column L so the exclusion text matches the cell value.
Use an AND formula to exclude the specified fee rows
Use this solution when the entire row should be highlighted only when column V contains YES and column L does not contain the excluded fee description.
Apply the rule to the full target range. The dollar signs keep the rule checking columns V and L while allowing the row reference to change for each row in the selected range.
In WPS Spreadsheet, select the complete range of rows that should receive the conditional formatting, starting with the row used in the formula.
Go to the Home tab, select Conditional Formatting, and choose New Rule or Manage Rules, depending on your WPS Spreadsheet version.
Select the option to use a formula to determine which cells are formatted, then enter =AND($V2="YES",$L2<>"058 - Miscellaneous Fees-Wtg to Bill"). Replace 2 with the first row number of your selected range.
Click Format, choose the fill, font, or border style for qualifying rows, and select OK to save the rule.
In Conditional Formatting Rules Manager, verify that Applies to covers the entire target range, then click Apply or OK.
Build Excel-compatible conditional formatting rules in WPS Spreadsheet
WPS Spreadsheet lets you apply formula-based conditional formatting to complete rows, combine multiple conditions with AND, and exclude matching text with a not-equal comparison. The result can be saved and shared in Microsoft Excel-compatible formats.
- 1. Open your spreadsheet: Launch WPS Office, open the spreadsheet, and select the rows that need conditional formatting.
- 2. Create a formula rule: Use Home > Conditional Formatting > New Rule, then choose the formula-based rule option.
- 3. Enter the exception formula: Enter =AND($V2="YES",$L2<>"058 - Miscellaneous Fees-Wtg to Bill"), adjusting 2 to the first row in your selected range.
- 4. Apply and save the format: Choose the desired formatting, confirm the Applies to range, select OK, and save the workbook as an .xlsx file if needed.
Frequently Asked Questions
Why does the formula use $ before columns V and L?
The absolute column references keep the rule checking columns V and L even when the formatting is applied across multiple columns in each row. The row number remains relative so it updates for every row.
What row number should replace 2 in the formula?
Use the first row of the selected conditional formatting range. For example, if the range begins at row 5, use =AND($V5="YES",$L5<>"058 - Miscellaneous Fees-Wtg to Bill").
Can I exclude a different description from the highlighted rows?
Yes. Replace the text between the quotation marks after $L2<> with the exact description that should be excluded, while keeping the quotation marks.
Why are some rows not highlighted even though column V says YES?
Check whether column L contains the excluded fee description, whether the formula row matches the first row of the applied range, and whether the Applies to range includes the row.
Can this WPS Spreadsheet rule be used with Excel files?
Yes. WPS Spreadsheet supports opening and saving Microsoft Excel .xlsx workbooks, including formula-based conditional formatting in compatible files.
