How to find added, removed and changed rows between two exports

To compare an old and a new export, match the rows by their ID, not by their position. Then sort every difference into one of three groups: added (the ID is only in the new file), removed (only in the old file) and changed (in both, but at least one value differs). For a wide export, the most useful form is one long list with a line per changed cell: ID, column, old value, new value.

Finding added and removed rows is the easy part, and our guide on comparing two Excel sheets does it with an anti join. But an anti join only finds IDs that are missing. As one Excel user put it on Reddit: "anti join wouldn't detect a change of a single value in the cell, right?" Right. This guide gets the changed cells too, in Power Query and in SQL, without writing a formula for each of your columns.

The result you want

The example is a customer export from a CRM, taken a month apart. The old file has five customers, the new one has five, and they are sorted differently:

In the new export, Borealis got a higher limit, Delta Foods is no longer on hold, Evergreen finally has a credit limit, Cobalt is gone and Fjord AS is new. The change list should say exactly that and nothing else:

Acme did not change, so it is not in the list. Both methods below produce this list (with one line per cell for the added and removed customers), and it works the same with 5 columns or 100.

1. Power Query: the change list without formulas

Power Query is built into Excel 2016 and later on Windows (Data → Get Data). The trick is to unpivot both tables first: instead of one row per customer with many columns, you get one row per cell, with three columns: ID, Column and Value. Then a single merge on ID and column compares every cell, however many columns there are.

1. Load each export: Data → From Text/CSV (or From Table/Range for a sheet), then Transform Data. Name the queries Old and New. Make sure the ID column is called ID in both.
2. Choose Home → New Source → Other Sources → Blank Query, open Advanced Editor and paste the query below.
3. Close & Load. You get a table with Key, Status, Field, Old value and New value. Next month, replace the files and click Refresh.

let
    // One row per cell: ID, Column, Value. Every value as text,
    // empty cells as "" so that unpivot does not skip them.
    Long = (t as table) as table =>
        let
            cols   = Table.ColumnNames(t),
            asText = Table.TransformColumnTypes(t, List.Transform(cols, each {_, type text})),
            filled = Table.ReplaceValue(asText, null, "", Replacer.ReplaceValue, cols)
        in
            Table.UnpivotOtherColumns(filled, {"ID"}, "Column", "Value"),
    OldLong  = Long(Old),
    NewLong  = Long(New),
    Joined   = Table.NestedJoin(OldLong, {"ID", "Column"}, NewLong, {"ID", "Column"}, "N", JoinKind.FullOuter),
    Expanded = Table.ExpandTableColumn(Joined, "N", {"ID", "Column", "Value"}, {"N.ID", "N.Column", "New value"}),
    Diff     = Table.SelectRows(Expanded, each [Value] <> [New value]),
    Status   = Table.AddColumn(Diff, "Status", each
                   if [ID] = null then "added" else if [N.ID] = null then "removed" else "changed"),
    Key      = Table.AddColumn(Status, "Key", each if [ID] = null then [N.ID] else [ID]),
    Field    = Table.AddColumn(Key, "Field", each if [Column] = null then [N.Column] else [Column]),
    Result   = Table.RenameColumns(
                   Table.SelectColumns(Field, {"Key", "Status", "Field", "Value", "New value"}),
                   {{"Value", "Old value"}})
in
    Result

What each step does, if you prefer clicking:

• Long is the same as selecting the ID column and choosing Transform → Unpivot Other Columns, after setting every column to Text and replacing null with an empty value (Transform → Replace Values, type null as the value to find).
• Joined is Home → Merge Queries with both ID and Column selected (hold Ctrl) and Full Outer as the join kind. A full outer join keeps rows from both sides, matched or not, so added and removed IDs stay in the result.
• Diff keeps only the pairs whose values differ. For an added row the old value is null, for a removed row the new value is, so they pass the filter too.

Why the empty-cell step matters: unpivot leaves out empty cells. Without the replacement, Evergreen's credit limit (empty in the old file, 2500 in the new one) would have no old row to match, and it would be reported as an added cell of an existing customer. With it, it is a normal change from empty to 2500.

Why everything is converted to text: after unpivoting, one Value column holds numbers, dates and text together. Converting each column first means both files are compared the same way. If Power Query detected 1.50 as a number, it becomes 1.5 on both sides; if you want 1.50 and 1.5 to count as different, delete the automatic Changed Type step from the Old and New queries.

2. SQL with DuckDB: for exports too big for Excel

