How to Find Values in One Excel Column Missing from Another in WPS Office
Question details
The user wants to compare two Excel columns and list or highlight values that appear in column B but not in column A.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Comparing two lists or columns in a spreadsheet to identify differences.
- Observed behavior
- Values in column B that are missing from column A need to be listed or highlighted.
Open the workbook in WPS Office Spreadsheets and confirm the exact ranges for the two columns, for example A3:A11 and B3:B11.
Return missing values with a FILTER and COUNTIF formula
Use this method when you want WPS Office Spreadsheets to produce a separate list of values found in column B but not in column A.
This formula checks each cell in column B and returns only the non-blank values that do not appear anywhere in column A. It works best in WPS Office versions that support dynamic array functions, where the result spills automatically.
Click an empty cell where the missing-value list should start, such as D2.
Type =FILTER(B3:B11,(B3:B11<>"")*(COUNTIF(A:A,B3:B11)=0),"") and press Enter.
Replace B3:B11 and A:A with your actual column ranges. The formula returns values in column B that do not appear in column A.
Highlight missing values with conditional formatting
Use this method when you want to keep the original column B visible and mark mismatched cells with a fill color or font style.
Find Values in One Excel Column That Are Missing from Another in WPS Office
WPS Office Spreadsheets gives you the formulas and conditional formatting tools to compare lists directly, so you can identify missing values without switching software.
- 1. Open your workbook in WPS Office: Launch WPS Office Spreadsheets and open the Excel file that contains the two columns.
- 2. Apply the comparison formula: Click an empty cell and enter =FILTER(B3:B11,(B3:B11<>"")*(COUNTIF(A:A,B3:B11)=0),"") to list missing values.
- 3. Add a highlighting rule: Select column B, go to Home > Conditional Formatting > New Rule, and use =AND(B3<>"",COUNTIF($A:$A,B3)=0).
- 4. Adjust ranges and formatting: Update A:A and B3:B11 to your actual data ranges, then choose a fill color or font style.
Frequently Asked Questions
Can I find missing values between two columns without a formula?
Yes. Select column B, go to Home > Conditional Formatting > New Rule, choose "Use a formula to determine which cells to format", and enter =AND(B3<>"",COUNTIF($A:$A,B3)=0). This highlights the cells instead of extracting a list.
What formula lists values in column B that are not in column A?
In WPS Office Spreadsheets, use =FILTER(B3:B11,(B3:B11<>"")*(COUNTIF(A:A,B3:B11)=0),""). Replace B3:B11 with your column B range and A:A with the column A lookup range.
Does the comparison ignore blank cells?
The formula condition (B3:B11<>"") excludes blank cells, so blanks are not returned as missing values. In conditional formatting, the AND function includes B3<>"" to avoid highlighting empty cells.
Can WPS Office open and edit my Microsoft Excel file for this task?
Yes. WPS Office Spreadsheets supports .xlsx, .xls, and .csv files, so you can open the workbook, apply the comparison formula or rule, and save it back in Microsoft Excel format.
