Merge Excel Worksheets into One Sheet Using Python

Introduction

In our daily office work, we often deal with Excel files that contain many worksheets. Each sheet may have similar data like student marks, sales details, or reports. Manually copying and pasting all these into one sheet is boring, time-consuming, and sometimes leads to mistakes.

The good news is — with Python automation, you can merge all Excel worksheets into one sheet in just a few seconds!

In this article, I will show you a simple and practical way to combine multiple Excel sheets using Python and openpyxl, even if you are a beginner.

Why Should We Use Python for This?

You might ask, “Why not do this manually in Excel?”

Here’s why Python is better:

It saves a lot of time
No copy-paste mistakes
Works smoothly even for big files
You can reuse the same code again and again
It looks professional and smart 😎

Once you learn this, you’ll never go back to manual merging again!

What You Need Before Starting

Just two simple things:

Python installed on your computer
The openpyxl library

Install openpyxl using this command:

pip install openpyxl

Make sure your Excel file is in the same folder as your Python file.

Example Excel File

Let’s assume your Excel file has these sheets:

Sheet1
Sheet2
Sheet3

All sheets have similar columns like:

Roll No | Student Name | Marks

Our goal is to merge all of them into a single sheet.

Simple Python Code to Merge All Worksheets

Here is a clean and easy Python script to do that:

from openpyxl import load_workbook


wb = load_workbook("Studentscombine.xlsx")

merged = wb.create_sheet(title="Merged_Data")

for sheet_name in wb.sheetnames:
    if sheet_name != "Merged_Data":
        ws = wb[sheet_name]
        for row in ws.iter_rows(values_only=True):
            merged.append(row)


wb.save("Merged_Result.xlsx")

print("All worksheets merged successfully!")

How This Code Works (In Simple Words)

It opens your Excel file
Creates a new sheet called Merged_Data
Reads data from all sheets
Copies everything into the new sheet
Saves a new Excel file

Very simple and very powerful!

Avoid Repeating Header Row

If all sheets have the same header (like Name, Subject, Marks), you may want to avoid repeating them again and again.

Use this improved version:

from openpyxl import load_workbook

wb = load_workbook("Studentscombine.xlsx")
merged = wb.create_sheet(title="Merged_Data")

first_sheet = True

for sheet_name in wb.sheetnames:
    if sheet_name != "Merged_Data":
        ws = wb[sheet_name]
        for i, row in enumerate(ws.iter_rows(values_only=True)):
            if i == 0 and not first_sheet:
                continue
            merged.append(row)
        first_sheet = False

wb.save("Merged_Result.xlsx")
print("Merged without duplicate headers!")

Merge Multiple Excel Worksheets into One Sheet Using Python (openpyxl)