How to Compare Two Excel Files: Five Methods, and What Each One Misses
There is no single compare command, because “compare” means at least four different questions. Decide which one you are asking first — then the method picks itself.
Short answer
First, decide what you are actually asking
People say “compare these two files” and mean quite different things. The method you choose depends entirely on which of these four questions you need answered.
| The question | What you need back | Best method |
|---|---|---|
| Are these files identical? | Yes or no | A hash or a total-row check, not a cell-by-cell scan |
| What changed between the two versions? | A list of changed cells | Formula comparison, or a diff tool if available |
| Which rows are missing from one side? | A list of missing keys | COUNTIF or XLOOKUP on a key column |
| Which rows exist on both sides but disagree? | A list of keys with the differing value | A merge on the key, then compare the value columns |
Most “comparison” requests are questions three and four
The key decision, before any method
If you take one thing from this article, take this: a comparison without a unique key is unreliable.
| Bad key | Why it fails | Better key |
|---|---|---|
| Row number | One extra row on one side shifts every subsequent row and reports false differences | |
| Amount alone | Two different invoices with the same value match to each other | |
| Date alone | Hundreds of records share a date | |
| Name | Spelling differs between exports — “Shree Balaji Enterprises” and “SHREE BALAJI ENT.” | |
| Invoice number plus party code | Combines uniqueness with a check on the party — this is usually right |
If no single column is unique, build a key by joining two or three columns. If the key itself may be written differently on the two sides, normalise it — strip spaces, force upper case, remove slashes — using the identical formula on both sheets.
Method 1: Formulas — the one that always works
This is the workhorse, and for most accounting comparisons it is the correct answer. Both files go into one workbook as two sheets, and you build the comparison in a third sheet or in helper columns.
| What you want to know | Formula | Reads as |
|---|---|---|
| Does this key exist on the other side? | =COUNTIF(Other!$A:$A, $A2) | 0 means missing, 1 or more means present |
| How many times does it exist? | =COUNTIF(Other!$A:$A, $A2) | More than 1 is a duplicate — the case a plain “exists” test hides |
| What is the value on the other side? | =XLOOKUP($A2, Other!$A:$A, Other!$D:$D, "Not found") | The value, or a clear marker when the key is absent |
| Do the values agree? | =IF(ABS($D2-F2)<=1, "Match", "Difference") | An absolute comparison with a tolerance of one rupee |
- Advantages: works in every version, works across sheets, the result is a value you can filter and sort, and the logic is visible to whoever reviews it later.
- Limitations: it does not survive an inserted column, it slows down when repeated down tens of thousands of rows, and it says nothing about formatting differences.
Method 2: Conditional formatting — useful, with one hard limit
Conditional formatting is good at making a difference list visible. It cannot, however, compare two files.
A formatting rule cannot reference another workbook
Once both sets of data are in one workbook, the practical approach is:
- 1Add a helper column with a COUNTIF or an equality test that returns TRUE or FALSE.
- 2Apply a rule that highlights TRUE — or better, highlight the two different conditions in two different colours, so “missing” and “different” do not look the same.
- 3Filter by that column, and you have your difference list.
The details, including the duplicate-highlighting rules that are surprisingly awkward to get right, are covered in highlighting differences with conditional formatting.
Method 3: Power Query — the right answer for large or repeated comparisons
Power Query loads both files, holds them in memory, and merges them on the key. A merge with a “left anti” join returns only the rows from the first table that do not exist in the second — which is exactly the missing-records question.
Load both files as queries
Confirm the key column type on both sides
Merge with a left anti join
Reverse the merge for the other side
Merge as an inner join to find changed values
Power Query is covered in more depth in Power Query in Excel. Its real advantage is not speed alone — it is that next month you replace the two source files and press Refresh, instead of rebuilding the comparison.
Method 4: Spreadsheet Compare — if your edition has it
Microsoft ships a program called Spreadsheet Compare with certain Office editions. Where it is available, it takes two workbooks and produces a report showing cells that changed in value, cells that changed in formula, and formatting differences.
- Good for: reviewing what changed between two versions of a model or a report template, where the structure is the same on both sides.
- Not good for: reconciling two lists that should match but do not, because it compares by position. Insert one row at the top of the newer file and every row below it is reported as changed.
- Availability: it depends on the Office edition and on the installation, so it is not something you can assume a colleague has. Because of that, it is rarely the method to build a monthly process around.
Method 5: A purpose-built comparison tool
A tool that reads two files and returns a difference list does the same job as the formula method, without you maintaining the formulas. What matters is whether it matches on a key rather than on position, reports missing records separately for each side, distinguishes an amount difference from a missing record, and records the reason for each result.
If it does not do those four things, it is a viewer, not a comparison.
Choosing between them
| Situation | Use | Why |
|---|---|---|
| A few hundred rows, done occasionally | Formulas | Fastest to set up, nothing to maintain, easy to explain to a reviewer |
| Same comparison every month | Power Query | Set it up once, then swap the source files and refresh |
| Tens of thousands of rows | Power Query, or a dedicated tool | Formulas repeated down the sheet become the slowest part of the task |
| You need to see what changed in a report template | Spreadsheet Compare, if available | It is designed for exactly that |
| You need a documented difference list with reasons | A dedicated tool | The reason has to be recorded per record, not inferred from a formula |
Common mistakes
- Comparing by position. The single most common error. Any inserted or deleted row makes the comparison meaningless from that point down.
- Sorting both sides and assuming that is enough. It helps, but a single extra row still shifts everything after it.
- Treating text and numbers as the same. A value stored as text does not equal the same digits stored as a number. Watch for exports where invoice numbers or amounts arrive as text.
- Ignoring duplicates. If a key appears twice on one side, a COUNTIF returns 2, and a lookup returns only the first. Both facts matter and both are easy to miss.
- Expecting formatting to be compared. None of the formula methods say anything about colour, bold or number format. If formatting differences matter, they must be checked separately.
- Not freezing the source files. If the file you are comparing against is being edited while you work, you are comparing against a moving target. Copy the file, note the date, and compare against the copy.
When to stop doing this by hand
The manual method is genuinely fine for the occasional comparison. It becomes a problem when the comparison runs monthly, when both files arrive in formats that change without warning, and when the output has to be evidence rather than a working sheet — somebody has to be able to see what was compared, on what key, and why a record was judged different.
That is the case for a tool that keeps the rules rather than the formulas. The Piloteq Automate comparison page shows the output format — records only in file one, only in file two, and present on both sides with different values, each with a reason.
If you would rather not build this by hand
Get the difference list without building the comparison
Piloteq Automate reads both files, matches on the key columns you choose, and returns the records that are only in the first file, only in the second, and in both with different values — each with the reason recorded.
- ✓Exact, tolerance and last-digit matching on key columns
- ✓Missing records reported separately for each side
- ✓Duplicate detection before the comparison runs
- ✓Difference analysis showing the changed value per record
Frequently asked questions
What is the easiest way to compare two Excel files?+
Copy both onto separate sheets of the same workbook and use a formula driven by COUNTIF or XLOOKUP. It takes a few minutes and it answers the question that matters most — which rows exist on one side and not the other. Everything else, including the visual tools, is a variation on that same comparison.
Can I use conditional formatting to compare two Excel files?+
No, and this catches a lot of people. A conditional formatting rule cannot reference another workbook. If your two sets of data are in separate files, highlighting differences between them is not possible with formatting alone — you must either copy the data into one workbook or write a formula that returns a flag. Formatting can then highlight the flag column, which is a different thing from comparing the files directly.
Does Excel have a built-in file comparison tool?+
Spreadsheet Compare is a separate program that ships with certain Microsoft Office editions rather than with every Excel installation, so many users do not have it. When it is available it produces a detailed report of cell-level differences, values, formulas and formatting between two workbooks. Because availability varies by edition and by installation, the formula-based approach remains the method that always works.
How do I compare two Excel files when the row order is different?+
Never compare by position. Sort both sides is not enough either, because a single extra row on one side shifts everything after it. Match on a unique key — an invoice number, an employee code, a ledger account — and compare the values belonging to that key. If there is no unique column, build one by joining two or three columns together.
How do I compare files when the sheet names or column order differ?+
Map the columns first, in a small mapping table, and compare by column name rather than by position. Two exports from the same system in different months can easily have their columns reordered, and a positional comparison will then report every cell as different. Comparing by mapped column name survives that.
What is the fastest way to compare two large Excel files?+
Convert both to tables, load them into Power Query and merge on the key column with a left anti join for each side. A query holds the data in memory and handles far more rows than formulas repeated down a sheet. The merge result gives you the missing rows on each side, and a subsequent merge on matching keys with a value comparison gives you the changed rows.
Related guides
Compare Two Sheets in the Same Workbook
The same comparison when both sets of data live in one file.
Finding Missing Values Between Two Lists
COUNTIF, XLOOKUP and MATCH used for the missing-rows question.
Comparing Large Excel Files Without Freezing
What to do when the files are too big for formulas down the sheet.