How to Merge Excel Files Online (XLS & XLSX): The Complete Guide

If you've ever worked with spreadsheets for more than a week, you know the pain. Five different people send you five different Excel files, and now you need everything in one place. Maybe it's monthly sales numbers, survey results from separate teams, or financial reports from three different departments. Whatever the case, merging Excel files is one of those tasks that sounds simple until you actually sit down and try to do it.

I've spent way too many hours copy-pasting rows between spreadsheets, so I put together this guide covering every method I know. We'll go through a free online tool (fastest option), Python with pandas (best for automation), Power Query (built right into Excel), VBA macros, and even command-line approaches. By the end, you should be able to pick the method that fits your situation and get it done without losing your mind.

What Are XLS and XLSX Files?

Before we get into the merge methods, it helps to understand the two main Excel formats, because the format you're working with affects what tools you can use and what limitations you'll run into.

XLS (Excel Binary Format) has been around since Excel 97. It uses a binary format called BIFF8, supports up to 65,536 rows and 256 columns, and tends to produce larger files. You still see XLS files floating around in legacy systems and older Excel installations.

XLSX (Excel Open XML Format) arrived with Excel 2007 and is the modern standard. It's essentially a ZIP archive containing XML files, which makes it smaller and more compatible. It supports over a million rows and 16,000+ columns. Google Sheets, LibreOffice, and every current spreadsheet tool can read XLSX.

Why does format matter when merging? A few reasons. If your combined data goes past 65,536 rows, you have to output as XLSX or you'll lose data. You might also have a mix of XLS and XLSX files from different sources, so you need a tool that handles both. And the output format determines what software can open the 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 Our Free Online Excel Merger Tool (No Code Required)

This is the fastest option if you just need to get it done. No installation, no coding, and your data stays completely private because everything runs in your browser.

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

Here's how it works:

1. Go to our Excel Merger Tool
2. Drag and drop your Excel files (.xlsx, .xls, or .csv)
3. You'll see file details like sheet count, row count, and file size
4. Pick your merge strategy:
   • Append Rows stacks all data into one sheet (ideal when files have the same columns)
   • Separate Sheets keeps each file on its own sheet in the output workbook
   • Merge by Header groups files that share the same column headers together
5. Choose your output format (XLSX, XLS, or CSV)
6. Toggle "Skip Duplicate Headers" if you're appending rows
7. Hit "Merge Excel Files"
8. Preview the result in an interactive table
9. Download the merged file

The tool processes everything locally using your browser, so your spreadsheet data never gets uploaded to any server. It handles both XLS and XLSX input, shows you sheet and row counts for each uploaded file, and lets you preview the merged output before downloading. There's no file limit beyond what your browser memory can handle.

This is the option I'd recommend for business analysts, office workers, or anyone who just wants a quick merge without learning Python or VBA.

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:

Bash
Copy
pip install pandas openpyxl xlrd

(

openpyxl

xlrd

Basic: Append All Files Into One Sheet

Python
Copy
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

Python
Copy
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

Python
Copy
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.,
   Copy

   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:

vba
Copy
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
   Copy

   Alt + F11

   to open the VBA Editor
3. Go to Insert, then Module
4. Paste the code above
5. Update the
   Copy

   FolderPath

   variable to point to your folder
6. Press
   Copy

   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):

Bash
Copy
# 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):

Bash
Copy
# 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:

Copy
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:

Copy
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:

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

Best Practices for Merging Excel Files

After doing this enough times, I've learned a few things the hard way:

Standardize headers first. This is the number one cause of merge problems. "First Name" vs "FirstName" vs "first_name" will be treated as three different columns. Get everyone on the same naming convention before you start.

Clean data beforehand. Remove blank rows, summary totals at the bottom, and any footer text from individual files before merging. These will show up as garbage rows in your merged output.

Check data types. If one file has text in a column and another has numbers in the same column, Excel might misformat the merged data. Consistency matters.

Use XLSX for output. Always choose XLSX when your merged data could exceed 65,536 rows. That's the XLS limit, and if you hit it, data gets silently dropped.

Back up originals. Keep copies of all source files before you start. You never know when you might need to re-merge with different settings.

Verify row counts. After merging, add up the rows from all source files and compare against the merged result. If something doesn't match, investigate before moving forward.

Review the preview. If your tool offers a preview, use it. Catching alignment issues before you download saves a lot of headaches.

Handle dates carefully. Different regional settings format dates differently. One file might have MM/DD/YYYY while another uses DD/MM/YYYY. Standardize date formats before merging.

Consider file size. Very large merged files can be slow to open in Excel. If your merged output is hundreds of megabytes, consider splitting it into logical chunks or using a database instead.

Document your process. Note which files were merged, what strategy you used, and any transformations you applied. Future you will thank present you.

Frequently Asked Questions

Can I merge Excel files with different numbers of columns?

Yes, but the approach depends on your strategy. With "Append Rows," missing columns get filled with empty cells. With "Separate Sheets," each file keeps its original structure on its own sheet. Our online tool handles both scenarios without any extra configuration.

Does merging Excel files preserve cell formatting?

Our online tool preserves cell values but not formatting like colors, fonts, or borders. If you need to keep formatting intact, Power Query or VBA macros are better options. For most data merging tasks though, you care about the values, not the styling.

Can I merge password-protected Excel files?

Not directly. You'll need to remove the password first. In Excel, go to File, then Info, then Protect Workbook, then Encrypt with Password, and clear the password field.

How do I merge specific sheets from Excel files?

Use the "Separate Sheets" strategy to import everything, then remove the sheets you don't need. In Python, you can specify which sheet to read with

pd.read_excel(file, sheet_name="Sheet1")

Can I merge Excel files on my phone?

Yes. Our online tool works on mobile browsers including Chrome, Safari, and Firefox. Visit merge-json-files.com/excel-merger on your phone and you can merge files right from your mobile device.

What's the maximum file size I can merge?

With our online tool, the limit depends on your browser's available memory. Modern browsers generally handle files up to 50-100MB without issues. For very large datasets beyond that, Python with pandas is the way to go.

Related Tools and Resources

• Excel File Merger Tool for merging XLS and XLSX files online for free
• JSON File Merger for combining JSON data files
• CSV File Merger for merging CSV datasets
• TXT File Merger for combining text files
• JSON to Excel Converter for converting JSON data to Excel format

Final Thoughts

Merging Excel files doesn't have to eat up your afternoon. Whether you're dealing with two spreadsheets or two hundred, the right tool makes all the difference.

For quick, one-off merges, our online Excel merger tool is free, supports both XLS and XLSX, and keeps your data private since everything runs in the browser. For automated or recurring tasks, Python with pandas gives you maximum flexibility and can be scheduled to run on its own. If you prefer staying inside Excel, Power Query and VBA macros both get the job done without leaving the application. And for command-line workflows, csvkit and ssconvert offer scriptable solutions.

The key to a successful merge is picking the right strategy (append vs. separate sheets vs. header matching), making sure your column headers are consistent, and always double-checking the merged output before putting it to use.

Try our Excel File Merger Tool for free, browser-based merging that supports XLS and XLSX.

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