Fix Wrong Sheet Order After Writing with pandas

A report written with pandas has one sheet per month, and they come out as Apr, Aug, Dec, Feb, Jan… Or a summary sheet added to an existing workbook ends up at the far right, after twelve monthly tabs, where nobody will find it. The data is right; the order is wrong, and for a workbook people navigate by tabs that matters almost as much. pandas writes sheets in exactly the order your code writes them, and Excel shows tabs in the order they are stored, so a wrong order always has a cause in the code that produced the sheets — usually one of three. This page reproduces each cause, shows how to write the sheets in the intended order in the first place, how to reorder an existing workbook with openpyxl when that is not possible, and how to make sure the right sheet opens first. It belongs to Troubleshooting Common Python Excel Errors.

Prerequisites

pip install pandas openpyxl

The input is a year of daily sales. The report should have a Summary sheet first, then one sheet per month from January to December.

Cause 1 — Group keys that sort alphabetically

import pandas as pd

sales = pd.read_excel("daily_sales.xlsx")
sales["Month"] = sales["Date"].dt.strftime("%b")

with pd.ExcelWriter("by_month_wrong.xlsx") as xw:
    for month, g in sales.groupby("Month"):
        g.to_excel(xw, sheet_name=month, index=False)
with pd.ExcelFile("by_month_wrong.xlsx") as f:
    print(f.sheet_names)

groupby sorts its keys by default, and month names are strings, so they sort alphabetically: April, August, December, February and so on. The sheets are written in that order and Excel shows them in that order. The output shows exactly that: Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar, May, Nov, Oct, Sep — every month present, none in the right place. The fix is to group by something that sorts in time — the month number or a monthly period — and use the name only for the sheet title:

sales["Period"] = sales["Date"].dt.to_period("M")
with pd.ExcelWriter("by_month.xlsx") as xw:
    for period, g in sales.groupby("Period"):
        g.drop(columns=["Month", "Period"]).to_excel(xw, sheet_name=period.strftime("%b %Y"), index=False)
with pd.ExcelFile("by_month.xlsx") as f:
    print(f.sheet_names)

A monthly Period sorts chronologically and also carries the year, which avoids a second problem when the data spans more than one year: two sheets called Jan for different years. The same applies to any categorical order that is not alphabetical — sizes, priority levels, regions in a fixed reporting order: group by an ordered key, such as an ordered Categorical, or loop over an explicit list.

Cause 2 — Sets and unordered collections

regions = {"North", "South", "East", "West"}            # a set: no defined order
print(list(regions))
ORDER = ["North", "South", "East", "West"]               # the order the business reports in
print([r for r in ORDER if r in regions])

Sheet names collected in a set — often from set(df["Region"]) or df["Region"].unique() passed through a set to remove duplicates — come back in an order that has nothing to do with how the business reads the report, and string sets can even change order between runs, because Python randomises string hashing per process. Dictionaries keep insertion order, so a dictionary built in the right order is safe; a set is not. When the order matters, loop over an explicit list and keep only the names that occur in the data, as the second line does — the result is the same on every run, and a region that appears in the data but not in the list can be reported rather than placed somewhere arbitrary.

Cause 3 — Appending to an existing workbook

summary = sales.groupby("Period")["Revenue"].sum().rename_axis("Month").reset_index()
summary["Month"] = summary["Month"].dt.strftime("%b %Y")
with pd.ExcelWriter("by_month.xlsx", mode="a", engine="openpyxl", if_sheet_exists="replace") as xw:
    summary.to_excel(xw, sheet_name="Summary", index=False)
with pd.ExcelFile("by_month.xlsx") as f:
    print(f.sheet_names[:3], "…", f.sheet_names[-2:])

mode="a" opens an existing workbook and adds to it, and a new sheet goes at the end — so a summary added to a finished monthly workbook lands after December. pandas has no option to insert a sheet at a position. (Replacing a sheet that already exists with if_sheet_exists="replace" keeps it where it was in current pandas versions; it is new sheets that are added at the end.) The simplest fix is to write the workbook in one pass with the summary first. When the workbook already exists and must be updated rather than rewritten, reorder it afterwards.

Step 4 — Reorder an existing workbook with openpyxl

