Fix "This sheet is too large" When Writing Excel with pandas

Exporting a big DataFrame ends with:

ValueError: This sheet is too large! Your sheet size is: 1500000, 4 Max sheet size is: 1048576, 16384

The message is precise. An Excel worksheet is a fixed grid of 1,048,576 rows by 16,384 columns, and pandas checks the frame's shape against it before writing. The first number pair is your frame — rows, then columns — and the second is the limit. The fix depends on who needs the data and why, so answer that before reaching for code. This guide, part of Troubleshooting Common Python Excel Errors, covers splitting long and wide frames, a boundary case where rows are lost with no error at all, and when to stop using Excel for the job.

Prerequisites

pip install pandas xlsxwriter pyarrow

The examples use a modest frame and a deliberately tiny "limit" so they run in seconds; the logic is the same at full size.

import numpy as np
import pandas as pd

MAX_ROWS = 1_048_576          # rows per worksheet, header included
MAX_COLS = 16_384

df = pd.DataFrame({"id": np.arange(25), "value": np.arange(25) * 1.5})

Step 1 — Mind the header: the silent off-by-one

pandas checks len(df) — the data rows — against 1,048,576. But to_excel also writes a header row, so the real capacity is one row less. A frame of exactly 1,048,576 rows therefore passes the check and does not fit. With the openpyxl engine the write then fails with a different message (Row numbers must be between 1 and 1048576); with the xlsxwriter engine it succeeds, and the last data row is simply not in the file. No error, no warning.

Check the real capacity yourself before writing, counting every row the export will occupy:

def rows_needed(frame: pd.DataFrame, header: bool = True, startrow: int = 0) -> int:
    header_rows = frame.columns.nlevels if header else 0
    return startrow + header_rows + len(frame)

def fits_one_sheet(frame: pd.DataFrame, **kwargs) -> bool:
    return rows_needed(frame, **kwargs) <= MAX_ROWS and frame.shape[1] <= MAX_COLS

print(fits_one_sheet(df))                                   # True
print(rows_needed(pd.DataFrame(index=range(MAX_ROWS))))     # 1048577 -> does not fit

The helper also accounts for startrow and for MultiIndex column headers, which take more than one row. Put this check in front of every export that can grow, and route oversized frames to Step 2 or Step 4.

Step 2 — Split long frames across sheets

When one reader needs everything in one workbook, split the rows into blocks that each fit under a header, and name the sheets so the order is obvious:

def write_split(frame: pd.DataFrame, path: str, base: str = "data", per_sheet: int = MAX_ROWS - 1) -> list[str]:
    names = []
    with pd.ExcelWriter(path, engine="xlsxwriter") as writer:
        for part, start in enumerate(range(0, len(frame), per_sheet), start=1):
            name = f"{base}_{part}"
            frame.iloc[start:start + per_sheet].to_excel(writer, sheet_name=name, index=False)
            names.append(name)
    return names

names = write_split(df, "split.xlsx", per_sheet=10)       # tiny blocks to show the split
print(names)                                               # ['data_1', 'data_2', 'data_3']
back = pd.concat(pd.read_excel("split.xlsx", sheet_name=None).values(), ignore_index=True)
print(len(back) == len(df))                                # True

The read-back check is worth keeping at full size: it proves no row was lost at a boundary. For very large splits, add engine_kwargs={"options": {"constant_memory": True}} to the writer so xlsxwriter streams rows to disk instead of holding the whole workbook in memory.

Step 3 — Wide frames: the column limit

The second limit is 16,384 columns. Frames that wide usually come from a pivot with a high-cardinality column — one column per customer or per day for years. Two fixes: pivot the long dimension onto the rows instead, or split the columns into groups across sheets while repeating the key column on each:

wide = pd.DataFrame(np.ones((3, 40)), columns=[f"day_{i:03}" for i in range(40)])
wide.insert(0, "store", ["A", "B", "C"])

def split_columns(frame, key, per_sheet, path):
    value_cols = [c for c in frame.columns if c != key]
    with pd.ExcelWriter(path, engine="xlsxwriter") as writer:
        for n, start in enumerate(range(0, len(value_cols), per_sheet), start=1):
            cols = [key] + value_cols[start:start + per_sheet]
            frame[cols].to_excel(writer, sheet_name=f"cols_{n}", index=False)

split_columns(wide, "store", per_sheet=15, path="wide.xlsx")
print(pd.ExcelFile("wide.xlsx").sheet_names)   # ['cols_1', 'cols_2', 'cols_3']

Most of the time, though, a frame with thousands of columns is better melted back to a long table — one row per store and day — which is easier to filter and pivot in Excel anyway; see Unpivot a Wide Excel Sheet with pandas melt.

