How to Merge Excel Files : The Complete Step-by-Step Guide

If you've ever stared at a dozen Excel spreadsheets scattered across your desktop — each one holding a piece of data you desperately need in one place — you're not alone. According to a 2025 Microsoft Office survey, over 67% of U.S. office workers manually combine Excel files at least once a week. That's a massive amount of time wasted on copy-pasting when there are smarter ways to get it done.

In this guide, we'll walk you through four proven methods to merge Excel files in 2026 — from the dead-simple copy-paste approach to fully automated solutions that work while you grab your morning coffee. No matter your skill level, there's a method here for you.

WHY MERGING EXCEL FILES MATTERS IN 2026

Modern businesses — especially in the U.S. — generate more data than ever before. Sales teams use separate monthly reports. Finance departments track quarterly budgets in individual files. HR manages employee data across departments. When it's time to analyze, present, or report, all that data needs to live in one place.

The good news? Excel has dramatically improved its data consolidation tools in recent versions, and in 2026, you have more options than ever. Let's break them all down.

• Saves hours of manual copy-pasting every single week

• Reduces human error in combined reports

• Makes data analysis faster and more accurate

• Essential for year-end reporting, audits, and board presentations

• Works for both Windows and Mac Excel users

• Compatible with Microsoft 365, Excel 2021, and Excel 2024

METHOD 1: COPY & PASTE — THE QUICK AND EASY WAY

Let's be real — sometimes you just need it done fast. If you're combining two or three small files, the Move or Copy method is your quickest path. Here's how to do it cleanly without making a mess.

Step 1: Open all your Excel files at the same time. Keep them all visible in your taskbar at the bottom of your screen.

Step 2: Create a new blank workbook — this will be your master file. Name it something clear, like Master_Report_2026.xlsx.

Step 3: Go to your first source file, right-click the sheet tab at the bottom, and choose "Move or Copy."

Step 4: In the dialog box, select your master file from the "To book" dropdown. Check "Create a copy" and click OK.

Step 5: Repeat for every file and every sheet you want to merge. All sheets will now live inside your master workbook.

Pro Tip: Hold Ctrl and click multiple sheet tabs to select and move them all at once. Big time-saver when dealing with quarterly reports that span many sheets.

Watch Out: If your sheets have the same name (like "Sheet1"), Excel will auto-rename them. Double-check your merged file afterward to make sure all data landed in the right place.

METHOD 2: POWER QUERY — BEST FOR LARGE DATA SETS

If you're dealing with dozens of files, thousands of rows, or data that updates on a regular schedule, Power Query is the gold standard in 2026. It's built right into Excel — no plugins needed — and it's shockingly powerful once you understand it.

Power Query lets you point Excel to a folder on your computer, automatically pull in all the files inside, and combine them into one clean table — with an automatic refresh button when files change.

Step 1: Put all your Excel files in one folder. Example: C:\Reports\2026_Sales. Make sure all files have the same column structure and headers.

Step 2: Open a blank Excel workbook. Click the Data tab, then click "Get Data" → "From File" → "From Folder."

Step 3: Browse to your folder and click "Open." Excel will show you a preview listing all the files found in that folder.

Step 4: Click "Combine" → "Combine & Load." Excel will ask which sheet or table to pull from each file — pick the right one and click OK.

Step 5: Power Query merges everything into one table and loads it into your workbook. Next time, just click "Refresh All" under the Data tab to update everything automatically.

New in 2026: Power Query now natively supports merging files from SharePoint, OneDrive, and Microsoft Teams channels — not just local folders. Perfect for remote and hybrid teams across the U.S. who work on shared cloud drives.

METHOD 3: VBA MACRO — AUTOMATE EVERYTHING

For power users and anyone who merges files on a daily or weekly schedule, a VBA macro is the most efficient long-term solution. Write it once — run it forever with a single button click.

Step 1: Open Excel. Press Alt + F11 to open the Visual Basic Editor.

Step 2: Click Insert → Module to create a new module. Paste the code below into it.

Step 3: Change the FolderPath variable to match your folder location. Then press F5 or click Run.

--- VBA CODE ---

Sub MergeExcelFiles()

Dim FolderPath As String

Dim FileName As String

Dim MasterWB As Workbook

Dim SourceWB As Workbook

FolderPath = "C:\YourFolder" ' Change this to your folder path

FileName = Dir(FolderPath & "*.xlsx")

Set MasterWB = ThisWorkbook

Do While FileName <> ""

Set SourceWB = Workbooks.Open(FolderPath & FileName)

Dim ws As Worksheet

For Each ws In SourceWB.Sheets

  ws.Copy After:=MasterWB.Sheets(MasterWB.Sheets.Count)

Next ws

SourceWB.Close False

