How to Compare Two Excel Files and Highlight the Differences

When people say "compare two Excel files," they usually mean two versions of the same spreadsheet — last month's and this month's, yours and a colleague's, the one you sent and the one that came back — and they want to see what changed: which values moved, which rows were added, and which were removed. That is a different job from comparing two separate lists to find which entries are missing from one of them; if that's what you need, comparing two lists in Excel to find missing records is its own method. This guide is about the version-comparison job: highlighting the differences between two workbooks, and getting a result you can hand to someone else.

There's a native way that's fine for a quick look, a more careful way that survives rows moving around, and a dedicated Microsoft tool that only some Excel editions have. The first method has a specific trap: an inserted row can create position-based mismatches. A highlight that compares cells by position looks perfect until a row moves, and then it lights up half the sheet in false differences. Here's each method and when to trust it.

01Compare two Excel files and highlight the differences

The simplest way to see two files together is View Side by Side. Open both workbooks, go to the View tab, and click View Side by Side; turn on Synchronous Scrolling so both files move together. It's a manual visual scan with no marks, but for a small sheet it's often all you need.

To actually highlight the changed cells, use conditional formatting. There's one catch worth knowing up front: Excel won't let a conditional-formatting formula point at a different workbook, so both sheets have to live in the same file first. Right-click the second version's tab, choose Move or Copy, and copy it next to the original. Then:

• Select the data range on your original sheet.
• Go to Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format."
• Enter =A2<>OtherSheet!A2 (point the second reference at the same cell on the other sheet).
• Click Format, pick a fill color, and click OK.

Every cell whose value differs from the other version is now colored. This is the answer to "compare two Excel files and highlight differences" for two sheets that line up row for row — and that condition is exactly where it gets fragile.

02Cell-by-cell vs row-by-key: why the second is what you want when rows move

Conditional formatting compares by position: it checks row 5 against row 5, row 6 against row 6, and so on. That's correct only if nobody added, deleted, or sorted a row. The moment someone inserts a single row near the top of one version, every row beneath it shifts by one, and the position-based comparison reports a wall of differences that aren't real — the data matched, it just moved. On a version that a few people have edited, this happens whenever a row is inserted, deleted, or sorted.

What you actually want is a comparison keyed on an identifier — a SKU, an invoice number, an email, a customer ID — so each row is matched to its counterpart by what it is, not by where it sits. A keyed comparison answers the questions that matter: which keys exist in both versions and changed, which are new in the later file, and which disappeared from it. Excel's own robust route for this is Power Query. First make the key column the same data type in both tables. A Left Anti merge, run both ways, finds the rows added in the newer version and removed since the earlier one — but it only returns keys present on one side, so it can't reveal changed values for keys that exist in both. To catch those, merge on the key with a Full Outer join, expand both versions' columns, and compare the paired fields. It works and it refreshes, but it's several fiddly steps and it still leaves you to assemble the highlighted output by hand.

03Show added, removed, and changed rows — with an export

When the two files are real versions with rows that moved, and you need the answer as something you can send rather than a colored sheet you have to explain, the question stops being "which formula" and becomes "just tell me what changed, keyed by ID, and give me a highlighted workbook I can hand over." That is what Anomaly does in one request.

In Anomaly you import each version as a table — the earlier file as one upload, the newer file as a second — name the column that uniquely identifies each row as the key (a stable, nonblank key, or a composite key, with blank or duplicate keys checked first), and ask in plain language:

"These two tables are two versions of the same sheet, keyed by sku. Show every difference — for each changed row, which column changed and the old value vs the new value; the rows added in the newer version; and the rows removed since the earlier one. Give me a differences table I can download, and a highlighted Excel workbook with the changes color-coded."

It matches the two versions on the key with a full outer join, classifies every row as changed, added, removed, or unchanged, and builds a downloadable Excel workbook that highlights them — amber for a changed cell, a green row for an addition, a red row for a removal — alongside a plain differences table (one row per change, with the old and new value). Because it matched on the key rather than the row position, inserting or reordering rows does not create positional mismatches — provided the key is clean and uniquely identifies each row.

