openpyxl: Create, Rename, Order & Delete Worksheets

Create a Workbook & Add Worksheets

from openpyxl import Workbook

wb = Workbook()  # New workbook
ws = wb.active             # Default worksheet
ws.title = "Main"       # Rename default sheet

# Create additional sheets (appended at end by default)
wb.create_sheet("Summary")
wb.create_sheet("Data")

# Create a sheet at a specific position (index 0 = first)
wb.create_sheet("Intro", 0)

wb.save("D:\\\\openpyxl_sheets_demo.xlsx")

Access Worksheets by Name or Index

from openpyxl import load_workbook

wb = load_workbook("D:\\\\openpyxl_sheets_demo.xlsx")

# By name
ws_main = wb["Main"]

# By index (sheetnames returns a list of titles)
first_title = wb.sheetnames[0]
first_ws    = wb[first_title]

print("All sheets:", wb.sheetnames)

Rename Worksheets

# Change an existing sheet title
ws_ren = wb["Summary"]
ws_ren.title = "Overview"
print("All sheets:", wb.sheetnames)
wb.save("D:\\\\openpyxl_sheets_demo.xlsx")

Reorder (Move) Worksheets

# Move a sheet to the front
print("Before Move all sheets:", wb.sheetnames)
ws_data = wb["Data"]
wb.move_sheet(ws_data, -2)  # negative moves left, positive moves right

print("New order:", wb.sheetnames)
wb.save("D:\\\\openpyxl_sheets_demo.xlsx")

Delete Worksheets

# Remove a worksheet (ensure it's not the only one)
print("Before delete  all sheets:", wb.sheetnames)
ws_intro = wb["Intro"]
wb.remove(ws_intro)

print("After delete:", wb.sheetnames)
wb.save("D:\\\\openpyxl_sheets_demo.xlsx")

List All Worksheet Names

for title in wb.sheetnames:
    print(title)

Monthly Sheet Generator (Practical Demo)

from openpyxl import Workbook

months = ["Jan","Feb","Mar","Apr","May","Jun",
            "Jul","Aug","Sep","Oct","Nov","Dec"]

wb = Workbook()
wb.active.title = "Summary"

for m in months:
    ws = wb.create_sheet(m)
    # Optional: set a header row
    ws["A1"] = "Date"
    ws["B1"] = "Note"

wb.save("D:\\\\monthly_report.xlsx")

Copy (Duplicate) a Worksheet

# Duplicate an existing sheet (structure & values)
source = wb["Apr"]
dup    = wb.copy_worksheet(source)
dup.title = "Apr_Copy"

wb.save("D:\\\\openpyxl_sheets_demo.xlsx")

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: