How to Apply Conditional Formatting to Excel FILTER Formula Results in WPS Office
Question details
The user needs to apply conditional formatting to a dynamic FILTER formula result in WPS Office so that filtered project rows are highlighted in different colors by project type.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a FILTER formula that returns project results and wants Existing and New projects to appear as blue and yellow rows.
- Observed behavior
- The current filtered output does not include the project type column, so formula-based conditional formatting cannot reliably evaluate the displayed rows.
Before creating the rules, check that your FILTER formula includes the project type column in the returned range. You can hide the column after the conditional formatting is applied.
Include the Project Type Column and Create Formula-Based Conditional Formatting Rules
Use this method when you want the filtered rows to update their colors automatically as the FILTER results change.
The FILTER result must contain the project type column even if it is not meant to be visible. Conditional formatting evaluates the displayed range against that column, so hiding it later does not stop the rules from working.
Edit the FILTER formula so the include argument returns all needed columns, including the project type column. For example, if the type is in column D on the source sheet, make sure that column is part of the result range.
Select the first row of the FILTER output and extend the selection over all rows and columns that should receive the highlight.
Go to the Home tab, click Conditional Formatting, and choose New Rule. Select Use a formula to determine which cells to format.
Enter a formula such as =$D2="EXISTING" if the project type is in column D. Click Format, set the blue fill, and confirm.
Repeat the steps with =$D2="NEW" and set the yellow fill. If necessary, hide the project type column after applying the rules.
Use WPS Office to Highlight FILTER Results by Project Type
WPS Office supports dynamic array formulas and formula-based conditional formatting, making it easy to apply the same Existing and New project color rules described in this guide.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the workbook that contains the FILTER formula, and confirm the formula is returning the expected columns.
- 2. Select the result range: Select the FILTER output range, starting from the top-left cell of the results area.
- 3. Create formula-based rules: Click Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =$D2="EXISTING" or =$D2="NEW" depending on the project type column.
- 4. Apply blue and yellow formats: Set the blue format for the Existing rule and the yellow format for the New rule, then click OK to apply both rules to the selected range.
Frequently Asked Questions
Can I hide the project type column after applying conditional formatting?
Yes. The formulas still evaluate the project type because the column exists in the result range. Hide it rather than deleting it, and the color rules will continue to work.
What conditional formatting formula should I use when the project type is in column D?
Use a formula like =$D2="EXISTING" for Existing projects and =$D2="NEW" for New projects. If the first row in your selected range is not row 2, change the row number to match the first row of the selection.
Does this method work when the FILTER results change?
Yes. Formula-based conditional formatting recalculates whenever the displayed FILTER results update, so new rows inherit the correct color based on the project type column.
Does WPS Office support the Excel FILTER function?
WPS Office supports the FILTER function and formula-based conditional formatting in compatible spreadsheet versions. Open your .xlsx workbook and the rules can be applied directly.
