How to compare two Excel files and find the rows that don't match

Start with the key, not the formulas

Every comparison hangs on one question: which column identifies the same record in both files? For a project status export and a time export, that's usually the project ID. Pick it before writing any formula.

Copy both exports into one workbook. Below, the sheets are Status (project ID in column A) and Time (project ID in column B). Work on copies.

Find rows in one file that are missing from the other

XLOOKUP is the cleanest tool for this. Its syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Exact match is the default, and if no match is found it returns the text you put in if_not_found instead of #N/A. So in a helper column on the Time sheet:

1. =XLOOKUP(B2, Status!A:A, Status!A:A, "MISSING")
2. Fill it down, then filter the helper column for MISSING. Those are hours logged against projects the status export doesn't have.
3. Run the same check the other way, from Status against Time, to find projects with no time at all.

On an older version without XLOOKUP, VLOOKUP works with an exact match (0 or FALSE as the last argument). Two catches: the value you look up must be in the first column of the range you give it, and a missing value comes back as #N/A, so filter for that.

Find duplicate rows

COUNTIF counts cells that meet one condition. =COUNTIF(B:B, B2) next to each row gives you how many times that key appears. Anything above 1 is repeated.

In a time export, one project usually has many entries, so a repeated ID is normal. A repeated entry isn't. COUNTIFS applies criteria across several ranges and counts the number of times all of them are met, so you can test project, date, person and hours together:

=COUNTIFS(B:B, B2, C:C, C2, D:D, D2, E:E, E2)

Each extra range has to be the same size as the first. A result above 1 means an identical line appears more than once.

For a visual pass, conditional formatting can highlight duplicates: select the column, then Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Be careful with Remove Duplicates: Microsoft says the duplicate data is permanently deleted. Flag first, decide second.

Spreadsheet Compare and Inquire, and who actually has them

Spreadsheet Compare compares two workbooks, or two versions of one workbook, and shows the results in a two-pane grid with changes highlighted by color. It's only available with Office Professional Plus 2013, 2016 or 2019, or Microsoft 365 Apps for enterprise.

Inside Excel, the Inquire tab has a Compare Files command that uses Spreadsheet Compare to show differences cell by cell. To turn Inquire on, go to File > Options > Add-Ins, pick COM Add-ins in the Manage box, select Go, and check Inquire. It's available only in Excel for Windows in Microsoft 365 Apps for enterprise plans and equivalent editions.

Because that comparison is cell by cell, when two exports list rows in a different order, a key-based lookup usually tells you more.

The traps that make a clean file look broken

• Trailing spaces. "P-104" and "P-104 " are different values. TRIM removes all spaces except single spaces between words, but on its own it doesn't remove the nonbreaking space (character 160).
• Different case. COUNTIF criteria aren't case sensitive, so "p-104" and "P-104" count as the same. If case matters in your IDs, EXACT compares two strings and is case sensitive.
• Numbers stored as text. The number 1042 and the text "1042" won't match. Microsoft notes that numbers stored as text can cause unexpected results, and Excel usually flags them with an alert on the cell. Select the cells, open the error indicator, and choose Convert to Number.
• Duplicate keys. XLOOKUP returns the item for the first match it finds. If the status export lists the same project twice, your lookup silently picks one. Run COUNTIF on the lookup side first.
• Totals that don't tie out. If total hours differ between the two files, sum by key on each side with SUMIFS, which adds values that meet multiple criteria, then compare project by project.

When it stops being a quick job

All of this works well once. It gets harder when the comparison has to run every month, when the files come from two different systems, or when someone has to decide which record wins. That needs written rules.

That's what our report reconciliation service does: you send the two exports, we reconcile them against written rules and return one checked report plus a list of every record that couldn't be matched. The service page has a downloadable synthetic example built on fictional data, a made-up consultancy's project status export with 40 projects and a time export with 300 rows, with problems planted on purpose: a duplicate time entry, hours logged against a project ID that doesn't exist, a blank project ID, and a project marked complete with hours logged after its completion date.

See the report reconciliation service