How to Compare WPS Spreadsheets Values with an External List and Flag Missing Items
Question details
The user needs to compare alphanumeric values in a WPS Spreadsheets worksheet against a static external list and identify which values do not appear in that list without requiring the external workbook to remain open.

- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a worksheet of alphanumeric values and a static external reference list, and needs to flag missing items across one or many workbooks.
- Observed behavior
- The goal state is a worksheet where each value is checked against the static external list and missing items are clearly flagged or marked.
Make the reference list available inside the workbook or as a reusable template before applying the comparison formulas. If you use a formula, create a reference sheet or named range so the comparison still works after the external source file is closed.
Use a Named Reference List with COUNTIF Formula
Best for static external lists that can be placed into the active workbook as a hidden or reference sheet.
This method keeps the comparison formula responsive after the external source file is closed because the list lives inside the workbook.
Using a named range makes the formula easier to read and maintain across multiple sheets.
Open the workbook in WPS Spreadsheets, insert a new sheet named Reference, and paste the external alphanumeric list into column A.
Select the list cells, click Formulas > Name Manager > New, enter ReferenceList, and confirm the range.
In the target sheet, insert a Flag column and enter =IF(COUNTIF(ReferenceList,A2)=0,"Missing","Found") in the first data row, then fill down.
Use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains > Missing to highlight the flagged rows.

Highlight Missing Values Directly with Conditional Formatting
Use this when you want visual highlighting without adding a helper column.
Reuse a Template or Master Workbook for Many Files
Use this when you compare many workbooks and do not want to copy the reference list into every file manually.
Flag Missing Values in WPS Spreadsheets with a Reference List and COUNTIF
Use WPS Spreadsheets to compare worksheet values with a static external list by placing the list into a reference sheet or named range, then flag missing items with COUNTIF and conditional formatting.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx or .xls workbook that contains the values you want to compare.
- 2. Add the external list as a reference sheet: Create a new sheet, paste the static external list, and select the list range.
- 3. Create a named range: Click Formulas > Name Manager > New, name it ReferenceList, and confirm the range.
- 4. Add the comparison formula or conditional formatting: In the target sheet, enter =IF(COUNTIF(ReferenceList,A2)=0,"Missing","Found") in a helper column or apply a conditional formatting rule with =COUNTIF(ReferenceList,A2)=0.
- 5. Flag and review missing items: Copy the formula down or apply the rule to the data range, then filter or sort to review the Missing results.

Frequently Asked Questions
Can WPS Spreadsheets compare values with a closed external workbook?
Yes. Place the external list in a sheet inside the active workbook, create a defined name, or save it in a reusable template. A standard formula can then compare the values without the external source file remaining open.
Which formula flags missing items from a reference list?
Use =IF(COUNTIF(ReferenceList,A2)=0,"Missing","Found") in a helper column. It returns Missing when the value in A2 is not found in the ReferenceList range.
Can conditional formatting highlight missing values directly?
Yes. Select the data range, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =COUNTIF(ReferenceList,A2)=0, and choose a highlight format.
Do I need to keep the external reference file open?
No. After you add the list to a reference sheet, defined name, or template, the comparison continues to work even when the original external file is closed.
How can I reuse this comparison for many workbooks?
Create a master workbook or template that contains the reference list sheet, named range, and comparison formula. Then open a copy of that template for each new workbook and paste the new values into the target column.




