How to Merge Excel Files Into One File

Combining data from multiple Excel workbooks is one of those tasks that sounds straightforward until you're staring at a dozen spreadsheets with slightly different column headers and no clear path forward. Whether you're consolidating monthly reports, pulling together data from different departments, or combining exported data from multiple sources, there are several ways to approach this — and which method works best depends heavily on what your files actually look like and what you need the result to do.

What "Merging" Actually Means in Excel

Before diving into methods, it's worth being precise about what you're trying to achieve, because "merge" can mean different things:

• Combining sheets into one workbook — moving or copying individual worksheets from multiple .xlsx files into a single file, each on its own tab
• Stacking data into one sheet — taking rows of data from multiple files and appending them into a single continuous dataset
• Consolidating summarized data — using Excel's Consolidate feature to aggregate values (sums, averages, counts) from matching ranges across files

These are meaningfully different operations. Confusing them leads to extra work.

Method 1: Move or Copy Sheets Manually

If you have a small number of files (say, two to five) and just want each file's sheets collected under one roof, the manual approach is often the fastest.

1. Open all the source workbooks
2. Right-click a sheet tab in the source file
3. Select Move or Copy
4. In the dropdown, choose your destination workbook
5. Check Create a copy if you want to preserve the original
6. Repeat for each sheet

This works well when your sheets are self-contained and don't need to be stitched together row by row. The limitation is obvious: it doesn't scale. Doing this across 20 files with 5 sheets each is a chore.

Method 2: Power Query (the Most Reliable Modern Approach) 💡

Power Query is built into Excel (available in Excel 2016 and later, and Microsoft 365) and is purpose-built for combining data from multiple sources. It's the go-to method when you need to stack rows from many files into one clean table.

The general workflow for merging multiple Excel files from a folder:

1. Go to Data → Get Data → From File → From Folder
2. Select the folder containing your Excel files
3. Power Query shows a preview of all files detected
4. Click Combine & Transform or Combine & Load
5. Choose which sheet within the files to pull from
6. Excel stacks all matching data and loads it into your workbook

Power Query assumes your files share a consistent structure — same column names, same data types in the same positions. If your source files have inconsistent headers or varying layouts, Power Query will still run but may produce misaligned or messy output that needs cleanup.

The major advantage here is that the query is refreshable. Drop a new file into the folder, hit Refresh, and the merged dataset updates automatically. This makes it genuinely useful for recurring reporting workflows, not just a one-time fix.

Method 3: VBA Macro for Custom Control

If Power Query doesn't give you the control you need — or if you're working with older Excel versions — a VBA macro can automate the merge process with more precision.

A basic macro to loop through files in a folder, open each one, copy the data range, and paste it into a master sheet is a few dozen lines of code. The logic typically involves:

• Defining a source folder path
• Looping through each .xlsx file
• Identifying the used range on the relevant sheet
• Appending that data to the master sheet, skipping headers after the first file

VBA gives you fine-grained control over which rows or columns to include, how to handle headers, and what to do with blank rows. The tradeoff is that it requires either writing the code yourself or sourcing a reliable script — and it's less forgiving of structural inconsistencies in your source files than Power Query's visual interface.

Method 4: Excel's Built-In Consolidate Feature

The Consolidate tool (found under Data → Consolidate) is designed for a specific use case: summarizing numerical data from multiple ranges by matching row and column labels.

This is not a row-stacking tool. It's best for situations like combining monthly budget spreadsheets where you want a single summary showing totals or averages by category. It supports functions like Sum, Average, Count, Max, and Min across source ranges.

If your goal is to preserve individual records rather than summarize them, Consolidate isn't the right tool.

The Variables That Determine Which Method Fits

When File Structure Gets Messy 🗂️

The most common failure point in any merge process is inconsistent source data. Files where column A is "First Name" in one file and "firstname" in another, or where some files have an extra summary row at the top, will cause problems regardless of method.

Before merging, it's worth auditing your source files:

• Are column headers identical across all files?
• Do all files use the same sheet name you're targeting?
• Are there merged cells, blank header rows, or footer rows that need to be stripped?

Cleaning this up before merging saves significant troubleshooting time on the back end.

Thinking About Your Specific Situation

The right approach depends on factors only you can evaluate: how many files you're working with, how often you need to repeat the process, how consistent your source data is, and what version of Excel you're running. A one-time merge of three tidy files is a completely different problem than a monthly consolidation of 50 exported reports with slightly varying structures — even though both technically involve "merging Excel files."