Step 4 — Summarise in Excel, deliver detail elsewhere

A million-row sheet is technically possible and practically poor: it takes a long time to open, filters are sluggish, and nobody reads it row by row. Ask what the reader does with the data. Usually they need a summary — totals by month, top customers, exceptions — and the full detail only for occasional lookups or for loading into another tool. Give them both, each in the right format:

detail = pd.DataFrame({
    "month": np.repeat(pd.period_range("2026-01", periods=5, freq="M").astype(str), 5),
    "amount": np.arange(25) * 10.0,
})
detail.to_parquet("detail.parquet", index=False)          # full data, compact and typed
detail.to_csv("detail.csv", index=False)                  # full data, opens anywhere

summary = detail.groupby("month", as_index=False)["amount"].sum()
with pd.ExcelWriter("summary.xlsx", engine="xlsxwriter") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)
    writer.sheets["Summary"].write(len(summary) + 2, 0, "Full detail: detail.parquet / detail.csv")
print(summary)

Parquet keeps types and compresses well, and pandas, Polars, DuckDB and Power BI read it directly; CSV opens anywhere, though Excel itself will only load the first 1,048,576 rows of it. The trade-offs are covered in Excel vs CSV vs Parquet for Python Data Pipelines.

Step 5 — Guard the export in production

The error tends to appear months after a report goes live, when the data grows past the limit. A guard that checks size before writing and chooses the delivery accordingly turns a crash into a controlled change of format:

def export(frame: pd.DataFrame, path_stem: str) -> str:
    if fits_one_sheet(frame):
        frame.to_excel(f"{path_stem}.xlsx", index=False)
        return f"{path_stem}.xlsx"
    if frame.shape[1] <= MAX_COLS and len(frame) <= 5 * (MAX_ROWS - 1):
        write_split(frame, f"{path_stem}.xlsx")
        return f"{path_stem}.xlsx (split)"
    frame.to_parquet(f"{path_stem}.parquet", index=False)
    return f"{path_stem}.parquet"

print(export(df, "orders"))     # orders.xlsx

Log which branch was taken. When a report switches from one sheet to a split workbook, the people who receive it should hear about it from you, not discover it.

Common pitfalls

• Forgetting the header row. Capacity under a header is 1,048,575 rows, not 1,048,576. Check with the full row count, as in Step 1.
• Legacy .xls. The old format holds 65,536 rows and 256 columns. Writing large data to .xls is never the right choice.
• Memory while writing. Large exports can exhaust memory before they hit the row limit. Use xlsxwriter's constant-memory mode; see Write a Million Rows to Excel with xlsxwriter.
• Blank rows between split sheets. Readers who copy the sheets back together by hand miss rows at the boundaries. Tell them the split exists — name the sheets clearly and add a contents sheet listing each block's first and last key.
• Index as an extra column. The default index=True adds a column, which matters only near the column limit but always adds noise. Pass index=False unless the index is data.

Conclusion

"This sheet is too large" means the frame exceeds Excel's grid of 1,048,576 rows by 16,384 columns. Count the header when checking — a frame of exactly 1,048,576 rows loses its last row silently with xlsxwriter — then split long frames across sheets, melt or split wide ones, and deliver very large detail as CSV or Parquet with an Excel summary. Guard production exports so growth changes the format deliberately instead of crashing the job.

Frequently asked questions

What are Excel's size limits? A worksheet has 1,048,576 rows and 16,384 columns (A to XFD). With a header row, at most 1,048,575 data rows fit under it.

Why did my export lose its last row without an error? pandas compares the number of data rows with the sheet limit and forgets the header. A frame of exactly 1,048,576 rows passes the check; with the xlsxwriter engine the header plus all but the last row are written and the last row is dropped silently.

Should I split the data across sheets or files? Split across sheets when one person needs all of it in one workbook. Split across files, or use CSV or Parquet, when the data feeds another system — Excel with over a million rows per sheet is slow to open and hard to use.

Can I raise Excel's row limit? No. The grid size is fixed by the file format in every current version of Excel. Power Pivot and Power Query can hold larger tables in the data model, but a worksheet cannot show more than 1,048,576 rows.

Does the limit apply to CSV files? No. A CSV file has no row limit, but Excel will only load the first 1,048,576 rows when opening it and warn that the rest were not loaded.

Related

• Up one level: Troubleshooting Common Python Excel Errors — every common error explained.
• Working with Large Excel Files in Python — reading and writing big workbooks.
• Convert CSV Files to Excel with Python — the same limit on the way in.
• Write Large DataFrames to Excel with write_only Mode — memory-friendly writing with openpyxl.