Compare data
Compare Two Lists in Excel: What's Missing, What's New, and What Changed
Compare two Excel lists to find shared records, missing rows on either side, and values that disagree after matching IDs.
TidyCell
Compare Files
Find added, absent and changed records in a separate comparison report.
Open Compare FilesTwo lists can disagree in several ways. A customer may be in both, only in yours, or only in theirs. Even when an ID appears in both, a price or balance may differ. Decide which of these questions you need answered before choosing an Excel formula.
The four useful answers
| Question | What it reveals | Excel approach |
|---|---|---|
| In both | Records shared by both lists | COUNTIF or an inner join |
| Only in yours | Rows missing from the other list | COUNTIF=0 or a left anti join |
| Only in theirs | New or unmatched records in the other list | Run the check in the other direction |
| In both, values differ | Disputed amounts or changed fields | Join by ID, then compare selected fields |
Start with a common ID
A customer ID, SKU, invoice number, or email address can connect the lists. Check whether both sides store that key in the same way. Trimming spaces or changing a text ID into a number can change the answer. Preserve leading zeroes when they are part of an identifier.
COUNTIF is the quickest presence check
With the ID in A2 and the second list in Sheet2!A:A, =COUNTIF(Sheet2!A:A,A2) tells you whether it appears in the second list. Filter for zeroes to see only records missing from the other list. Reverse the formula to catch records only in the other list. A count greater than one also reveals repeated keys that need review.
Compare the values after matching
Matching IDs alone does not prove that two exports agree. Bring the amount or status from the second list beside the first and compare those values explicitly. A difference can be a real change, a storage difference such as text versus number, or a rounding policy. State any tolerance you choose rather than silently hiding small differences.
Compare files and review a separate report
TidyCell Compare Files compares selected record keys and fields across two sheets or files. Review added, absent, changed and unchanged records, plus rows marked Needs Review. Duplicate or ambiguous keys require attention. The output is a comparison report; it does not overwrite either source or automatically merge the changes. Set the comparison direction and check the field mapping before running.
Open Compare FilesDo you want to combine fields instead?
If your goal is to add customer names, prices, or other columns from one list to another, use Match & Merge. It keeps the destination rows, identifies missing and repeated keys, and exports a new merged table. Comparing two versions for changes is a separate job.
Common questions
The sheets are in different files. Does that matter?
No. Excel can import both files into Power Query before comparing them. For a column merge, TidyCell also accepts a second file.
What if the two lists use different header names?
Pick the columns by their contents. Customer ID in one list can match Cust No in another when the actual values represent the same identifier.
Should I delete rows that do not match?
Keep them until you know why they differ. An unmatched invoice or customer may be the most important result of the comparison.