How to Merge Two Excel Files Into One

There are two distinct flavors of "merge two Excel files": combining data from separate workbooks into a single sheet, or consolidating multiple sheets into one workbook. Both are common. None of the native Excel approaches handle them particularly well.

Why merging Excel files is harder than it should be

Excel was built around a single-file, single-user mental model. The tools for working with multiple files simultaneously — copy-paste, Power Query, VBA — all assume you know exactly what you're doing before you start. They don't account for the most common real-world scenario: two files with slightly different column names, different sheet arrangements, and no documentation on what changed when.

The pain shows up in three places:

• Header mismatches — File A uses "Customer Name"; File B uses "Client". A naive paste merges the wrong columns.
• Lost context — When you paste one file into another, you lose track of which data came from which source. Auditing becomes guesswork.
• Sheet navigation friction — Switching between two workbooks to compare their structure before merging takes longer than the merge itself.
• No rollback — Excel doesn't track your merge steps. If you paste in the wrong sheet or overwrite data, undo history is limited and often clears on save.

The fix isn't just merging — it's merging while keeping both files visible so you can verify what you're doing before it becomes permanent.

Four ways to merge two Excel files into one

Method 1: Copy and paste — fast but error-prone

Open both files side by side, select the data in the source file, copy it, then paste into the destination file. Works fine for a one-time merge with clean, consistent data. Here's the process:

1. 1
   Open both workbooks Open the destination file first — this is where the merged data will land. Open the source file second.
2. 2
   Select the source data Click the tab of the sheet you want to copy from. Select the range — or press Ctrl+A to select the entire sheet. Press Ctrl+C.
3. 3
   Navigate to the destination sheet Click the destination workbook. Navigate to the sheet or create a new one. Position your cursor where the data should begin.
4. 4
   Paste Press Ctrl+V. Use Paste Special → Values if you want to strip formulas and paste only the data.

The copy-paste method breaks down when the two files have different column orders, when the source data has hidden rows you didn't notice, or when you need to merge multiple sheets and can't track which paste came from which file.

Method 2: Power Query (Get & Transform) — repeatable and flexible

Power Query is Excel's built-in ETL tool. It connects to files, transforms the data, and outputs it to a destination sheet — all with a repeatable step history. This is the right approach when you have recurring merges (weekly reports, monthly consolidations) and want a process you can re-run without re-doing the setup.

To merge two files using Power Query:

1. 1
   Get Data → From File → From Folder or Workbook In Excel 2016+, go to Data → Get Data → From File → From Folder (for multiple files) or From Workbook (for individual files).
2. 2
   Load and Transform Select your files and click Transform Data. The Power Query Editor opens. You see each file as a separate query.
3. 3
   Append or Merge Use Append Queries to stack rows from multiple files on top of each other. Use Merge Queries to join them side by side on a key column (like an ID or date). Choose the join type (inner, left outer, full outer) based on what you need to keep.
4. 4
   Load to sheet Click Close & Load. Power Query outputs the result to a new sheet in your destination workbook. The step history is saved — re-open the Query Editor and click Refresh to re-run the merge on updated files.

Method 3: VBA macro — automated but requires code

For power users who need to merge files programmatically — or who have a fixed workflow that doesn't change — a VBA macro automates the merge process entirely. You write the code once, run it whenever needed, and the macro handles the copy-paste loop across as many files as you specify.

A basic merge macro looks like this:

This opens every Excel file in a specified folder, copies each sheet to the destination workbook, then closes the source file without saving. The result is one workbook containing all sheets from all source files.

Method 4: DualPane — see both files while you merge

None of the methods above solve the fundamental visibility problem: when you're merging two files, you're working blind. You open one file, switch to another, try to remember what the source data looked like — and then paste and hope it landed in the right place.

DualPane is an Excel add-in that splits your Excel window so you can see two sheets from two different workbooks at the same time. Before you run a copy-paste merge, a Power Query setup, or even a VBA macro — open both files, split the view with DualPane, and visually confirm the data structure before you touch anything.

The workflow for a careful merge with DualPane:

1. 1
   Open both Excel files Open the source file and the destination file in separate Excel windows.
2. 2
   Split the view with DualPane Click Split in the DualPane toolbar. Both files appear side by side in the same Excel window. See the exact column headers, row counts, and data format in both files simultaneously.
3. 3
   Identify column mismatches before pasting Scroll through both files. Notice that File A uses "Amount" in column D while File B uses "Total" in column D? Spot it now, not after you've already pasted. With both files visible, mismatches are obvious.
4. 4
   Execute the merge with confidence Copy the verified source data. Navigate to the destination. Paste. The visual confirmation step prevents the most common merge errors — wrong columns, misaligned rows, overwritten headers.

For ongoing merges — where you're combining the same two files every week — DualPane also lets you save tab pairs as named layouts. Open both files, split the view once, save the layout. Next week, open the files and restore the layout. The view is ready in one click.

Comparison: how each merge method stacks up