Stop Using Copy-Paste — Compare Two Excel Spreadsheets in 4 Steps

The first thing most people do when asked to compare two Excel spreadsheets is open both side-by-side and start scrolling. They highlight cells, squint at numbers, maybe even print them out. That’s not just slow — it’s guaranteed to miss changes in row order, hidden formatting shifts, or identical-looking values that differ by a space or trailing zero.

The Problem

You get two versions of a supplier pricing sheet: Suppliers_Q3_FINAL.xlsx and Suppliers_Q3_APPROVED.xlsx. Marketing says "just check what changed." You open both. Column A is Supplier Name, B is Item Code, C is Unit Price, D is Effective Date. But the rows aren’t sorted the same way. One file has 127 rows; the other has 129. There’s no version control. No audit trail. Just two static sheets with no markers.

Supplier	Item Code	Unit Price	Effective Date
Acme Corp	ITEM-7821	$45,200.00	2024-03-15
Zenith Labs	ZEN-994B	$12,850.50	2024-03-18
NovaTech Inc	NT-330X	$8,999.99	2024-03-20
BrightSource Ltd	BS-117K	$21,400.00	2024-03-22
Orion Dynamics	OD-881M	$33,650.25	2024-03-25
Skyline Group	SKY-442P	$17,300.00	2024-03-26

This is the raw state. No color coding. No flags. And if you try to eyeball differences between this and another sheet where Zenith Labs moved from row 2 to row 5, and $12,850.50 became $12,850.500 (which Excel treats as equal but isn’t always), you’ll miss it every time.

The Solution

Do this — not in order, but all of it:

1. Copy both sheets into one workbook. Open Suppliers_Q3_FINAL.xlsx, press Ctrl+A, then Ctrl+C. Open Suppliers_Q3_APPROVED.xlsx, create a new sheet named FINAL, paste into A1. Repeat for the other file into a sheet named APPROVED.
2. Add a unique key column in both sheets. In FINAL!E1, type Key. In E2, enter =A2&"|"&B2. Drag down to E129. Do the same in APPROVED!E2. This creates keys like Acme Corp|ITEM-7821. Why |? Because A2&B2 could accidentally match different combos — AB+C vs A+BC. The pipe prevents false matches.
3. In a third sheet (DIFF), pull data with XLOOKUP + IF. In DIFF!A1, type Key. In A2, enter =FINAL!E2 and drag down. In B2, use:
   =IF(ISNA(XLOOKUP(A2,APPROVED!E:E,APPROVED!C:C)),"MISSING",IF(XLOOKUP(A2,APPROVED!E:E,APPROVED!C:C)<>FINAL!C2,"CHANGED","OK"))
   This checks price only — but you can extend it to D2 (date), or nest more conditions.
4. Filter and review. Select A1:B129 in DIFF, press Ctrl+Shift+L to apply AutoFilter. Click the dropdown in column B and uncheck OK. Now only MISSING and CHANGED remain — 11 rows instead of 129.

   Step	Action	Result	Shortcut
   1	Paste both sheets into one workbook	Two clean sheets: FINAL and APPROVED	Ctrl+V
   2	Add =A2&"|"&B2 in column E	Unique composite key for each row	Ctrl+D
   3	In DIFF sheet, use XLOOKUP + IF to flag mismatches	Clear status per row: OK / CHANGED / MISSING	F2 → Enter
   4	Apply filter and hide "OK" rows	Only actionable differences visible	Ctrl+Shift+L

   Going Further

   You don’t need Power Query for this — but if you do have it, load both tables, merge them with a Full Outer Join on the Key column, then add a custom column: =if [FINAL.Price] = [APPROVED.Price] then "OK" else "CHANGED". It’s faster for >10k rows.

   For date-only comparisons, wrap dates in TEXT(C2,"yyyy-mm-dd") before concatenating — avoids time-stamp mismatches.

   Want to see *what* changed, not just *that* it changed? Replace the simple IF with:
   =LET(f, XLOOKUP(A2,APPROVED!E:E,APPROVED!C:C), IF(ISNA(f),"MISSING", IF(f<>FINAL!C2, "OLD:"&FINAL!C2&" → NEW:"&f, "OK")))

   And here’s the counterintuitive tip: Never compare entire columns with =A:A=B:B. Excel will return #N/A for blank rows and silently ignore mismatches beyond row 1,048,576. Always anchor ranges: =A2:B129=A2:B129 (array-entered with Ctrl+Shift+Enter in older Excel) or better — use structured references if using Tables.

   When NOT to Use This

   If either spreadsheet contains merged cells — stop. Merged cells break XLOOKUP, break filters, break sorting. Unmerge everything first. Run Find & Select → Go To Special → Merged Cells (Alt+H+FJ+M), then unmerge.

   If the files use different number formats (e.g., one shows $12,850.50, the other stores 12850.5 without formatting), your comparison will fail. Pre-format both columns as Number with 2 decimals *before* building the key.

   Don’t use this method if row order matters *and* rows were inserted/deleted mid-table — because your key assumes the same logical record exists in both. In that case, use conditional formatting across sheets: select FINAL!C2:C129, go to Home → Conditional Formatting → New Rule → Use a formula, enter =C2<>INDIRECT("'APPROVED'!C"&ROW()), then set red fill. This compares row-for-row, not record-for-record.

   Keyboard Shortcuts

   Shortcut	What It Does	Notes
   Alt+H+FJ+M	Select all merged cells	Critical pre-check
   Ctrl+Shift+L	Toggle AutoFilter	Use after building DIFF sheet
   Ctrl+Shift+Down	Select all contiguous cells below	Fast fill-down for formulas
   F2 → Ctrl+Enter	Edit cell & fill formula across selection	Better than dragging for large ranges