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.
Best for repeatable joins, 1,000+ rows, or folder-level merges. Refreshable. Learning curve.
Best for pulling one or two columns from File B into File A. Lives in cells, breaks if columns move.
Manual, fast for one-off jobs under 500 rows. No refresh. Easy to mess up.
Built-in tool for SUM, AVG, COUNT across sheets. Not a true row merge — it aggregates.
Dynamic array functions. Spill stacked or side-by-side ranges across sheets. Live, recalculates.
Workbooks.Open + Range.Copy. For scheduled jobs or repeatable file-level merges.
Append stacks rows from File B underneath File A. Both files need the same column structure (Name, Email, Date). End result: more rows, same columns.
Merge joins File B's columns onto File A's rows using a shared key (like Customer ID). End result: same rows, more columns.
If you say "merge" but actually need to stack rows, you'll waste an hour trying to build a join. Identify which one you need before opening Power Query — the menu literally asks you to choose Append or Merge as separate options.
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.
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.
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.
What it does: Adds File B's rows below File A's rows. Columns must match — same names, same data types.
When to use: Monthly sales files, daily exports, multi-region CSVs with identical structure. Stacking January + February sales into one annual table is a classic Append job.
Watch out for: Mismatched column names ("Customer Name" vs "customer_name") create separate columns instead of merging them. Rename in Power Query first, then Append.
What it does: Matches rows between File A and File B using a shared key (Customer ID, Email, Order Number). Pulls File B's columns onto File A's rows.
When to use: Adding customer details from CRM to order data. Pulling product specs into a sales report. Linking employee IDs to payroll info.
Join kinds: Left Outer (keep all File A, match where possible — most common). Inner (only matched rows). Full Outer (everything, blanks where no match). Anti (rows in A that have no match in B — useful for finding gaps).
What it does: Reads every file in a folder, extracts the same sheet from each, stacks the rows. Auto-detects new files on refresh.
When to use: Monthly reports dropped into one folder. Regional sales exports from 12 stores. Year-end aggregation of weekly logs.
Setup: Data → Get Data → From File → From Folder. Pick folder. Click Combine. Excel creates a sample query, a helper function, and the final combined table — all automatically.
Right-click table → Refresh: pulls latest data from source files. Only works while source files exist at the same path.
Auto-refresh on open: Query Properties → "Refresh data when opening the file." Useful for dashboards.
Background refresh: on by default. Turn off if you need the refresh to finish before macros run on the result.
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.
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 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Save both files to a stable location. Don't move or rename them after building the query — Power Query stores the path.
Data tab → Get Data → From File → From Workbook. Repeat for File B. Both queries appear in the Queries pane.
Append for stacking rows (same columns). Merge for joining on a key column. Different menu items — pick the right one upfront.
For Merge: pick key columns, pick join kind (Left Outer is the safest default). For Append: confirm column names match exactly.
Click the expand icon on the merged column to pull in fields. Rename columns, set data types (text/number/date), remove unused columns.
Home → Close & Load. Excel drops the result into a new sheet as a refreshable table. You're done — click Refresh anytime to pull updates.
Right-click table → Properties → check 'Refresh data when opening the file' for daily auto-refresh.
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.
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.
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.
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.
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.
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.