The highlighted comparison, keyed by SKU

Two honest limits. The comparison is exact matching on a key you name (here sku) — it is not fuzzy or tolerance matching, and it is not a positional row-by-row diff, so tell it which column is the key, and make sure that key is stable, nonblank, and unique (or a composite key), since a non-unique key can multiply matches and blur the result. And the highlighting is delivered as color fills in the workbook you download, not painted onto your original file in place. Within those bounds it does the version-comparison job — keyed, highlighted, and shareable — without the Power Query assembly.

04Compare two CSV files in Excel

Comparing two CSV files is the same job with plainer inputs. Open both in Excel (or import them), and the same rules apply: if the rows still align, conditional formatting with =A2<>OtherSheet!A2 highlights the changed cells; if rows were added or removed, compare by a key instead. Because CSVs carry no cell formatting of their own, there's nothing to compare except the values — which makes them a clean input for a keyed comparison. Mind the output format, though: a CSV can hold a plain differences table, but it can't carry the color highlighting or the multi-sheet workbook, so export those as XLSX.

05"For differences" vs "for duplicates"

Two nearby jobs get tangled with this one, and they need different tools. Comparing two files for differences — what this page covers — is about what changed between two versions. Comparing two lists for duplicates usually means one of two other things: finding the values that appear in both lists (that's comparing two lists in Excel, a membership check), or removing repeated rows inside a single file (that's deduplicating one list with Remove Duplicates). If your real question is "which items are in both lists" or "how do I strip duplicate rows from one sheet," those are separate methods, not a version comparison. And combining or appending several files into one master sheet is a different job again — that is merging files, not comparing two versions of one.

06What Excel's Spreadsheet Compare add-in does — and doesn't

Microsoft ships a dedicated comparison tool. Through the Inquire add-in, the Spreadsheet Compare command runs a full cell-by-cell comparison of two workbooks and lays the differences out in a two-pane grid, color-coded by the kind of change — entered values, calculated values, formulas, named ranges, cell formats, even line-by-line VBA code. For an audit of what changed between two versions, it's thorough and it's free if you already have it.

The catch is who has it. Spreadsheet Compare and the Inquire add-in ship only with Office Professional Plus and Microsoft 365 Apps for enterprise, on Windows — they aren't in the Home, Personal, or Business editions, and not on Mac. When you launch Compare Files from Excel's Inquire tab you need both workbooks open (the standalone Spreadsheet Compare app lets you browse to the two files instead), and like conditional formatting it aligns cells by position, so the same row-movement caveat applies. If you have the right edition, it's an excellent audit tool; if you don't, the keyed methods above are the portable answer.

What about Copilot? Copilot in Excel can import data from another workbook, which helps you pull both versions together — but importing is not comparing. Microsoft's documented workflow brings the data in; you still apply one of the methods above (conditional formatting when the rows align, a keyed comparison when they move) to get the before/after diff. Microsoft's documented import workflow does not itself produce the complete keyed, highlighted before/after diff shown here.

Which method fits the job

What you need	Best method
A quick visual scan of two files	View Side by Side (View tab)
Highlight changed cells, rows still aligned	Conditional formatting, both sheets in one file
Compare when rows were added, removed, or reordered	A keyed comparison — Power Query, or Anomaly
A full cell-by-cell audit (formulas, formats, VBA)	Spreadsheet Compare (Windows, Professional Plus / Microsoft 365 Apps for enterprise)
Added / removed / changed rows as a downloadable, highlighted file	Anomaly

FAQFrequently asked questions

—The bottom line

To compare two Excel files, first ask whether the rows can be trusted to line up. If they can, conditional formatting highlights the changed cells, and Spreadsheet Compare gives a full audit if your edition has it. If they can't — if rows were added, removed, or reordered — stop comparing by position and match the two versions on a key instead, so a moved row reads as moved rather than changed. That keyed comparison, returned as a highlighted workbook you can actually send, is the version-comparison job done properly.

Compare two files and download the highlighted differences

✕