How to compare two spreadsheets
It depends what "compare" means for your files. To see what changed between two versions, compare them directly. To bring columns across from one to the other, match them on a shared column. To check two systems agree on money, reconcile them.
Free, no sign-up, and your file is read on your own device rather than uploaded.
People ask how to compare two spreadsheets when they mean three genuinely different jobs, and picking the wrong one wastes an afternoon. It is worth being clear which you have before you start.
If the two files are versions of the same thing and you want to know what changed, that is a comparison. If they are different things sharing a key - orders and customers, say - and you want columns from one added to the other, that is a lookup. If they are two records of the same money and you want to know where they disagree, that is a reconciliation.
- Work out which job you have
- See what changed between two versions
- Bring columns across from another sheet
- Match when the keys do not quite agree
- Check two systems agree on the money
The steps
Work out which job you have
This one is a decision, not a tool. Two versions of one file, and you want the differences? Compare. Two different files sharing an ID, and you want to join them? Look up. Two records of the same transactions, and you want to know where the totals part company? Reconcile. The table below is the short version.
See what changed between two versions
Add both files and pick the column that identifies a row. You get added rows, removed rows and, for rows present in both, exactly which cells differ.
Compare SpreadsheetsBring columns across from another sheet
This is what VLOOKUP is for, without the formula. Choose the column both files share and the columns you want copied over. Rows that find no match are reported rather than silently left blank.
VLOOKUP OnlineMatch when the keys do not quite agree· only if you need it
If the shared column is a name rather than an ID, exact matching will fail on spacing, punctuation and spelling. Set how similar counts as a match, see the confidence for every row, and review the borderline ones.
Match Similar NamesCheck two systems agree on the money· only if you need it
Match on a reference and an amount, set the tolerance you can live with, and get both totals, the gap between them, and every difference beyond that tolerance.
Check Two Systems Agree
Which of the four you need
| What you have | What to use |
|---|---|
| Two versions of the same file, and you want to know what changed | Compare spreadsheets |
| Two different files sharing an ID, and you want columns from one added to the other | VLOOKUP |
| The same, but the shared column is a name rather than an ID | Fuzzy Lookup |
| Two records of the same money, and you want to know where they disagree | Data Reconciliation |
| One file, and you want to find rows that repeat inside it | Duplicate Finder |
A worked example
You have an export from your accounting system with 4,000 payments, and a bank statement with 4,010 lines. The totals differ by £312.40 and you need to know why.
That is a reconciliation, not a comparison: the two files are different systems recording the same money, they do not share a row-for-row structure, and you care about the gap rather than about which cells differ. Match on the payment reference and the amount, set a tolerance of a penny or two for rounding, and you get both totals, the difference, and the specific lines that are in one file and not the other.
What this cannot do
Worth knowing before you start, rather than halfway through.
- Every one of these needs two files, so the spreadsheet you have open fills the first slot and you supply the second.
- Matching needs a column the two files share. If nothing links them, no tool can invent the relationship.
- Fuzzy matching reports a confidence, not a certainty. Anything close to your threshold is worth reviewing by hand, which is why the borderline rows are shown separately.
Common questions
- Can I compare two sheets in the same workbook?
- Yes. Add the same file on both sides and choose a different sheet on each.
- What is VLOOKUP actually for?
- Taking a value you have in one sheet - an order ID, an email, a product code - and pulling in the matching row from another sheet. It is a join, and it is the most common thing people use spreadsheets for that spreadsheets make hard.
- What is fuzzy lookup?
- The same job when the shared column is not reliable. It matches on similarity rather than on being character-for-character identical, which is what you need when the key is a company or a person's name typed by two different people.
- Are my files uploaded?
- No. Both files are read in your browser and neither leaves your device. That matters here more than usual, because comparing two files usually means two versions of something confidential.
- Should I clean the files before comparing them?
- Usually, yes. Stray spaces and inconsistent capitalisation make identical rows look different, so a comparison run on messy files reports differences that are not real ones.
The tools this uses
- Compare SpreadsheetsSee what changed between two versions of the same data.
- VLOOKUP OnlineBring columns from one sheet into another, without writing a formula.
- Match Similar NamesMatch records whose names never quite agree between two systems.
- Check Two Systems AgreeReconcile two systems and see exactly where they disagree.
Continue in the workspace
Your file stays open there - pick the next thing to do to it, without a second upload.