Data Import & Export

How to Compare Two Excel Tables for Missing Data or Duplicate Values

WPS Content Manager Oct 12, 2026 869 views

Question details

The user needs a reliable method to cross-reference and compare two sets of accounting data to find mismatched amounts, missing records, and duplicates.

How to Compare Two Excel Tables for Missing or Duplicate Values

Product: Excel
Device & OS: not provided
Scenario: Reconciling accounting records entered independently by two different people to ensure data accuracy.
Observed behavior: The user wants to identify missing records, duplicate entries, and data mismatches across two tables rather than manually checking line by line.

Before you start

Ensure both tables share at least one common column, such as an Invoice Number, Employee ID, or Product Code, to act as a unique identifier during the comparison.

Solution 1Recommended

Use VLOOKUP to Find Missing Records and Mismatches

Using a lookup function is the most accurate way to compare two tables line-by-line based on a unique identifier.

VLOOKUP searches for a value from your first table inside the second table. If the data is missing, it returns an error, making discrepancies easy to spot.

Create a comparison column

Add a new column next to your first table and name it 'Comparison Status'.

Enter the VLOOKUP formula

In the first cell of the new column, type the formula =VLOOKUP(A2, Sheet2!A:B, 2, FALSE), assuming A2 is your unique identifier and Sheet2 contains your second table.

Apply formula to all rows

Press Enter, then drag the fill handle at the bottom-right of the cell down to apply the formula to the rest of the rows.

Identify missing records

Look for #N/A errors in the results. An #N/A error indicates that the record from the first table is completely missing in the second table.

Use VLOOKUP to Find Missing Records and Mismatches

Data Format Warning: If you get unexpected #N/A errors, verify that there are no hidden spaces in your cells and that both columns are formatted exactly the same (e.g., both as Text or both as Numbers).

Solution 2

Highlight Duplicate or Unique Values Using Conditional Formatting

If you need a quick visual comparison without writing formulas, Conditional Formatting can instantly highlight identical or unique entries.

Solution 3

Merge Queries with Power Query

For complex or very large datasets, Power Query is the best tool to join tables and clearly map out matched and unmatched rows.

Efficient Data Processing with WPS Spreadsheet

Compare Tables Quickly with WPS Spreadsheet

WPS Spreadsheet provides full support for advanced lookup formulas, conditional formatting, and data comparison tools, making it easy to reconcile large datasets and identify duplicates or missing values effortlessly.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing the two tables you need to compare.
2. Use conditional formatting: Highlight your data columns, go to the Home tab, and use Conditional Formatting to easily color-code duplicate or unique entries.
3. Apply advanced lookup formulas: For detailed line-by-line reconciliation, enter a VLOOKUP or XLOOKUP formula to seamlessly cross-reference amounts and pinpoint discrepancies.

Fully compatible with Microsoft Excel formulas like VLOOKUP, XLOOKUP, and IFBuilt-in Highlight Duplicates feature for instant visual data comparisonLightweight and highly responsive even when analyzing massive accounting datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP returning #N/A even when the data visually seems to match?

This commonly happens due to hidden trailing spaces, differing data types (such as numbers being stored as text in one table but not the other), or spelling mistakes. You can use the TRIM() function to clean the data or ensure both columns are formatted identically.

Can I compare two tables that are located on completely different sheets?

Yes, you can easily reference a different sheet in your lookup formulas or conditional formatting rules. Simply switch to the other sheet while writing your formula to select the desired data range (for example, Sheet2!A1:B100).

Is there a way to compare multiple columns at the same time to check for duplicate records?

Yes, you can create a 'helper column' in both tables that concatenates the data from multiple columns using the ampersand symbol (e.g., =A2&B2&C2). You can then run your VLOOKUP or Conditional Formatting check on this new combined helper column.

How do I automatically remove the duplicate records after identifying them?

Select your entire table, navigate to the Data tab on the top ribbon, and click 'Remove Duplicates'. A prompt will allow you to choose which columns should be evaluated to define a duplicate. Click OK to instantly clean up your dataset.

Excel & Data Problems