How to compare two files manually in Excel
Excel has no single button that answers “what changed between these two versions?” for everyone, but you can get most of the way with a few techniques.
Look at them side by side
Open both workbooks, then on the View tab, in the Window group, click View Side by Side. Turn on Synchronous Scrolling in the same group so both windows move together. To compare two sheets of one workbook, first click View › New Window so the workbook opens twice, then pick a different sheet in each window. This is fine for twenty rows. For two hundred, your eyes will miss things.
A difference sheet with a formula
Add a third sheet and put this in cell A1:
=IF(Sheet1!A1<>Sheet2!A1,"Old: "&Sheet1!A1&" New: "&Sheet2!A1,"")
Fill it across and down to the size of your data. Every cell that differs shows its old and new value; the rest stay blank. The catch is that it compares cell A5 with cell A5. If one row was inserted or deleted, or the second file is sorted differently, every row below that point shows up as changed even though nothing really did.
Conditional formatting
To colour differences in place, select the data on the first sheet, choose Home › Conditional Formatting › New Rule › Use a formula to determine which cells to format, and enter something like =A1<>Sheet2!A1. It has the same weakness: it only works when the rows line up exactly.
Lookup formulas for added and removed rows
To find IDs that exist in one file but not the other, add a helper column next to the first list with =ISNA(XLOOKUP(A2,Sheet2!A:A,Sheet2!A:A)) in Excel for Microsoft 365 or 2021, or =ISNA(VLOOKUP(A2,Sheet2!A:A,1,FALSE)) in older versions. TRUE means the ID was removed. Repeat in the other direction to find added rows. You then need more lookups, one per column, to spot edited values.
Spreadsheet Compare
Some Windows editions of Office include a separate Spreadsheet Compare app and an Inquire add-in that can be switched on under File › Options › Add-ins › COM Add-ins. They are only available in Microsoft 365 Apps for enterprise and Office Professional Plus, so many home and small-business users will not have them.
When this tool helps
Supplier price lists. A vendor sends this month’s list and you need to know which prices went up before you update your own. Matching on SKU shows exactly which products changed price, which were dropped and which are new, even if the supplier re-sorted the list.
Stock reports. Compare yesterday’s stock export with today’s to see which items moved, without scanning every line.
A colleague’s edited copy. You shared a sheet and got it back “with a few fixes”. The Cell changes sheet tells you precisely which cells they touched and what the values were before.
Bank and vendor master data. Payment details are a common target for fraud and simple mistakes. Comparing the current vendor list with last quarter’s copy flags any changed account number for a second look.
Before and after a cleanup. After trimming spaces or fixing dates, compare the cleaned file with the original to confirm only the intended cells changed.
Audit trails. Keep the comparison workbook alongside the two versions as a dated record of what was edited and when.
What the report contains
The download is a workbook named after your original file, for example price-list-v1-comparison.xlsx, with four sheets:
- Changed lists each changed row as it appears in the changed file, with an extra Changed columns column naming which fields differ.
- Added holds rows found only in the changed file.
- Removed holds rows found only in the original file.
- Cell changes has one line per edited cell: the key, the column, the original value and the new value. Filter it by column to see, say, every price change at once.
With the sample price lists, the summary reads: “Compared by SKU: 1 row added, 1 removed and 2 changed (2 cells). 1 row is the same.” One price went from 240 to 260, one stock count dropped from 80 to 64, SKU-003 was removed and SKU-005 was added.
Getting a clean comparison
Pick a key that truly identifies each row, such as an order number, SKU or employee ID. Names make poor keys because two people can share one and spellings drift. If the summary warns about repeated keys, remove duplicates from both files first. And if one file stores numbers as text, you do not need to fix that beforehand: the comparison already treats 1250 and “1250” as equal, so you only see real changes.