How to Apply an Excel Conditional Formatting Rule in WPS Office Spreadsheets
Question details
The user needs to recreate an Excel for the web conditional formatting rule in the WPS Office desktop application so that cells are formatted when their value is less than or equal to the value in column E.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is moving an Excel for the web conditional formatting rule into a desktop workbook and wants to apply the same conditional formatting logic.
- Observed behavior
- The web-based conditional formatting rule is not automatically available in the desktop application and must be recreated manually.
Open the WPS Office spreadsheet and identify the exact range to format. Note the first row of that range, because the comparison formula must use the same row number as the selected range.
Recreate a Less Than or Equal To Conditional Formatting Rule in WPS Office Spreadsheets
Use this method when you need to apply a desktop conditional formatting rule that highlights cells based on a less-than-or-equal-to comparison with column E.
The rule keeps column E fixed and lets the row number adjust for each row in the selected range. The row number in the formula must match the first row of the selection so WPS Office evaluates every row correctly.
Open your workbook in WPS Office Spreadsheets and select the cells you want to format, such as B5:F20.
Click the Home tab, choose Conditional Formatting, and then select New Rule.
In the New Formatting Rule dialog, select Format only cells that contain.
Under Edit the Rule Description, choose Cell Value and less than or equal to, then enter =$E5 or the row reference that matches your first selected row.
Click Format, choose the fill color, font color, or other formatting, and then click OK to confirm the rule.
Create Excel-Compatible Conditional Formatting Rules in WPS Office Spreadsheets
WPS Office Spreadsheets lets you recreate Excel conditional formatting rules directly on the desktop. Use Home > Conditional Formatting > New Rule to compare each selected row against column E and apply the formatting you need.
- 1. Open your workbook in WPS Office: Download and open WPS Office Spreadsheets, then load the Excel file that contains the data you want to format.
- 2. Create the conditional formatting rule: Select the target range, go to Home > Conditional Formatting > New Rule, and choose Format only cells that contain.
- 3. Compare with column E and apply formatting: Choose less than or equal to, enter =$E5 or the row reference that matches your selection, click Format to set the style, and confirm.
Frequently Asked Questions
Why does the formula use =$E5 instead of =$E$5?
The $ before E locks the comparison to column E, while the row number stays relative. When the selected range starts at row 5, =$E5 tells WPS Office to compare each row with the value in column E on the same row.
Can I apply this rule to an entire column?
Yes, but the row number in the formula must match the first row of the selected range. For example, if you select A5:A1000, use =$E5. Empty cells may also be formatted unless the rule or its applied range is adjusted.
How do I edit or delete the conditional formatting rule later?
Select the formatted range or open the Conditional Formatting menu, choose Manage Rules, then select the rule to edit its condition or delete it.
Will WPS Office keep this conditional formatting when I save as an Excel file?
Yes, WPS Office Spreadsheets preserves conditional formatting in .xlsx files, so the rule remains available when the workbook is opened again in WPS Office or Microsoft Excel.
