How to Merge Two Excel Spreadsheets — Complete Guide (2026)

How to Merge Two Excel Spreadsheets — Complete Guide (2026)

Short answer: there's no single "merge" button in Excel. The right way depends on what you mean. Stacking rows? Joining on a key? Pulling one column across? Each needs a different tool.

This guide walks through five methods that actually work, plus what to skip. You'll see when Power Query beats VLOOKUP, when plain copy and paste is fine, and the one trick that only works in merge cells in excel situations (it's not what most beginners think).

Worth knowing: "merge" inside Excel's ribbon means joining cells visually. That's almost never what you want when combining two spreadsheets. For real data combining — pull rows from File A and File B into one clean dataset — you need Power Query, a lookup formula, or the new dynamic array functions. The ribbon's Merge & Center button is for layout. It's not a data tool.

Here's the thing: if both files share a common column (Customer ID, SKU, Date), you can join them. If they share the same column structure, you can stack them. If they share nothing, you're back to manual paste. Identify which scenario you're in before opening anything. Spending two minutes on this decision saves the wrong-tool spiral that costs an hour.

The honest answer: most people overcomplicate this. A 200-row merge with a shared key takes three minutes in excel merge tables Power Query. A formula version takes ten. A copy-paste-and-pray version takes thirty minutes and breaks the second the source data changes.

One more thing before the methods. Always work on a copy. Always. The first merge attempt fails about half the time — wrong join kind, mismatched headers, dates that look right but aren't. Save File A as File A-merged before touching anything. You'll redo it at least once.

Method 1 — Power Query (the Best Option for Most Cases)

Power Query is built into Excel 2016 and later. It lives under Data → Get Data. It's free. It refreshes. It handles a million rows. And once you build a query, you can refresh it every Monday morning without rebuilding anything.

Step-by-step: combine two files

Open File A. Go to Data → Get Data → From File → From Workbook. Pick File B. In the Navigator pane, select the sheet you need. Click Transform Data (not Load — Load drops it straight into a sheet without cleanup).

The Power Query Editor opens. Now do the same for File A: Home → Recent Sources or repeat the Get Data flow. You'll have two queries listed on the left.

Right-click File A's query. Pick Append Queries as New if you want stacked rows. Pick Merge Queries as New if you want a join on a key column. Excel walks you through column matching either way.

For Merge: select the key column in both tables. Choose join kind (Left Outer keeps all rows from File A, Inner keeps only matches). Click OK. Expand the new column to pull in fields from File B.

Click Close & Load. Excel drops the combined data into a new sheet as a refreshable table. Right-click the table and pick Refresh to pull updated data from the source files. Worth knowing: this is closely related to how to merge spreadsheets in excel using Power Query's Append feature.

Combining 10+ files from a folder

This is where Power Query shines. Data → Get Data → From File → From Folder. Point at the folder holding your files. Excel reads every .xlsx inside, lets you pick which sheet to grab from each, and stacks them into one table. Add a new file to the folder → click Refresh → it appears in your combined dataset. No formulas, no copy-paste.

Method 2 — VLOOKUP and XLOOKUP for Pulling Columns Across

If you only need one or two columns from File B added to File A, skip Power Query. A formula does it faster — and lives right in the cell where you can see it.

VLOOKUP (works in every Excel version)

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Real example: File A has Customer ID in column A. File B has Customer ID in column A and Email in column D. To pull emails into File A's column E:

=VLOOKUP(A2, '[FileB.xlsx]Sheet1'!$A:$D, 4, FALSE)

Breaking that down: A2 is the Customer ID to look up. '[FileB.xlsx]Sheet1'!$A:$D is the range in File B (keep File B open while building the formula or save it locally first). 4 is the column number to return (Email is the 4th column). FALSE means exact match only — never use TRUE unless you really know why.

XLOOKUP (Excel 2021 and Microsoft 365)

XLOOKUP fixes everything wrong with VLOOKUP. It searches left, right, returns multiple columns, handles errors with a fourth argument, and doesn't break when you insert a new column in the source.

Same example with XLOOKUP:

=XLOOKUP(A2, '[FileB.xlsx]Sheet1'!$A:$A, '[FileB.xlsx]Sheet1'!$D:$D, "Not found")

Cleaner. The fourth argument ("Not found") replaces IFERROR wrappers. And if someone inserts a column between A and D in File B, the formula still works — it references the Email column directly, not column number 4.

Fair warning: lookups reference external workbooks. If File B moves, gets renamed, or someone closes it without you converting the formulas to values first, you'll see #REF! errors everywhere. For long-term merges, switch to Power Query. For quick one-offs, lookups are fine. This is similar to how you'd approach how to merge in excel for cell-level joins, just at column scale.

Method 3 — Copy and Paste (Yes, Sometimes It's the Right Answer)

Don't dismiss this. For a one-shot job under 500 rows where both files have identical column structure, copy-paste beats every other method on speed. Three steps and done.

The clean way to do it

Open File B. Select the data rows (not headers, unless File A is empty). Ctrl+C. Switch to File A. Click the first empty cell below your existing data. Right-click → Paste Special → Values. Done.

Why Paste Special → Values? Direct paste brings formats, conditional formatting, formulas, and named ranges along for the ride. Values-only paste leaves File A's existing formats clean and dumps in just the data.

If both files have headers but different column orders, sort one before pasting. Or use Power Query — at that point you're past the simple paste threshold.

When paste goes wrong

Three failures keep showing up. One: dates pasted as text because regional settings differ. Fix: paste into a temp column, then =DATEVALUE() the column, paste-as-values back. Two: numbers showing as text (left-aligned with a green triangle warning). Fix: multiply the range by 1, or use Text to Columns → Finish. Three: hidden rows in the source. Selecting visible rows only: hold Alt+; (semicolon) before copying. That selects only visible cells.

Bottom line: paste works. But the moment you'd run the same merge a second time, switch to Power Query. Paste is a one-shot tool.

Method 4 — Consolidate (for Summary Totals, Not Raw Rows)

Consolidate is the most misunderstood tool in Excel. It's not a row-level merge. It's an aggregator. If you have January sales in Sheet1 and February sales in Sheet2, Consolidate creates a Sheet3 showing the SUM per product across both months. Raw rows? Not preserved. Just the totals.

When to use it

Monthly budget files where each tab has the same row labels (Q1 expenses, Q2 expenses, etc.) and you want totals. Sales by region across separate workbooks. Multiple locations reporting in the same template — Consolidate aggregates by row label.

How it works

Open a blank sheet (this is where the summary lands). Data tab → Consolidate. Pick a function (Sum, Average, Count, Max, Min). Add each source range one by one — they can be on different sheets or in different files. Check "Top row" and "Left column" if both files have matching headers and labels. Click OK.

The result: a new table with unique row labels and aggregated values. Consolidate matches on label name, so "Office Supplies" in File A and "office supplies" in File B will create two separate rows. Standardize labels first.

Why it's not what most people want

If you ask 100 Excel users "how do I merge two files," maybe 5 actually want Consolidate. The other 95 want stacked rows or column joins — and Consolidate doesn't do either. It silently aggregates and throws away row-level detail. That's a feature, not a bug, but only if you actually wanted aggregation. For comparing aggregated values to mail merge excel workflows (which aggregate contact lists differently), the logic is similar but the destination is different.

Method 5 — VSTACK and HSTACK (Microsoft 365 Only)

VSTACK and HSTACK landed in Microsoft 365 around 2022. They're dynamic array functions — meaning you type one formula in one cell, and Excel spills the result across as many cells as the data needs.

VSTACK basics

Syntax: =VSTACK(array1, array2, ...)

Stacks ranges vertically. If Sheet1 has data in A1:D100 and Sheet2 has data in A1:D80, the formula:

=VSTACK(Sheet1!A1:D100, Sheet2!A1:D80)

...spills 180 rows of combined data wherever you typed it. Live. Edit Sheet1, the merged view updates instantly.

HSTACK for side-by-side

Same idea, horizontal. =HSTACK(Sheet1!A1:A100, Sheet2!A1:A100) puts two columns next to each other in one spill. Useful for quick comparisons before you commit to a real merge.

LAMBDA combos for reusable merges

If you're stacking files weekly, wrap VSTACK in LAMBDA to build a reusable function. Name it MergeFiles in Formulas → Name Manager. Then =MergeFiles(Range1, Range2, Range3) works as a custom function. Not for beginners, but for power users it kills repetitive merge work.

The catch

VSTACK and HSTACK only exist in Microsoft 365 and Excel 2024. Excel 2019, 2021 perpetual, or anything older won't recognize them — they'll throw #NAME? errors. Check File → Account: if it says "Microsoft 365 Apps," you're set. Anything else, fall back to Power Query.

VBA and Power Automate (For Scheduled Merges)

For merges that run on a schedule — every Monday at 8 AM, or whenever a new file lands in a SharePoint folder — formulas and manual Power Query refreshes aren't enough. You need automation.

VBA basics

Press Alt+F11 to open the VBA Editor. Insert → Module. Paste a macro that opens File B, copies the data range, and pastes it into File A. The skeleton:

Sub MergeFiles()
  Workbooks.Open "C:\path\to\FileB.xlsx"
  Workbooks("FileB.xlsx").Sheets(1).Range("A2:D1000").Copy
  ThisWorkbook.Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
  Workbooks("FileB.xlsx").Close False
End Sub

Run it with a button or the Macro dialog (Alt+F8). For scheduled runs, use Windows Task Scheduler to launch Excel with the file and trigger the macro via command-line arguments.

Power Automate for file-level merges

If your files live in SharePoint, OneDrive, or Teams, Power Automate is cleaner than VBA. Build a flow: When a file is created in folder → Run Office Script → Append data to master file. No VBA, runs in the cloud, works whether your machine is on or off. Office Scripts (the cloud cousin of VBA) handle the copy/paste piece.

Common Errors and How to Fix Them

Five errors keep showing up every time someone merges two Excel spreadsheets. Knowing them in advance saves an afternoon of head-scratching. Each one has a specific symptom, a clear cause, and a fix that takes under a minute once you've seen it before.

Duplicate column headers after Append

Symptom: appending creates "Email" and "Email.1" instead of one Email column. Cause: column names don't match exactly across files — case, spaces, or hidden characters. Fix: rename columns to lowercase, trimmed versions in both queries before appending. Power Query → right-click column → Rename. Or use the Transform tab's Format → Trim and Format → lowercase to normalize automatically.

Mismatched data types

Symptom: numbers showing as #VALUE! after merge, or dates appearing as 5-digit numbers like 45291. Cause: one file stored a column as text, the other as a number or date. Excel treats the merged column as text by default, which breaks every downstream formula. Fix: in Power Query, change column types explicitly (Transform tab → Data Type). Whole Number, Decimal, Date, Text. Set the type BEFORE clicking Close & Load.

Merge key missing or with nulls

Symptom: many rows after Merge show null values in the new columns. Cause: the key column has missing or mismatched values, or the join kind was wrong. Fix: before merging, filter out nulls on the key column in both queries. Power Query → click filter icon on key column → uncheck (null). For partial matches (where some rows have no counterpart in File B), switch the join kind to Left Outer — that keeps every File A row and just leaves the new columns blank where no match exists.

Power Query can't find the file

Symptom: Refresh throws "DataSource.Error: Could not find file." Cause: source file moved or renamed. Power Query stores the absolute path at build time, so any move breaks the link. Fix: Power Query Editor → Advanced Editor → update the file path in the M code, OR delete the query and rebuild from the new path. Better long-term fix: use OneDrive or SharePoint URLs — they survive folder restructuring on the local disk.

#REF! errors after lookup formulas

Symptom: VLOOKUP formulas show #REF! after closing the source file. Cause: external workbook reference broke. Fix: convert the formula results to values (Copy → Paste Special → Values) before the source file is moved or deleted. Or switch to Power Query for permanent links — its file path is stored once and the data lives in the destination workbook. For more Excel troubleshooting, the excel reference page has the full error code list and recovery steps for each one.