How to Highlight Assets Serviced but Missing from an Excel Schedule in WPS Spreadsheets
Question details
The user needs to compare service results against a service schedule and highlight assets that were serviced at a site but have no matching scheduled entry.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Reviewing field service results in a spreadsheet and identifying work that was completed without being scheduled.
- Observed behavior
- Assets appear in the service results but are not listed in the schedule for the matching asset and site combination, so they remain unmarked.
Make sure the service results sheet and the schedule sheet are in the same workbook, and identify the Asset, Site, and Schedule Date columns before writing the formula.
Add a Helper Column with COUNTIFS and Apply Conditional Formatting
Use this when you have service data on one sheet and a schedule on another, and you need a visible flag for assets that were serviced without being scheduled.
The COUNTIFS function checks whether the same Asset and Site appear in the service results sheet, while IF and AND add the requirement that the schedule date cell is blank. A helper column keeps the comparison easy to audit and works directly in WPS Spreadsheets.
In WPS Spreadsheets, click the schedule worksheet tab, then select cell D2 or the first blank cell in a new helper column and type the header Status.
With D2 selected, enter =IF(AND(COUNTIFS(DATA2!$B:$B,$A2,DATA2!$A:$A,$B2)>0,$C2=""),"Serviced, Not Scheduled","") and press Enter. Adjust DATA2, $A2, $B2, and $C2 to match your workbook columns.
Drag the fill handle from D2 down through all schedule rows so each asset row is evaluated against the service results.
Select the helper column range such as D2:D100, go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, enter =D2<>"" and select a highlight color.
Look for cells that display Serviced, Not Scheduled. These are the assets that were serviced at a site but are missing from the schedule.
Highlight Unscheduled Assets in WPS Spreadsheets
Open your Excel schedule in WPS Spreadsheets, add the COUNTIFS helper column and conditional formatting rule to flag serviced assets that have no schedule date. The process uses familiar Excel formulas and formatting, so you can complete the task without switching software.
- 1. Open your schedule workbook in WPS Spreadsheets: Click Home > Open and double-click the .xlsx schedule file to load it in WPS Spreadsheets.
- 2. Add the helper column and formula: Insert a new column in the schedule table and enter the COUNTIFS, IF, and AND formula in the first data row.
- 3. Apply conditional formatting: Select the helper column range, go to Home > Conditional Formatting > New Rule, and create a formula-based rule with =D2<>"".
- 4. Review and save: Check the highlighted rows, then save the file as .xlsx to preserve compatibility with Excel.
Frequently Asked Questions
Can I highlight unscheduled assets without adding a helper column?
You can create a conditional formatting rule directly on the asset column, but a helper column makes the logic easier to audit. If you use direct formatting, include COUNTIFS with asset and site criteria and a condition for the blank schedule date.
Why does the formula compare both Asset and Site?
Comparing both Asset and Site prevents assets with the same name at different locations from being matched incorrectly. COUNTIFS uses both criteria so only rows with the same asset and site in the service results are counted.
What should I adjust if my data has different column positions?
Change $A2, $B2, and $C2 to your actual Asset, Site, and Schedule Date columns in the schedule sheet, and change DATA2!$A:$A and DATA2!$B:$B to the matching service-results columns.
Does this work in WPS Spreadsheets when the original file is in Excel?
Yes. WPS Spreadsheets opens .xlsx files and supports COUNTIFS, IF, AND, and formula-based conditional formatting, so the same steps work.
Why is my conditional formatting not highlighting anything?
Check that the helper column is not empty, the rule references the first cell in the selected range, and the formula uses no absolute row locking, such as =D2<>"".
