Merge Multiple Excel Files into One Workbook Using Python

Working with multiple Excel files can quickly become tedious when you're tasked with consolidating them into one file. Fortunately, Python offers a fast and efficient way to automate this process.

In this tutorial, we'll learn how to merge several Excel files—each containing a single worksheet—into a single workbook, with each file becoming a separate sheet.

Use Case

Imagine you receive monthly sales reports from multiple branches. Each branch sends its own Excel file. You want to merge them all into one workbook where:

Each branch’s data is a separate sheet.
The sheet names are based on the original filenames.

Requirements

We'll use the following Python libraries:

openpyxl: For reading and writing Excel files (XLSX format).

pandas: For reading Excel data easily.

glob: For finding Excel files in a directory.

os: To extract filenames for sheet names.

Step 1: Install Python

First, ensure Python is installed on your system.

For Windows/macOS/Linux:

Download Python from the official website:

https://www.python.org/downloads/

Then verify the installation:

c:\user\username> python --version

Python 3.11.9

Step 2: Install Required Dependencies

We will use the xlwings library to manipulate Excel files while maintaining full formatting.

Open your terminal or command prompt and run:

c:\user\username> pip install xlwings

Tip: You can also create a virtual environment for better project management.

python -m venv env

source env/bin/activate # On Windows: env\Scripts\activate

pip install xlwings

Step 3: Prepare Your Excel File

Here’s a full script to export each worksheet as a separate Excel file:

import os

import glob

import pandas as pd

from openpyxl import Workbook

# Step 1: Set your directory path

folder_path = "excel_files" # Replace with your folder containing Excel files

output_file = "merged_workbook.xlsx"

# Step 2: Get all Excel files in the directory

excel_files = glob.glob(os.path.join(folder_path, "*.xlsx"))

# Step 3: Create a new Excel writer

with pd.ExcelWriter(output_file, engine='openpyxl') as writer:

for file in excel_files:

# Extract filename without extension to use as sheet name

sheet_name = os.path.splitext(os.path.basename(file))[0]

# Read the first sheet of the current Excel file

df = pd.read_excel(file, sheet_name=0)

# Write to new workbook

df.to_excel(writer, sheet_name=sheet_name, index=False)

print(f"Successfully merged {len(excel_files)} Excel files into '{output_file}'")

By automating Excel merging with Python, you can save hours of manual copy-pasting. This is perfect for financial reports, department overviews, or aggregating form responses.

Merge Multiple Excel Files into a Single Workbook Using Python

Use Case

Requirements

Step 1: Install Python

Step 2: Install Required Dependencies

Step 3: Prepare Your Excel File