You have two versions of a list: last month and this month, the payroll file and the HR roster, the bank export and your ledger. You need the rows that appear in one but not the other. Here are three ways to do it, from quickest to most repeatable.
Before you start: pick the key column
Every method needs one column that identifies a row in both files: an employee ID, invoice number, SKU, email or transaction reference. Names and descriptions make poor keys because spelling varies. If no single column is unique, combine two (for example Date + Amount) in a helper column first.
Also clean the obvious mismatches: trailing spaces, SKU-001 vs sku-001, and numbers stored as text. These cause most "it says missing but it's right there" problems.
Method 1: No formulas (fastest)
- Open Compare Excel Files and upload File A and File B (.xlsx or .csv).
- Pick the key column in each file. The columns can have different names.
- Leave Fuzzy Match ticked to ignore extra spaces and letter case.
- Download the report. It has two sheets, named "Only in" plus each file name: the rows found only in File A, and the rows found only in File B, with every column kept.
The files are read in your browser, which matters for payroll and customer data. If you only need to compare two lists of IDs rather than whole rows, Compare Two Columns is quicker: paste both lists and get matches, only-in-A and only-in-B.
Method 2: A formula in Excel or Google Sheets
In File A, add a column next to your data and check whether each key exists in File B:
=IF(COUNTIF(FileB!A:A, A2)=0, "Missing in B", "")Fill it down, then filter the column for "Missing in B". Repeat in File B to find rows missing from A. XLOOKUP or MATCH work too. This is fine for one-off checks but gets slow on very large sheets, and you have to rebuild it for every new file.
Method 3: Power Query anti-join (repeatable)
In Excel, load both tables with Data > From Table/Range, then use Merge Queries with Left Anti join on the key column. The result is every row of the first table with no match in the second. It refreshes when the source files change, which suits a monthly reconciliation you run the same way each time.
Real examples
- Payroll vs HR roster: key = employee ID. Rows only in payroll may be leavers still being paid; rows only in the roster may be new joiners not yet on payroll.
- Bank statement vs ledger: key = transaction reference, or Date + Amount if there is no shared reference.
- Inventory vs store listings: key = SKU. See the Amazon listings guide for a worked example.
Which method should you use?
Use the browser tool for a quick answer with no setup, the formula when you are already working inside the sheet, and Power Query when the same reconciliation repeats every week or month.