Multiple Excel Sheets

Why Multiple Sheets?

Excel workbooks can have many sheets (tabs) in one file. This helps organize related data separately.

Real-world examples:

Monthly reports: Jan, Feb, Mar sheets
Department data: Sales, Marketing, HR sheets
Data pipeline: Raw Data, Cleaned Data, Summary sheets

Listing All Sheets

See what sheets exist in a workbook.

code.py

import openpyxl

workbook = openpyxl.load_workbook("report.xlsx")
sheets = workbook.sheetnames
print("Available sheets:", sheets)

Shows something like: ['Sales', 'Expenses', 'Summary']

Accessing Different Sheets

code.py

import openpyxl

workbook = openpyxl.load_workbook("report.xlsx")

sales_sheet = workbook["Sales"]
expenses_sheet = workbook["Expenses"]

print("Sales data:")
for row in sales_sheet.iter_rows(values_only=True):
    print(row)

print("Expenses data:")
for row in expenses_sheet.iter_rows(values_only=True):
    print(row)

What this does: Reads data from two different sheets in the same workbook.

Looping Through All Sheets

code.py

import openpyxl

workbook = openpyxl.load_workbook("report.xlsx")

for sheet_name in workbook.sheetnames:
    sheet = workbook[sheet_name]
    print("Sheet:", sheet_name)
    print("Rows:", sheet.max_row)
    print("Columns:", sheet.max_column)
    print()

What this does: Shows information about each sheet.

Creating Multiple Sheets

code.py

import openpyxl

workbook = openpyxl.Workbook()

workbook.active.title = "January"

feb_sheet = workbook.create_sheet("February")
mar_sheet = workbook.create_sheet("March")

workbook["January"].append(["Date", "Sales"])
workbook["January"].append(["2024-01-15", 5000])

workbook["February"].append(["Date", "Sales"])
workbook["February"].append(["2024-02-10", 6000])

workbook.save("monthly_sales.xlsx")

What this creates: One workbook with three sheets, each with its own data.

Copying Data Between Sheets

code.py

import openpyxl

workbook = openpyxl.load_workbook("data.xlsx")

source_sheet = workbook["Raw Data"]
summary_sheet = workbook.create_sheet("Summary")

summary_sheet.append(["Product", "Total"])

for row in source_sheet.iter_rows(min_row=2, values_only=True):
    product = row[0]
    quantity = row[1]
    price = row[2]
    total = quantity * price
    summary_sheet.append([product, total])

workbook.save("data.xlsx")

What this does: Reads from one sheet, processes data, writes to another sheet.

Combining Data from Multiple Sheets

code.py

import openpyxl

workbook = openpyxl.load_workbook("quarterly.xlsx")

all_data = []

for sheet_name in ["Q1", "Q2", "Q3", "Q4"]:
    sheet = workbook[sheet_name]

    for row in sheet.iter_rows(min_row=2, values_only=True):
        all_data.append(row)

summary_sheet = workbook.create_sheet("Year Summary")
summary_sheet.append(["Product", "Sales"])

for row in all_data:
    summary_sheet.append(row)

workbook.save("quarterly.xlsx")

What this does: Collects data from Q1-Q4 sheets and combines into Year Summary sheet.

Practice Example

The scenario: You have monthly sales in separate sheets. Create a summary sheet with totals.

code.py

import openpyxl

workbook = openpyxl.load_workbook("sales.xlsx")

monthly_totals = {}

for sheet_name in workbook.sheetnames:
    if sheet_name != "Summary":
        sheet = workbook[sheet_name]
        total = 0

        for row in sheet.iter_rows(min_row=2, values_only=True):
            amount = row[2] if len(row) > 2 else 0
            if amount:
                total = total + amount

        monthly_totals[sheet_name] = total

if "Summary" in workbook.sheetnames:
    del workbook["Summary"]

summary = workbook.create_sheet("Summary")
summary.append(["Month", "Total Sales"])

for month, total in monthly_totals.items():
    summary.append([month, total])

grand_total = sum(monthly_totals.values())
summary.append(["Grand Total", grand_total])

workbook.save("sales.xlsx")
print("Summary created")

What this does:

Loops through all sheets except Summary
Calculates total for each sheet
Deletes old Summary if exists
Creates new Summary sheet
Writes monthly totals
Adds grand total

Key Points to Remember

Access sheets by name with workbook["SheetName"]. Loop through all sheets with workbook.sheetnames.

Create new sheets with create_sheet("Name"). Set sheet position with second parameter.

Each sheet is independent. You can read from one and write to another.

Common Mistakes

Mistake 1: Wrong sheet name

code.py

sheet = workbook["Sales"]  # KeyError if sheet doesn't exist

Check sheetnames first.

Mistake 2: Not handling missing sheets

code.py

for month in ["Jan", "Feb", "Mar"]:
    sheet = workbook[month]  # Error if sheet missing

Check if sheet exists:

code.py

if month in workbook.sheetnames:
    sheet = workbook[month]

What's Next?

You now know how to work with multiple sheets. Next, you'll learn about SQL database connections - connecting Python to databases.