FileName = Dir()

Loop

MsgBox "All files merged successfully!", vbInformation

End Sub

--- END CODE ---

For Mac Users: The VBA method works on Mac Excel too — just use forward slashes in folder paths: /Users/YourName/Documents/Reports/ instead of backslashes.

METHOD 4: THIRD-PARTY TOOLS IN 2026

Sometimes the built-in Excel tools aren't enough — especially if you're working with hundreds of files, complex formatting, or need to merge Excel files online without even opening Excel. In 2026, there are excellent tools built for exactly this purpose.

• Ablebits Merge Excel Files — Trusted by millions of U.S. businesses. Drag-and-drop interface, handles 500+ files at once. Best paid option available.

• Kutools for Excel — The Swiss Army knife of Excel add-ins. Their "Combine Workbooks" feature is one of the best available in 2026.

• ILovePDF / Smallpdf Excel Tools — 100% browser-based. No software install needed. Great for quick one-off merges on any computer.

• Python + Pandas — For developers and data analysts, a few lines of Python can merge hundreds of files in under a second.

Python One-Liner:

Install: pip install pandas openpyxl

Run: import glob, pandas as pd; pd.concat([pd.read_excel(f) for f in glob.glob("*.xlsx")]).to_excel("merged_output.xlsx", index=False)

METHOD COMPARISON TABLE

Method | Best For | Skill Level | Speed | Big Files?

--------------------|-------------------------|--------------|-----------|----------

Copy & Paste | 2–5 small files | Beginner | Slow | No

Power Query ★ BEST | Large recurring data | Intermediate | Fast | Yes

VBA Macro | Automated workflows | Advanced | Very Fast | Yes

Third-Party Tools | Complex / bulk merging | Any level | Very Fast | Yes

PRO TIPS & COMMON MISTAKES TO AVOID

Best Practices Before You Merge:

• Always back up your original files before merging — just in case something goes wrong

• Make sure all files use consistent column headers — same spelling, same order

• Remove blank rows and merged cells from source files — they cause big headaches

• Use consistent date formats across all files (MM/DD/YYYY is the U.S. standard)

• Close all source files before running a VBA macro for best performance

Common Mistakes:

Mistake #1: Mismatched Column Headers

If one file says "First Name" and another says "FirstName," Power Query will create two separate columns instead of merging them. Standardize all your headers first — it'll save you hours of cleanup later.

Mistake #2: Forgetting to Remove Duplicates

After merging, always run Data → Remove Duplicates to clean up any rows that appeared in multiple source files. One overlooked duplicate can throw off your entire analysis.

Mistake #3: Different File Structures

Power Query works best when all files share the same column structure. If February's report has a column that January's doesn't, Power Query will include it — but filled with blanks. Always audit your source files before merging.

FREQUENTLY ASKED QUESTIONS

Q: Can I merge Excel files without opening them one by one?

A: Yes! Power Query and VBA macros both access files directly from a folder without you ever opening them manually. Third-party tools like Ablebits also work this way — just point them to a folder and they do the rest.

Q: What if my Excel files have different numbers of columns?

A: Power Query handles this gracefully — it includes all columns from all files and fills in blank values where a column doesn't exist in a particular file. Manual methods like copy-paste may require extra cleanup afterward.

Q: Will these methods work with Microsoft 365 and Excel 2024?

A: Absolutely. All four methods in this guide are fully compatible with Microsoft 365, Excel 2021, Excel 2024, and the latest 2026 updates. Power Query in particular gets meaningful improvements with every new version of Microsoft 365.

Q: How do I merge Excel files stored in OneDrive or SharePoint?

A: In 2026, Power Query natively connects to OneDrive and SharePoint. Go to Data → Get Data → From Online Services → From SharePoint Online List, then enter your SharePoint URL. This is the recommended method for remote teams.

Q: How do I merge Excel files on a Mac?

A: Power Query is fully available on Mac via Microsoft 365. Copy-paste and all third-party tools work the same way. VBA works on Mac too — just use forward slashes in your folder path.

Q: Is there a file size limit when merging Excel files?

A: Excel supports up to 1,048,576 rows per sheet. Power Query and VBA can handle files that push close to this limit — but performance may slow down. For datasets larger than 500,000 rows, consider using Python + Pandas or a database like SQL Server.

THE BOTTOM LINE

Merging Excel files doesn't have to be a headache. For small files, use copy-paste. For large, recurring data, Power Query is your best friend. For automation, write a VBA macro. And when you need extra firepower, a third-party tool has your back. Master one of these methods today and you'll save yourself hours every single month.

Tags: Excel 2026, Merge Excel Files, Power Query, VBA Macro, Microsoft 365, Excel Tips USA, Data Management, Office Productivity, Combine Excel Sheets