How to compare two Excel files by key column on Mac (2026)
Excel for Mac has no built-in "compare files" command: Microsoft's Spreadsheet Compare is "available only in Excel for Windows". On a Mac you match the two versions on a key column (ID, SKU, email) with XLOOKUP, with a Power Query merge, or with a browser tool that does the matching and lists added, removed and changed rows. Comparing by position (row 5 against row 5) only works if neither file was sorted, filtered or edited row by row.
Why the key column matters
A position-based comparison reports a difference on every row below the first inserted or deleted line. A key-based comparison looks up each row of the old file in the new one by its identifier, wherever it is, and then compares the other columns cell by cell.
| Situation | Compare by position | Compare by key column |
|---|---|---|
| One row inserted at the top | every row reported as changed | 1 row added |
| File sorted differently | most rows reported as changed | 0 changes |
| Price changed on 3 items | 3 cells, if nothing else moved | 3 cells |
| Column renamed ("Price" → "Unit price") | column treated as new | column matched by you or by name |
| Same ID twice in one file | not detected | reported as a duplicate key |
Method 1: XLOOKUP formulas (Excel for Mac 2021, 2024, Microsoft 365)
XLOOKUP is listed for Excel 2021 for Mac, Excel 2024 for Mac and Excel for Microsoft 365 for Mac on Microsoft's XLOOKUP page.
- Put both versions in one workbook: sheet
Oldand sheetNew, key in column A. - In
New, next to the price column (say C), add the old price:=XLOOKUP(A2, Old!A:A, Old!C:C, "NEW"). "NEW" is returned when the key is not in the old file; without that fourth argument XLOOKUP returns#N/A. - Status column:
=IF(D2="NEW","added",IF(D2<>C2,"changed","same")). - Removed rows need the reverse lookup in the
Oldsheet:=XLOOKUP(A2, New!A:A, New!A:A, "REMOVED"). - Repeat step 2 for every column you want to compare.
Limits: one helper column per compared column and per direction, 1,50 stored as text does not equal 1.5 stored as a number, trailing spaces make keys miss, and duplicate keys silently return the first match.
Method 2: Power Query merge
Power Query's "Merge queries" is listed for Excel for Microsoft 365 for Mac on Microsoft's merge queries page. Load both tables, merge on the key column, and pick the join kind: "Left anti join" gives rows only in the old file (removed), "Right anti join" rows only in the new file (added), "Inner join" the rows present in both, which you then compare column by column with custom columns. It handles large files but takes some setup for each new pair of files.
Method 3: a browser tool
The compare tool does the matching for you: drop the two files (XLSX, XLS or CSV), it proposes the key column (a column with unique values, or one named ID, SKU, ref, code or email), matches columns by name ignoring case, accents and spaces, and shows added, removed and changed rows with the old and new value of each cell and the difference in % for numbers. Duplicate keys are listed with their row numbers. It runs in Safari or Chrome, the files stay in the browser, and it is free for files up to 200 rows.
Pitfalls that create false differences
- Numbers stored as text.
12,50in a CSV from a French system and12.5in an Excel cell are the same price; a plain<>comparison says they differ. - Leading zeros dropped. Excel turns
00123into123when it opens a CSV, so SKUs or ZIP codes stop matching. Import the CSV with the column set to Text, or compare against the original file. - Trailing spaces and case.
TS-BLK-Sandts-blk-sare different keys for a formula. - Composite keys. A Shopify inventory export has one row per variant and location; the SKU alone may repeat across locations, so the key is SKU + Location.
FAQ
Can I install Spreadsheet Compare on a Mac? No. Microsoft's page on comparing two versions of a workbook states it is available only in Excel for Windows, in Microsoft 365 Apps for enterprise plans and equivalent editions.
Does Numbers on Mac compare spreadsheets? Numbers has lookup functions such as VLOOKUP but no compare command, so the formula method above applies with the same limits.
What if my two files have different column names? Map them yourself: in formulas by pointing to the right column, in a tool by choosing the matching column. A name match that ignores case, accents and spaces covers "Unit Price" vs "unit_price".
What is a composite key? Two or more columns that together identify a row, for example product handle + size, or SKU + warehouse. Use it when no single column is unique.