How to Merge Multiple Excel Spreadsheets Into One

Combining data from several Excel files sounds straightforward until you're staring at a dozen spreadsheets with slightly different column layouts, inconsistent naming, and no obvious way to pull them together without copy-pasting for an hour. There are actually several solid methods for merging Excel spreadsheets — and the right one depends heavily on how your data is structured and what you need the final result to look like.

What "Merging" Actually Means in Excel

Before diving into methods, it's worth separating two things people often use interchangeably:

• Consolidating data — combining values from multiple sheets or files into a single summary (totals, averages, etc.)
• Appending data — stacking rows from multiple sheets into one continuous dataset

These are meaningfully different tasks. Consolidating is useful for reporting; appending is useful when you're building a master list from multiple sources. Most methods below serve one or both of these goals.

Method 1: Copy and Paste (Manual, No Setup Required)

The most basic approach. Open each file, select the data range, copy it, and paste it into a master sheet.

When it works well: Small datasets, one-time tasks, files with identical column structures.

Where it breaks down: Any variation in column order between files will silently scramble your data. It also doesn't scale — merging 20 files manually is tedious and error-prone.

If you go this route, always paste using Paste Special → Values to avoid dragging in unwanted formatting or formulas from source files.

Method 2: Power Query (Built Into Excel, No Coding Required) 🔧

Power Query is Excel's most powerful built-in tool for merging spreadsheets, and it's available in Excel 2016 and later (including Microsoft 365). It lets you connect to multiple files, transform the data, and load everything into a single table — all without writing a single formula.

Merging files from a folder

If your spreadsheets follow a consistent structure and live in the same folder, Power Query can pull them all in automatically:

1. Go to Data → Get Data → From File → From Folder
2. Select the folder containing your Excel files
3. In the preview window, click Combine → Combine & Transform Data
4. Power Query detects the structure and stacks all the files together

Any new files you add to that folder will be included the next time you refresh the query. This makes it ideal for recurring reports or monthly data drops.

Key Power Query strengths

• Handles mismatched column orders by matching on column names
• Lets you filter, rename, and reformat before loading
• Fully repeatable — refresh updates everything automatically

Skill level required: Beginner to intermediate. The interface is visual and guided, but understanding how to handle column mismatches or filter out header rows takes a little practice.

Method 3: Excel Formulas and References

For simpler scenarios, you can reference data across sheets or workbooks directly using formulas.

• =[WorkbookName.xlsx]SheetName!A1 pulls a value from another file
• INDIRECT() can build dynamic references, though it only works when source files are open

This approach is better suited to consolidating specific cells or ranges rather than appending large datasets. It also creates live links, meaning the master file depends on the source files remaining in the same location.

Method 4: VBA Macros

For users comfortable with Excel's built-in scripting language, a VBA macro can loop through multiple files and copy data into a master sheet programmatically.

This is the most flexible approach — you can handle unusual layouts, apply custom logic, and automate the entire process. The tradeoff is that writing and maintaining VBA requires more technical confidence, and macros can break if file structures change.

A basic macro for this task will:

1. Open each workbook in a specified folder
2. Copy a defined range or sheet
3. Paste it into the master workbook
4. Close the source file and repeat

VBA is worth learning if you're doing this regularly and Power Query doesn't give you enough control.

Method 5: Python or Third-Party Tools

Outside of Excel itself, tools like Python with the pandas library or dedicated ETL (Extract, Transform, Load) tools can merge spreadsheets at scale — especially useful when dealing with hundreds of files or very large datasets that slow Excel down.

Python's pandas.concat() or pandas.merge() functions handle both appending and joining datasets, and the logic can be saved and rerun anytime. This sits firmly in intermediate-to-advanced territory and requires a working Python environment.

The Variables That Determine Which Method Fits 📊

No single method is universally right. Here's what shapes the decision:

• Number of files — a handful vs. dozens vs. hundreds
• File structure consistency — identical columns make everything easier; variation adds complexity
• How often you need to repeat this — a one-time task vs. a recurring weekly process
• Data volume — large files can overwhelm formula-based approaches
• Your Excel version — Power Query isn't available in older versions like Excel 2010 or 2013
• Technical comfort level — manual methods vs. Power Query vs. VBA vs. scripting tools represent a real skill spectrum

A user merging three monthly sales reports with identical layouts has very different needs from someone aggregating data from 50 regional offices with inconsistent formatting. Power Query handles both scenarios well, but the setup and transformation steps look completely different in each case.

The method that saves time for one setup might cause problems in another — which is why understanding your own file structure, frequency, and comfort level matters more than defaulting to whichever method sounds most advanced.