WPS Spreadsheets: Compare Two Sheets and Highlight Matching or Missing Values
Question details
The user needs to compare values in two worksheets and highlight entries that match or are missing from the other sheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Two worksheets contain text and numeric lists in shared columns, but the lists have different lengths. The user wants to identify matching and missing values.
- Observed behavior
- The worksheets contain different sets of values, and the user needs visual highlighting to show whether each entry exists in the other worksheet.
Confirm which columns contain the shared lists and check that corresponding values use compatible data types. Numbers stored as text may not match numeric values, even when they look identical.
Use COUNTIF conditional formatting to highlight missing values
Use this method when you want WPS Spreadsheets to automatically mark values in one sheet that do not appear in the comparison sheet.
COUNTIF checks whether each value appears in the target column. A result of zero means the value is missing from the other worksheet, while a result greater than zero means it exists there.
Open the first worksheet and select the values to check, such as column B. Make sure B1 is the active cell when creating the rule.
Go to Home > Conditional Formatting > New Rule, select Use a formula to determine which cells to format, and enter =COUNTIF(Sheet2!$C:$C,B1)=0.
Click Format, choose a fill color or font style, select OK, and then click Apply or OK to highlight values from the first sheet that are absent from Sheet2 column C.
To highlight values that do exist in Sheet2, create another conditional formatting rule with =COUNTIF(Sheet2!$C:$C,B1)>0 and apply a different format.
Use MATCH to identify values missing from the other worksheet
Use this alternative when you prefer an exact-match lookup formula or need a clear missing-value test.
Compare worksheets and flag missing values in WPS Spreadsheets
WPS Spreadsheets lets you compare shared columns with COUNTIF or MATCH formulas and apply conditional formatting to distinguish matching and missing entries. Its familiar spreadsheet interface supports efficient work with Excel-compatible files.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the two worksheets.
- 2. Select the values to compare: Select the source column, such as B, and confirm the corresponding comparison column on the second worksheet, such as Sheet2 column C.
- 3. Add the missing-value rule: Choose Home > Conditional Formatting > New Rule, select the formula-based rule, and enter =COUNTIF(Sheet2!$C:$C,B1)=0.
- 4. Format and apply the results: Choose a highlight style, click OK, and review the source column for entries that are not present in the second worksheet.
Frequently Asked Questions
How do I highlight values that exist in both WPS Spreadsheets worksheets?
Select the source range, open Home > Conditional Formatting > New Rule, choose a formula-based rule, and enter =COUNTIF(Sheet2!$C:$C,B1)>0. Apply a highlight style to mark matching values.
Why does COUNTIF not find a value that appears identical?
The values may use different data types, such as a number in one sheet and text in the other. Convert both columns to the same type and check for leading or trailing spaces before applying the rule again.
Can I compare columns with different numbers of rows?
Yes. COUNTIF and MATCH search the entire comparison column, so the two lists do not need to have the same length. Select the full source range that should be checked.
What formula highlights values missing from another worksheet?
For a value in B1 compared with Sheet2 column C, use =COUNTIF(Sheet2!$C:$C,B1)=0 in a conditional formatting rule. You can also use =ISERROR(MATCH(B1,Sheet2!$C:$C,0)).
How do I compare sheets when the worksheet name contains spaces?
Wrap the worksheet name in single quotation marks, such as =COUNTIF('January Data'!$C:$C,B1)=0. Keep the comparison column absolute and the source cell relative.
