Key Takeaways
- Google Sheets compares two CSV files for free when you import both as tabs and match on a key column.
- FILTER with ISNA and MATCH finds added and removed rows, and XLOOKUP reveals changed values.
- A match-rate formula turns the whole comparison into a single health number.
- Fuzzy keys, many files and merge workflows are where Flookup takes over in the same sheet.
Introduction
Your CRM exports a customer list on Monday and again on Friday. Between the two, people joined, left and changed their details. Finding what changed by hand is slow and error prone.
A spreadsheet already has everything needed to answer the question. Import both exports as tabs, match them on a key column and let a few formulas list exactly which rows were added, removed or changed.
This guide builds that comparison step by step, using only built-in Google Sheets functions. There is no add-on to install and no file to upload anywhere else.
What a CSV Diff Does
Feed it two files and a key column and it reports every difference between them in four categories:
- Added: rows in file B that are not in file A.
- Removed: rows in file A that are missing from file B.
- Changed: rows present in both but with different values.
- Match rate: what share of file B matched file A by key.
A spreadsheet diff produces the same four answers. The only difference is that you build it once and can inspect every formula behind the result.
Import Both Files as Tabs
Create one spreadsheet with two tabs. Import Monday's file into a tab named A and Friday's file into a tab named B. Put the header in row 1 and the data from row 2 down.
Keep the key column in the same position in both tabs, ideally column A, and keep the column order identical. If the columns are in a different order, the changed-row comparison will compare the wrong values.
Trim the key column in both tabs before you start. A stray space turns the same identifier into two different keys, and every formula below will report a false difference.
Find Added and Removed Rows
Added rows are the keys that appear in tab B and not in tab A. Put this formula on a summary tab:
=FILTER(B!A2:A, ISNA(MATCH(B!A2:A, A!A2:A, 0)))Removed rows are the reverse. The only change is which tab supplies the keys:
=FILTER(A!A2:A, ISNA(MATCH(A!A2:A, B!A2:A, 0)))Both formulas spill a list, so leave the cells below them empty. If you want the whole row rather than the key, replace the first range with the full row range.
Find Changed Rows
Changed rows share a key but differ in another column. Add a helper column to tab B and pull the old value across with XLOOKUP, then compare the two values.
=IF(IFERROR(XLOOKUP(A2, A!A:A, A!C:C), C2) = C2, "", "changed")The IFERROR handles keys that exist only in B, which are additions rather than changes. The formula returns nothing when the values agree and the word changed when they differ, so a filter on that column leaves exactly the changed rows.
Repeat the helper column for each field you care about. Comparing every column at once is possible with an array formula, but a column per field is easier to audit and much easier to explain to a colleague.
A Worked Example
Suppose tab A holds Monday's user list.
| id | name | plan |
|---|---|---|
| 1 | Alice | Pro |
| 2 | Bob | Free |
| 3 | Carol | Team |
Tab B holds Friday's export.
| id | name | plan |
|---|---|---|
| 1 | Alice | Pro |
| 3 | Carol | Enterprise |
| 4 | Dave | Free |
With id as the key, the formulas report the following result.
| Category | Rows | Why |
|---|---|---|
| Added | id 4 (Dave) | Present in B, not in A |
| Removed | id 2 (Bob) | Present in A, not in B |
| Changed | id 3 (Carol) | Same key, plan changed from Team to Enterprise |
| Unchanged | id 1 (Alice) | Same key and same values |
An unchanged row is useful evidence, not noise. It proves that a record survived the export intact, which is exactly what an import audit needs to show.
Calculate a Match Rate
The match rate condenses the comparison into one number. Count the keys in tab B and the keys that also appear in tab A.
=COUNTA(B!A2:B1000)=SUMPRODUCT(COUNTIF(A!A2:A1000, B!A2:B1000) * (B!A2:B1000 <> ""))Divide the second result by the first and format the cell as a percentage. The bounded ranges and the blank test stop empty rows from inflating the numerator. The rate assumes each key is unique in both tabs, because a repeated key is counted once per occurrence. A high rate with a small change list means the export is trustworthy. A low rate usually points to a structural problem: a different key chosen in each file, a header row treated as data or two files that were never meant to line up.
Troubleshooting Common Issues
- Everything shows as added or removed: the key column differs between tabs or one tab treats the header as data. Check the header row and the key selection first.
- Duplicate keys: if the same key appears more than once, the comparison cannot tell which row is which. Deduplicate or add a second column to the key.
- Encoding or delimiter problems: exports saved with a different delimiter can misparse. Import the file again with the correct separator before comparing.
- Trailing whitespace: a stray space in a key makes two identical-looking rows appear different. Trim the key column in both tabs before running anything.
- Numbers stored as text: an ID imported as text will not match the same ID stored as a number. Standardise the type before comparing.
When Two Files Are Not Enough
The formulas in this guide assume the keys match exactly. Real reconciliations often break that assumption. Two systems may spell the same customer differently, an export may abbreviate a company suffix or a key may gain a country prefix overnight.
When the key itself is unreliable, a spreadsheet diff reports noise instead of change. That is the point to bring in fuzzy matching, which compares near-duplicates rather than exact strings. The Compare tool in Flookup scores similarity, and the workflow templates bundle matching and merge steps for vendor, order and user-list reconciliation.
If the files are also too large for a spreadsheet, split them into parts before the upload and diff each part in turn.
Comparing Two Files and Reconciling Many
A two-file diff is one of the most common data tasks there is, and it should take minutes rather than a spreadsheet full of manual lookups. Import both files, add the three formulas above and you have added, removed and changed rows with a match rate you can defend.
Use the sheet for exact keys and a small number of files. When the keys are fuzzy, the files keep arriving or the reconciliation spans several systems, Flookup Data Wrangler brings the same comparison into Google Sheets with fuzzy matching and a review workflow.
Frequently Asked Questions
How do I compare two CSV files in Google Sheets?
Import each file as its own tab, then match the rows on a shared key column. FILTER with MATCH lists the keys that appear in one file and not the other, and XLOOKUP reveals values that changed.
What does a key column mean in a CSV diff?
A key column is the value that identifies a row, such as an ID or email. Rows with the same key in both files are treated as the same record and compared for changes.
How do I find changed rows rather than added or removed ones?
Use XLOOKUP to pull the old value for every current key, then compare it with the new value in the same row. A formula that returns an empty string when the values agree leaves only the changed rows.
How is the match rate calculated?
Divide the number of keys in the second file that also appear in the first file by the total number of keys in the second file. SUMPRODUCT and COUNTIF give both numbers without a helper column.
Can I compare more than two CSV files?
You can add more tabs and repeat the formulas, but the sheet becomes hard to maintain. For recurring comparisons across many files, Flookup adds matching and merge workflows in the same spreadsheet.