How to Use Conditional Formatting to Track Task Completion in WPS Spreadsheets
Question details
The user wants to create conditional formatting rules in WPS Spreadsheets that color employee task rows red when work remains, green when individual tasks are complete, and fully green when all tasks are finished.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is managing an employee task list and needs visual status colors for pending, individual completed, and fully completed rows.
- Observed behavior
- The desired behavior is for task rows to automatically change color based on the status entries and the number of completed tasks.
Open the WPS Spreadsheets workbook that contains the employee task list and confirm the status column uses consistent wording such as Done before creating the rules.
Create Formula-Based Conditional Formatting Rules for Task Completion
Use this when you want precise control over green, red, and fully green row colors based on Done status and the number of completed tasks.
Formula-based rules let WPS Spreadsheets evaluate each task cell and apply a fill only when the condition is true. Place the fully completed row rule above the incomplete-row rule and enable Stop If True so it can override the remaining-work format.
Open your workbook in WPS Spreadsheets and select the employee task rows and columns you want to format, for example B2:E20.
Click the Home tab, choose Conditional Formatting, and then select Manage Rules.
Click New Rule, select Use a formula to determine which cells to format, and enter =ISNUMBER(SEARCH("Done",B2)) in the formula box. Click Format and choose a green fill.
Create another formula rule that checks the whole row, for example =COUNTIF($B2:$E2,"Done")=4, set a green fill, and place this rule above the incomplete-row rule.
Create a rule that formats rows with work remaining red, such as =COUNTIF($B2:$E2,"Done")<4, apply a red fill, and click OK.
Fix Rule Order and Contains Text Matching in the Rules Manager
Use this when the colors are applied, but the wrong row becomes green or the Done text condition is not matching.
Track Task Completion with Conditional Formatting in WPS Spreadsheets
Use WPS Spreadsheets to set up color-coded employee task rows, including green for completed tasks, red for remaining work, and a fully green row when all tasks are finished.
- 1. Open the task workbook: Launch WPS Spreadsheets and open your employee task workbook saved in .xlsx or .xls format.
- 2. Select the task range: Highlight the rows and columns that contain employee names, tasks, and the status entries such as Done or Pending.
- 3. Open Conditional Formatting: Click the Home tab, choose Conditional Formatting, and select Manage Rules to create or edit rules.
- 4. Add completion rules: Create a formula rule using =ISNUMBER(SEARCH("Done",B2)) for individual task completion and a COUNTIF rule for fully completed rows, then choose green fills.
- 5. Save and verify: Click OK, confirm the rows turn red, green, or fully green as intended, and save the workbook.
Frequently Asked Questions
Why does my Done text rule not match in WPS Spreadsheets?
Open Home > Conditional Formatting > Manage Rules and edit the Contains Text rule. Type Done without extra quotation marks; formula rules still need "Done" as a text value.
How do I make the entire row green only when all tasks are done?
Create a formula-based rule such as =COUNTIF($B2:$E2,"Done")=4 and apply it to the task row range. Place it above the incomplete-row rule and enable Stop If True.
How do I change conditional formatting rule order in WPS Spreadsheets?
Select the formatted range, go to Home > Conditional Formatting > Manage Rules, choose the rule, and use the up or down arrow buttons to reorder it.
Can I apply these conditional formatting rules to an existing Excel file?
Yes, open the .xlsx file in WPS Spreadsheets, create or edit the rules, and save it as an Excel file. The formatting remains compatible with Microsoft Excel.
Why does the row stay red even after I type Done?
The incomplete-row rule may be higher than the completed rule, or the status text may contain extra quotes or spaces. Check the rule order in Manage Rules and make sure the status cell contains only Done.
