How to Compare Two Excel Files and See Exactly What Changed

Last month's export and this month's, and the question is what moved. Opening both side by side and scrolling is how this usually gets done, and it is how differences get missed.

The short answer

Align the files and diff them rather than reading them. In DataTray, open Compare Files, pick two files — or two sheets, or two folders of Parquet — and a key column. It reports schema drift first, then diffs rows: with a key you get added, removed and changed, with changed rows broken down cell by cell. Tolerances keep float noise and timestamp granularity out of the result. Out comes a report you can email and an .xlsx with a sheet per category.

Schema drift comes first, and it is usually the answer

Before any row is compared, the two files' columns are aligned by name and the differences reported: columns added, columns removed, columns renamed by position, and — the one that causes the most damage — columns whose type changed.

A column that was a number last month and is text this month will break whatever consumes it, and it will do so in a way that looks like a data problem rather than a schema problem. Reporting it before the row-level detail means you usually stop reading there, because you have found it.

With a key, and without

With a key column, a full outer join gives you three clean categories — rows only in the old file, rows only in the new one, and rows in both whose values differ, with the specific cells highlighted.

Without a key, you get added and removed only. This is a real limitation rather than a missing feature: with nothing identifying a row across the two files, a changed row is indistinguishable from one removal plus one addition. A tool that pretends otherwise is guessing.

DataTray says which mode you are in, and if you have not picked a key it offers the candidates its own column profiling found — the columns that are unique and non-null.

Tolerances, or the diff is useless

A naive comparison on real numeric data reports thousands of differences that are not differences.

• Numeric tolerance — treat values within ±0.01 as equal, so floating-point representation noise stops being news.
• Date granularity — compare by day when one file holds dates and the other holds timestamps.
• Case and whitespace insensitivity for strings, optional, for when one system trims and the other does not.
• An ignore list for the columns that always change and never matter — a last_exported_at stamp, a row number.

Without these, the first comparison of two real files produces a wall of changes and everybody goes back to scrolling.

The output is meant to be forwarded

Two artefacts, because two different people need this:

The comparison report is a self-contained HTML file — schema drift, counts, and a colour-coded cell-level table. It references no external stylesheet or script, so it opens correctly in an email client and on a machine with no internet connection.

The workbook has one sheet per category: Added, Removed, Changed, Schema. This is the form people actually circulate, because the recipient can filter and comment on it.

What Excel's own tools do and do not do

Excel has Inquire, which compares workbooks, and it is genuinely useful for spreadsheets — it understands formulas and formatting. But it is only in some editions, it is oriented at spreadsheet structure rather than at data, and it inherits the row ceiling.

If what you have is two data files rather than two spreadsheets — an export from last month and one from this month — a data diff with a key column and tolerances is the better shape of tool.

More than two files, and other formats

Compare two sheets of the same workbook, two CSVs, two Parquet directories, or one of each. The comparison is the same either way, because both sides are read into the same shape before anything is diffed.

---

Every other free way to do this asks you to upload both files first. Two versions of the same internal export is twice the reason not to.