← GCH Services · More free cleanup tips · Clean a messy CSV
Disclosure: this article was drafted with AI assistance (Claude) and the steps were reviewed before publishing. I sell a paid cheat sheet linked at the end.
"Merge two Excel files" usually means one of three different jobs. Pick the right one before you touch anything — using the wrong method is how rows quietly go missing.
1. Same columns, just stacking rows (e.g. monthly exports). Easiest case. Open both files, copy the data rows (not the header) from file B, paste them directly under the last row of file A. In Excel 365 / Google Sheets you can also reference the whole other file with =A2:F200 style formulas, but a plain copy-paste-values is safer for one-off merges because it won't break if the source file moves.
2. Same columns, but you're not sure they line up (different column order, extra columns). Don't paste blind. First make both headers match exactly (rename or reorder columns), or use Excel's Data > Get Data > From File > From Workbook (Power Query) and pick "Append Queries" — it matches by column name, not position, so a shuffled column order still lands correctly.
3. Different files, matching by a shared key (e.g. combine a customer list with an order list by Customer ID). This is a lookup, not a stack. In the destination sheet: =XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B) pulls the matching value from the other file/tab for each key in column A. Older Excel or Sheets without XLOOKUP: =VLOOKUP(A2, Sheet2!A:B, 2, FALSE). Google Sheets pulling from a genuinely separate spreadsheet file needs =IMPORTRANGE("sheet-url","Sheet2!A:B") first (accept the connection permission once), then VLOOKUP/XLOOKUP against that imported range.
Before merging by key, always:
=TRIM(CLEAN(A2))) — a trailing space makes "C-102" and "C-102 " fail to match silently.=COUNTIF(A:A,A2)>1) before you trust a one-to-one lookup.=COUNTIF(Sheet2!A:A,A2) next to your merged data. A 0 means that row had no match in the other file — don't skip this, it's the #1 way merges silently lose data.Power Query for recurring merges. If you merge the same two file shapes every week/month, Power Query (Data tab > Get Data) saves the steps as a query you can just "Refresh" next time instead of redoing the lookups by hand. Worth the 10-minute setup once you're merging more than twice.
Workflow tip: merge into a brand-new third file or a new tab, never overwrite either source file directly. If a formula-based merge (VLOOKUP/XLOOKUP) looks right, copy the result range and Paste Special > Values so the final file doesn't break if you later close or move the source workbook.
If you'd rather not rebuild these formulas from scratch, I put the cleanup and lookup formulas, a duplicate/mismatch checklist and common gotchas on one page: the "CSV & Excel Data Cleanup Cheat Sheet" ($7). GCH Services is the seller (we made it and we get the money).
Sources: reader questions seen repeatedly on r/excel and r/googlesheets ("how to merge 2 excel files into one excel file", "merge spreadsheets"); formulas are standard Excel/Sheets functions. Test on a copy of your data first.