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

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

← Subscribe to our YouTube Channel here