Data Import & Export

How to Compare WPS Spreadsheets Values with an External List and Flag Missing Items

Anum MushtaqAnum Mushtaq Nov 13, 2026 871 views

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.

How to Compare WPS Spreadsheets Values with an External List and Flag Missing Items
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.
Before you start

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.

Solution 1Recommended

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.

1
Add the external list to a reference sheet

Open the workbook in WPS Spreadsheets, insert a new sheet named Reference, and paste the external alphanumeric list into column A.

2
Create a named range for the list

Select the list cells, click Formulas > Name Manager > New, enter ReferenceList, and confirm the range.

3
Enter the comparison formula

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.

4
Filter or format the Flag column

Use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains > Missing to highlight the flagged rows.

Use a Named Reference List with COUNTIF Formula
Missing values are flagged: The formula returns Missing only when the value in A2 is not present in ReferenceList.
WPS Spreadsheets comparison

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. 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. 2. Add the external list as a reference sheet: Create a new sheet, paste the static external list, and select the list range.
  3. 3. Create a named range: Click Formulas > Name Manager > New, name it ReferenceList, and confirm the range.
  4. 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. 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.
Flags missing alphanumeric values without keeping the external source file openUses Excel-compatible COUNTIF and conditional formatting workflowsOpens and saves .xlsx, .xls, and .csv files with formatting preservedFree, lightweight alternative to Microsoft Office for data comparison tasksTemplate or named range setup makes repeated comparisons faster
microsoft office alternative - wps office

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.