An Excel sheet stops at 1,048,576 rows, and unpivoting multiplies the row count: a 50,000-row export with 30 columns becomes 1.5 million rows, one per cell. If your exports are CSV files, DuckDB does the same thing in SQL. It is a single program with no server to set up, and it reads CSV files directly. Run this in the folder with old.csv and new.csv (the ID column is id):

-- every value as text, empty cells as '' so they are compared too
CREATE TABLE old_rows AS
  SELECT coalesce(COLUMNS(*), '') FROM read_csv('old.csv', all_varchar = true);
CREATE TABLE new_rows AS
  SELECT coalesce(COLUMNS(*), '') FROM read_csv('new.csv', all_varchar = true);

CREATE TABLE changes AS
WITH old AS (UNPIVOT old_rows ON COLUMNS(* EXCLUDE (id)) INTO NAME col VALUE val),
     new AS (UNPIVOT new_rows ON COLUMNS(* EXCLUDE (id)) INTO NAME col VALUE val)
SELECT
  coalesce(o.id, n.id)   AS id,
  CASE WHEN o.id IS NULL THEN 'added'
       WHEN n.id IS NULL THEN 'removed'
       ELSE 'changed' END AS status,
  coalesce(o.col, n.col) AS "column",
  o.val                  AS old_value,
  n.val                  AS new_value
FROM old o
FULL OUTER JOIN new n ON o.id = n.id AND o.col = n.col
WHERE o.val IS DISTINCT FROM n.val;

COPY changes TO 'changes.csv';

On the example files this returns exactly the list above: 3 changed cells, plus one line per column for Cobalt (removed) and Fjord AS (added). A short summary by row:

SELECT status, count(DISTINCT id) AS "rows" FROM changes GROUP BY status;
-- added 1, changed 3, removed 1

The same notes apply as in Power Query. all_varchar = true reads every value as text, so 00123 keeps its leading zeros and 1.50 stays 1.50. And the coalesce step matters here too: DuckDB's UNPIVOT leaves out empty (NULL) values by default. We tested it: without the step, Evergreen's new credit limit has nothing to match on the old side.

IS DISTINCT FROM is used instead of <> because <> returns NULL, not true, when one side is missing, and the added and removed rows would silently disappear.

3. Three checks before you send the result

A change list is only useful if people can trust it. These three checks catch most wrong results:

• Every ID appears once. If an ID is in a file twice, the join pairs each copy with each copy on the other side, and you get changes that are not real. Check each file first:

  SELECT id, count(*) FROM read_csv('new.csv', all_varchar = true) GROUP BY id HAVING count(*) > 1;

  In Power Query: Home → Group By on ID with a Count Rows column, then filter for counts above 1.
• The counts add up. Rows in the old file = unchanged + changed + removed. Rows in the new file = unchanged + changed + added. In the example: 5 = 1 + 3 + 1 and 5 = 1 + 3 + 1. If the numbers do not add up, something was lost or counted twice.
• Both files have the same columns. If a column was added, renamed or removed between the exports, every value in it shows up as a change, and the status of those lines says "added" or "removed" even though the customer exists in both files. Rename or remove such columns before comparing.

Also look at the IDs themselves before you start. If one system writes 00123 and the other 123, or one adds a trailing space, every row will look removed and added at the same time. Our guide on false differences in CSV files lists the usual causes.

4. Without Power Query or SQL

If you do this every month, or you cannot install anything on a work computer, Calamo's free compare tool runs the same comparison in the browser:

1. Save both exports as CSV (in Excel: File → Save As → CSV UTF-8).
2. Drop the old export as file A and the new one as file B. Calamo suggests an ID column and warns if its values do not look alike in both files (for example 00123 and 123).
3. Read the counts, then open the changed rows: every changed cell is shown next to its old value. "Most changed columns" shows which columns changed most, and clicking one filters the list.
4. Download calamo-diff-report.csv with every difference, or the added, removed and changed rows separately.

It does the checks from section 3 for you: repeated IDs are listed on their own tab instead of being paired at random, and the summary row shows that the counts add up. Nothing needs to be installed, and the files are read in your browser and never uploaded.

Only need the values that are in one column but not the other? See how to compare two lists in Excel.

Questions

Michal PijákData engineer and the maker of Calamo. These guides come from comparing exports at work: every formula and command is checked against the linked documentation, and the SQL in this guide was run on the example files. Found a mistake? hello@calamo.io