How to Merge Excel Files: Free Tool + Python + Power Query (2026)

Merging Excel files means combining multiple .xlsx or .xls files into one workbook. You can either append rows (stack data vertically) or keep each file on separate sheets. The Excel Merger tool handles both scenarios in your browser without uploads.

This guide walks through the most reliable ways to combine Excel workbooks, depending on what you actually need: append rows into one sheet, keep each file on a separate sheet, or merge only when headers match. You’ll see a fast browser-based option, plus Python (pandas), Power Query, and VBA for more control.

What Are XLS and XLSX Files?

Understanding two main Excel formats helps before merge methods. Format affects available tools and limitations encountered.

XLS (Excel Binary Format) existed since Excel 97.

Uses binary format called BIFF8. Supports up to 65,536 rows and 256 columns. Tends to produce larger files.

Still see XLS files in legacy systems and older Excel installations.

XLSX (Excel Open XML Format) arrived with Excel 2007. Modern standard.

Essentially ZIP archive containing XML files. Makes it smaller and more compatible. Supports over million rows and 16,000+ columns.

Google Sheets, LibreOffice, and every current spreadsheet tool can read XLSX.

Why format matters when merging:

Combined data past 65,536 rows requires XLSX output or lose data. Mix of XLS and XLSX files from different sources needs tool handling both.

Output format determines what software can open result.

Why Would You Need to Merge Excel Files?

There are more scenarios than you'd think:

Consolidating sales reports from multiple months or regions into one master spreadsheet. Aggregating survey responses from different collection periods. Merging financial records like income statements or expense reports from various departments. Combining inventory data from different warehouses. Pulling together HR records, attendance logs, or timesheet exports. Merging student grades or enrollment records across semesters. Consolidating e-commerce order exports from Amazon, Shopify, and eBay. Preparing datasets for analysis in Power BI, Tableau, or R by combining data from multiple sources. Combining exported data from legacy systems before importing into a new platform.

The list goes on, but you get the idea. If you work with data, you'll eventually need to combine spreadsheets.

Method 1: Use the Free Online Excel Merger (No Code Required)

This is the fastest option when the goal is simply to combine files without installing anything. Everything runs locally in the browser for privacy.

Try it here: merge-json-files.com/excel-merger

How it works:

1. Open the Excel merger page
2. Drag and drop Excel files (.xlsx, .xls, or .csv)
3. Review file details (sheet count, row count, file size)
4. Choose a merge strategy:
   • Append Rows: stack all rows into one sheet (best when columns match)
   • Separate Sheets: keep each file on its own sheet in the output workbook
   • Merge by Header: group and merge files that share the same headers
5. Choose an output format (XLSX, XLS, or CSV)
6. Toggle "Skip Duplicate Headers" when appending rows
7. Click "Merge Excel Files"
8. Preview the result
9. Download the merged file

Because processing happens locally, spreadsheet contents are not uploaded to a server. This option works well for quick, one-off merges where you want a clean result without writing code.

Method 2: Merge Excel Files Using Python

Python with pandas is by far the most powerful approach, especially if you're merging files regularly or working with large datasets. Once you write the script, you can run it whenever you need it.

Install the required libraries:

pip install pandas openpyxl xlrd

(openpyxl handles .xlsx files, xlrd handles legacy .xls files)

Basic: Append All Files Into One Sheet

import pandas as pd
import glob

def merge_excel_files(input_pattern, output_file):
    """Merge multiple Excel files by appending rows."""
    all_dataframes = []
    files = sorted(glob.glob(input_pattern))

    for filepath in files:
        df = pd.read_excel(filepath)
        all_dataframes.append(df)
        print(f"Read {filepath}: {len(df)} rows, {len(df.columns)} columns")

    merged = pd.concat(all_dataframes, ignore_index=True)
    merged.to_excel(output_file, index=False)
    print(f"\nMerged {len(files)} files → {len(merged)} rows → {output_file}")

# Usage
merge_excel_files("./reports/*.xlsx", "merged_report.xlsx")

Advanced: Keep Each File on a Separate Sheet

import pandas as pd
import glob
import os

def merge_to_separate_sheets(input_pattern, output_file):
    """Merge Excel files, keeping each on a separate sheet."""
    files = sorted(glob.glob(input_pattern))

    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        for filepath in files:
            df = pd.read_excel(filepath)
            # Use filename (without extension) as sheet name
            sheet_name = os.path.splitext(os.path.basename(filepath))[0][:31]
            df.to_excel(writer, sheet_name=sheet_name, index=False)
            print(f"Added sheet '{sheet_name}': {len(df)} rows")

    print(f"\nCreated {output_file} with {len(files)} sheets")

# Usage
merge_to_separate_sheets("./data/*.xlsx", "all_data.xlsx")

Advanced: Merge with Duplicate Removal

import pandas as pd
import glob

def merge_and_deduplicate(input_pattern, output_file, key_columns=None):
    """Merge Excel files and remove duplicate rows."""
    all_dfs = []

    for filepath in sorted(glob.glob(input_pattern)):
        df = pd.read_excel(filepath)
        all_dfs.append(df)

    merged = pd.concat(all_dfs, ignore_index=True)
    before_count = len(merged)

    if key_columns:
        merged = merged.drop_duplicates(subset=key_columns, keep='first')
    else:
        merged = merged.drop_duplicates(keep='first')

    after_count = len(merged)
    print(f"Removed {before_count - after_count} duplicates")
    print(f"Final row count: {after_count}")

    merged.to_excel(output_file, index=False)

# Usage - deduplicate by email column
merge_and_deduplicate("./contacts/*.xlsx", "unique_contacts.xlsx", key_columns=["Email"])

Python handles thousands of files and millions of rows without breaking a sweat. You get full control over merge logic, filtering, and transformations. You can automate it with cron jobs or build it into a CI/CD pipeline. And you can clean data, remove duplicates, and add calculated columns all during the merge process.

The downside is that it requires Python and library installation, and there's a learning curve if you haven't written code before. It can also be memory-intensive with very large files.

Method 3: Merge Excel Files with Power Query (Built into Excel)

If you're an Excel power user and you're running Excel 2016 or Microsoft 365, Power Query is worth knowing about. It's built right in, so there's nothing extra to install.

Step by step:

1. Put all your files in one folder (e.g., C:\Reports)
2. Open Excel, go to the Data tab, click Get Data, then From Folder
3. Navigate to your folder and click OK
4. Click Combine & Transform Data
5. Power Query shows a preview of the merged data. Click OK to accept
6. In the Power Query Editor, review everything
7. Click Close & Load to import into your worksheet

Some things you can do with Power Query once the data is loaded:

Filter by filename by adding a column with the source filename to track where each row came from. Apply transformations like renaming columns, changing data types, or filtering rows before loading the data. Handle missing columns gracefully since Power Query deals with files that have different column structures better than most tools. Set up scheduled refresh so the merge runs automatically when source files are updated.

Power Query is great because it requires no coding, handles mixed file structures well, and supports automatic refresh. The main drawback is that it's only available in Excel 2016+ or Microsoft 365, there's a bit of a learning curve for the M language, and you can't easily merge files from different folders without extra configuration.

Method 4: Merge Excel Files with VBA Macro

If you're comfortable with Excel's built-in programming, VBA gives you solid control over the merge process.

VBA Macro to Merge All XLSX Files in a Folder:

Sub MergeExcelFiles()
    Dim FolderPath As String
    Dim FileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim DestWs As Worksheet
    Dim LastRow As Long
    Dim DestLastRow As Long
    Dim IsFirstFile As Boolean

    ' Set folder path (change this)
    FolderPath = "C:\Reports\"

    Set DestWs = ThisWorkbook.Sheets(1)
    IsFirstFile = True
    DestLastRow = 1

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

    Do While FileName <> ""
        Set wb = Workbooks.Open(FolderPath & FileName)
        Set ws = wb.Sheets(1)
        LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

        If IsFirstFile Then
            ' Copy header and data from first file
            ws.Range("A1:A" & LastRow).EntireRow.Copy DestWs.Range("A1")
            DestLastRow = LastRow + 1
            IsFirstFile = False
        Else
            ' Copy data only (skip header) from subsequent files
            ws.Range("A2:A" & LastRow).EntireRow.Copy DestWs.Range("A" & DestLastRow)
            DestLastRow = DestLastRow + LastRow - 1
        End If

        wb.Close SaveChanges:=False
        FileName = Dir
    Loop

    MsgBox "Merge complete! Total rows: " & DestLastRow - 1
End Sub

How to use it:

1. Open a new Excel workbook
2. Press Alt + F11 to open the VBA Editor
3. Go to Insert, then Module
4. Paste the code above
5. Update the FolderPath variable to point to your folder
6. Press F5 to run

VBA runs directly in Excel and doesn't need extra software. You can customize it for complex merge logic. On the other hand, you do need some VBA knowledge, it's slower than Python for large datasets, and macros may be blocked by your organization's security settings.

Method 5: Command-Line with csvkit or ssconvert

For developers and power users who prefer working in the terminal:

Using csvkit (Python-based CLI):

# Install
pip install csvkit

# Convert Excel to CSV first
in2csv report1.xlsx > report1.csv
in2csv report2.xlsx > report2.csv

# Stack/merge CSV files
csvstack report1.csv report2.csv > merged.csv

Using ssconvert (Gnumeric):

# Install on Linux
sudo apt install gnumeric

# Convert and merge
ssconvert --merge-to=merged.xlsx file1.xlsx file2.xlsx file3.xlsx

Comparison of Excel Merge Methods

*Limited by browser memory

Understanding Merge Strategies

Picking the right merge strategy is probably the most important decision you'll make. Here's what each one does:

Strategy 1: Append Rows (Vertical Stack)

This is the go-to when all your files have identical column structures. It stacks the data vertically, one file after another:

File 1:          File 2:          Merged:
Name  | Sales    Name  | Sales    Name  | Sales
Alice | $100     Carol | $300     Alice | $100
Bob   | $200     Dave  | $400     Bob   | $200
                                   Carol | $300
                                   Dave  | $400

Strategy 2: Separate Sheets

When your files have different structures, or when you want to keep the source data separated, this puts each file on its own sheet:

Output Workbook:
├── Sheet "Q1_Report": (data from file 1)
├── Sheet "Q2_Report": (data from file 2)
└── Sheet "Q3_Report": (data from file 3)

Strategy 3: Merge by Matching Headers

For mixed file types where some share the same columns and others don't:

Files with headers [Name, Email] → merged into Sheet 1
Files with headers [Product, Price] → merged into Sheet 2

Which Method to Use

• One-off merge, non-technical: use the online tool at merge-json-files.com/excel-merger
• Repeated task, same column structure: use Python with pandas
• Excel power user, no coding: use Power Query (Data > Get Data > From File)
• Need to merge 50+ files automatically: use Python or VBA

Best Practices for Merging Excel Files

A few habits prevent most merge headaches:

• Standardize headers first. "First Name" vs "FirstName" vs "first_name" will be treated as different columns.
• Clean data beforehand. Remove blank rows, summary totals, and footer text so they don’t become garbage rows in the output.
• Check data types. If one file has text and another has numbers in the same column, the merged sheet can end up misformatted.
• Use XLSX for output when row counts might exceed 65,536 (the XLS limit).
• Back up originals so it’s easy to re-merge with different settings later.
• Verify row counts. Compare the sum of rows across source files with the merged result.
• Use the preview (when available). It’s the fastest way to catch header alignment issues.
• Handle dates carefully. Mixed regions can produce MM/DD/YYYY vs DD/MM/YYYY surprises.
• Consider file size. Very large workbooks can be slow to open, so splitting into chunks can help.
• Document your process (files used, strategy chosen, and any cleanup steps).

Frequently Asked Questions

Can I merge Excel files with different numbers of columns?

Yes, but it depends on the strategy. With "Append Rows," missing columns are filled with empty cells. With "Separate Sheets," each file keeps its original structure. The browser-based tool supports both scenarios without extra setup.

Does merging Excel files preserve cell formatting?

The browser-based tool preserves values, not formatting like colors, fonts, or borders. If formatting matters, Power Query or VBA are better options.

Can I merge password-protected Excel files?

Not directly. Remove the password first (File → Info → Protect Workbook → Encrypt with Password).

How do I merge specific sheets from Excel files?

Use the "Separate Sheets" strategy and delete sheets you don’t need, or in Python select a sheet with pd.read_excel(file, sheet_name="Sheet1").

Can I merge Excel files on my phone?

Yes. The browser-based tool works in mobile browsers (Chrome, Safari, Firefox). Open merge-json-files.com/excel-merger, upload the files, and merge.

What's the maximum file size I can merge?

With a browser-based merge, the practical limit depends on available memory. Modern browsers often handle 50 to 100MB comfortably. For very large datasets, Python with pandas is usually the most reliable.

Related Tools and Resources

• JSON to Excel Converter

Frequently Asked Questions

How do I merge Excel files without losing formatting?

Use Power Query or VBA macros to preserve cell formatting like colors, fonts, and borders. The online tool and Python pandas preserve data values but not visual formatting. For formatting-critical merges, stick with Excel-native methods.

Can I merge Excel files with different column headers?

Yes, using the "Separate Sheets" strategy keeps each file's original structure. With "Append Rows," missing columns get filled with empty cells. The online tool handles both scenarios automatically without manual column mapping.

What's the easiest way to merge Excel files without VBA?

The browser-based Excel merger at merge-json-files.com/excel-merger requires no installation or coding. Just drag files, choose your merge strategy, and download the result. Everything processes locally for privacy.

Will formulas still work after merging Excel files?

Formulas may break if they reference cells outside the merged range. Simple formulas (SUM, AVERAGE) usually work fine. Complex formulas with external references need manual adjustment after merging.

How do I merge Excel files in Python?

Install pandas and openpyxl, then use pd.read_excel() to load files and pd.concat() to combine them. The Python examples in this guide show complete working code for different merge scenarios.

Final Thoughts

Most Excel merges come down to choosing the right strategy (append vs separate sheets vs header matching), keeping headers consistent, and validating the output before sharing it.

For quick, one-off merges, a browser-based tool is the simplest option. For recurring workflows, Python with pandas gives maximum flexibility. And when staying inside Excel is the priority, Power Query and VBA can handle most day-to-day cases.

Related Guides: Check out how to merge JSON files and how to split JSON files for more data processing tutorials.