from openpyxl import load_workbook

wb = load_workbook("by_month.xlsx")
wb.move_sheet("Summary", offset=-wb.sheetnames.index("Summary"))      # move to the front
wb.active = 0                                                          # open on the summary
for ws in wb.worksheets:
    ws.sheet_view.tabSelected = ws is wb.active                        # only one tab selected
wb.save("by_month.xlsx")
print(load_workbook("by_month.xlsx").sheetnames[:4])

move_sheet moves one sheet by an offset, so moving it to the front is an offset of minus its current position. For a full reorder — say, a desired list of names — sorting the workbook's internal list by position in that list does it in one line, wb._sheets.sort(key=desired.index); the underscore marks it as internal to openpyxl, but it is the documented way people reorder many sheets at once and it is stable across versions. After moving sheets, set wb.active to the sheet the reader should see first, and make sure only that tab is marked selected: a workbook saved with several tabs selected opens in Excel with the sheets grouped, and anything typed then goes into all of them.

With XlsxWriter, and when other code reads by position

With the XlsxWriter engine the same rule holds even more strictly: sheets appear in the order they are added, and because XlsxWriter only writes new files, there is no reordering afterwards — the fix is always to add them in the right order. Its activate() method chooses the sheet that opens first, and set_first_sheet() chooses which tab is scrolled into view at the left of the tab bar, which matters in a workbook with more tabs than fit on screen.

Sheet order also matters to code that reads the workbook later. pd.read_excel(path, sheet_name=0) reads the first sheet, whatever it is called, so moving a summary to the front silently changes what such a script reads. Downstream code should read sheets by name — sheet_name="Summary" — which keeps working whatever order the tabs end up in, and is one more reason to fix the order deliberately rather than leave it to chance. Hidden sheets keep their position in the order too, so a hidden lookup sheet at the front would become sheet 0 for any script that reads by position.

Step 5 — Test the order

expected = ["Summary"] + [p.strftime("%b %Y") for p in pd.period_range("2026-01", "2026-12", freq="M")]
actual = load_workbook("by_month.xlsx", read_only=True).sheetnames
assert actual == expected, f"sheet order wrong: {actual}"
print("sheet order OK:", actual[:3], "…", actual[-1])

Sheet order is easy to break again later — someone adds a sheet in the middle of the code, or changes a group key — so it is worth a one-line assertion in the report's tests: the expected list of sheet names, built from the same rules as the report, compared with the workbook's sheetnames. Opening the file in read-only mode makes the check fast even for large workbooks. The broader approach to testing report output is in Test Excel Output with pytest.

Common pitfalls

• Grouping by month names. Group by a period or month number; use the name only as the title.
• Collecting sheet names in a set. Loop over an explicit, ordered list.
• Appending a summary with mode="a". It goes last; write in one pass or reorder.
• Forgetting the active sheet. Set wb.active after reordering.
• Several tabs selected. Clear tabSelected on all but the active sheet.

Conclusion

pandas writes sheets in the order the code writes them, so a wrong tab order comes from the code: alphabetical group keys such as month names, unordered sets of names, or sheets appended to an existing workbook. Group by periods or loop over an explicit list to write in the right order, write the summary first in a single pass, or reorder an existing workbook with openpyxl's move_sheet or by sorting its sheet list; then set the active sheet and assert the order in a test.

Frequently asked questions

Why are my monthly sheets in alphabetical order?groupby sorts month names as text; group by a monthly period or month number instead.

How do I move a sheet to the front with openpyxl? Use wb.move_sheet("Summary", offset=-wb.sheetnames.index("Summary")).

Why does a new sheet appear at the end when I append with pandas?ExcelWriter(mode="a") adds new sheets after the existing ones; reorder with openpyxl afterwards or write the file in one pass.

How do I choose which sheet opens first? Set wb.active to the sheet's index and make sure only that sheet has tabSelected set.

Related

• Up one level: Troubleshooting Common Python Excel Errors — common errors and fixes.
• Rename, Reorder and Delete Excel Sheets with openpyxl — sheet management.
• Create a Sheet for Each Group in a DataFrame — one sheet per group.
• Write Multiple DataFrames to One Excel File — writing in one pass.