How to Merge Multiple Excel Files in Python for Reporting

Tired of spending hours manually copying data between Excel files for your quarterly reports? If you're a developer or analyst working with Excel workbooks, you've likely encountered this: multiple files, multiple sheets, scattered data that needs to be consolidated into one report. It's tedious, error-prone, and wastes valuable time.

The Manual Way (And Why It Breaks)

Most people deal with this by copying and pasting data between Excel files, often opening and closing dozens of workbooks. You end up clicking through sheets, selecting ranges, and trying to keep everything aligned. This method is incredibly slow and prone to mistakes—especially when dealing with large files or repetitive tasks. It also breaks down when Excel APIs are involved, or when you hit limits on manual interaction. When deadlines loom, the last thing you want is to spend hours on a task that can be automated.

The Python Approach

Here’s a simple Python script that demonstrates how you might merge a couple of Excel files manually. It uses pandas and openpyxl to read and concatenate data from multiple sheets:

import pandas as pd
from pathlib import Path

def merge_excel_files(file_list, output_file):
    all_data = []

    for file in file_list:
        # Read all sheets from each Excel file
        sheets = pd.read_excel(file, sheet_name=None)
        for sheet_name, df in sheets.items():
            df['Source_File'] = Path(file).name
            df['Sheet_Name'] = sheet_name
            all_data.append(df)

    # Combine all DataFrames
    merged_df = pd.concat(all_data, ignore_index=True)

    # Save to a new Excel file
    merged_df.to_excel(output_file, index=False)

    print(f"Combined {len(file_list)} files into {output_file}")

# Example usage:
files = ['q1_sales.xlsx', 'q2_sales.xlsx']
merge_excel_files(files, 'annual_report.xlsx')

This code reads multiple files, extracts all sheets, adds metadata for tracking, and combines everything into one final Excel file. It’s easy to understand, but lacks features like error handling, support for .xls files, or a CLI for batch processing.

What the Full Tool Handles

The tool we're featuring, Spreadsheet File Merger for Reporting, addresses all the limitations of a DIY script:

• Handles both .xlsx and .xls files
• Supports merging all sheets or selecting specific ones
• Preserves original formatting and data types
• Works efficiently with large datasets
• Provides a clean command-line interface for batch operations
• Offers intelligent error handling and logging

Running It

Using the tool is simple. After importing the module, you can merge multiple files like this:

import excel_merger

merged_df = excel_merger.merge(['sales_q1.xlsx', 'sales_q2.xlsx'])
merged_df.to_excel('annual_report.xlsx', index=False)

You can also pass a list of files, or even use a glob pattern for batch processing. The tool will automatically detect and merge all sheets, or you can specify which ones to include. The output is a clean, organized DataFrame that you can write directly to Excel.

Results

By using this tool, you cut down the time spent on data consolidation from hours to minutes. You get a single, clean Excel report without manual copy-paste errors, and the tool handles edge cases you might not think of. It's ideal for generating reports, dashboards, or datasets that pull from multiple sources.

Get the Script

If you're tired of building this yourself, skip the development and use the ready-made solution. This is the polished version of what you just read, designed for real-world reporting tasks.

Download Spreadsheet File Merger for Reporting →

$29 one-time. No subscription. Works on Windows, Mac, and Linux.

Built by OddShop — Python automation tools for developers and businesses.