Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Read a Large Excel File in Chunks with pandas

pandas has no chunksize for Excel — build one with an openpyxl row generator, aggregate chunk by chunk, and keep peak memory flat on files with hundreds of thousands of rows.

pd.read_csv takes a chunksize and hands you an iterator; pd.read_excel does not, and the reason is structural rather than an oversight. A CSV is a stream of lines, while an .xlsx sheet is zipped XML that has to be parsed before any row is addressable. Chunking Excel therefore means building the iterator yourself. This guide, part of Working with Large Excel Files in Python, shows the pattern and the aggregation rules that go with it.

Streaming rows into fixed-size DataFrame chunks openpyxl yields rows one at a time from the sheet. A buffer collects fifty thousand of them, converts the batch into a DataFrame, aggregates it, adds the result to a running total, and discards the chunk before filling the buffer again. openpyxl one row at a time buffer 50,000 rows DataFrame chunk transform, group running total small and constant chunk discarded, buffer refills — peak memory stays at one chunk Read once, in order, and never hold more than one batch.

Prerequisites

Bash
pip install pandas openpyxl

Why not skiprows and nrows?

The obvious approach looks like paging, and it is a trap:

Python
import pandas as pd

# Works, but each call re-parses the whole sheet from the beginning
page1 = pd.read_excel("large.xlsx", sheet_name="Orders", skiprows=0, nrows=50_000)
page2 = pd.read_excel("large.xlsx", sheet_name="Orders", skiprows=50_000, nrows=50_000, header=None)

Each read_excel opens the archive, parses the sheet XML and then throws away the rows it was told to skip. Eight pages means eight full parses, so the "chunked" version is slower than reading the file once. It is fine for grabbing a sample from the top; it is not a way to process a large file.

The chunk generator

Build the iterator on openpyxl's streaming reader instead. One parse, one row at a time, batched into DataFrames:

Python
import pandas as pd
from openpyxl import load_workbook

def excel_chunks(path, sheet, size=50_000, columns=None):
    """Yield DataFrames of at most `size` rows from a worksheet."""
    wb = load_workbook(path, read_only=True, data_only=True)
    try:
        ws = wb[sheet]
        rows = ws.iter_rows(values_only=True)
        header = [str(h).strip() if h is not None else "" for h in next(rows)]
        wanted = columns or header
        picks = [header.index(c) for c in wanted]

        buffer = []
        for row in rows:
            if all(v is None for v in row):
                continue                          # skip fully blank rows
            buffer.append([row[i] for i in picks])
            if len(buffer) >= size:
                yield pd.DataFrame(buffer, columns=wanted)
                buffer.clear()
        if buffer:
            yield pd.DataFrame(buffer, columns=wanted)
    finally:
        wb.close()

for i, chunk in enumerate(excel_chunks("large.xlsx", "Orders", size=50_000), start=1):
    print(f"chunk {i}: {len(chunk)} rows, {chunk.memory_usage(deep=True).sum() / 1e6:.1f} MB")

Three details make this production-worthy. values_only=True yields plain tuples rather than Cell objects, which is the difference between fast and pointless. The finally block closes the workbook even if the consumer stops early, so a break in the caller cannot leak a file handle. And picking only the columns you asked for keeps each chunk narrow, which matters more than the row count.

Aggregating across chunks

The whole point is to produce something small. Any aggregation that can be combined works — the accumulator is a tiny frame that lives across iterations:

Python
import pandas as pd

running = None
rows_seen = 0

for chunk in excel_chunks("large.xlsx", "Orders",
                          columns=["Order_Date", "Region", "Quantity", "Unit_Price"]):
    chunk["Revenue"] = chunk["Quantity"] * chunk["Unit_Price"]
    chunk["Month"] = pd.to_datetime(chunk["Order_Date"]).dt.to_period("M").astype(str)

    part = chunk.groupby(["Month", "Region"], observed=True).agg(
        Revenue=("Revenue", "sum"),
        Orders=("Revenue", "size"),
        Largest=("Revenue", "max"),
    )
    if running is None:
        running = part
    else:
        running = running.add(part, fill_value=0)
        running["Largest"] = running["Largest"].combine(part["Largest"], max, fill_value=0)
    rows_seen += len(chunk)

summary = running.reset_index().sort_values(["Month", "Region"])
print(f"{rows_seen:,} rows -> {len(summary)} summary rows")

add(..., fill_value=0) handles the case where a group appears in one chunk and not another, which is guaranteed to happen as soon as the data is sorted by anything. Note that Largest needs max rather than addition — combining aggregates correctly means knowing which operator each column takes.

The averages trap

Means do not combine. The average of chunk averages is only correct when every chunk has the same number of rows, and the last chunk never does:

Why averaging chunk averages is wrong A chunk of fifty thousand rows averaging ten and a final chunk of two rows averaging one hundred give a true overall average of ten point zero zero four. Averaging the two chunk means instead gives fifty five, which is wildly wrong. chunk 1 50,000 rows · mean 10 chunk 2 (the remainder) 2 rows · mean 100 sum ÷ count 500,200 ÷ 50,002 = 10.004 ✓ mean of means (10 + 100) ÷ 2 = 55 ✗ Accumulate the numerator and the denominator; divide once, at the end.
Python
import pandas as pd

totals = None
for chunk in excel_chunks("large.xlsx", "Orders", columns=["Region", "Quantity", "Unit_Price"]):
    chunk["Revenue"] = chunk["Quantity"] * chunk["Unit_Price"]
    part = chunk.groupby("Region", observed=True).agg(
        Revenue_Sum=("Revenue", "sum"), Row_Count=("Revenue", "size")
    )
    totals = part if totals is None else totals.add(part, fill_value=0)

totals["Average_Order"] = totals["Revenue_Sum"] / totals["Row_Count"]
print(totals.round(2))

The same reasoning rules out a few other aggregates entirely. Medians, percentiles and exact distinct counts need the whole distribution, so either collect just that one column across the file, or accept an approximation — nunique per chunk summed is an upper bound, not an answer.

Filter early, inside the loop

If the job only needs part of the file, discard rows as soon as they arrive. The filtered rows never join an accumulator and never cost anything again:

Python
import pandas as pd

WINDOW_START = pd.Timestamp("2024-06-01")
kept = []

for chunk in excel_chunks("large.xlsx", "Orders",
                          columns=["Order_Date", "Region", "Quantity", "Unit_Price"]):
    chunk["Order_Date"] = pd.to_datetime(chunk["Order_Date"], errors="coerce")
    recent = chunk[chunk["Order_Date"] >= WINDOW_START]
    if not recent.empty:
        kept.append(recent)

result = pd.concat(kept, ignore_index=True) if kept else pd.DataFrame()
print(f"{len(result):,} row(s) in the window")

Collecting a filtered subset with concat at the end is a legitimate middle ground: peak memory is the size of the result, not of the file. It only works while the filter is selective — if half the rows survive, you are back to loading half the workbook and should reconsider the approach.

Choosing a chunk size

Chunk sizeBehaviour
1,000pandas overhead per chunk dominates; slow
20,000 – 100,000The useful range for most spreadsheet data
500,000Approaching a full load; memory advantage mostly gone
Whole fileUse read_excel and stop reading this page

Tune it by watching peak memory rather than by intuition: a chunk of 50,000 rows with six numeric columns is a few megabytes, while the same chunk with a wide free-text column can be a hundred. The chunk you can afford depends on the columns you kept, which is one more argument for pruning them first.

Which accumulator does each aggregate need?

Before writing the loop, decide what each output column accumulates. Getting this wrong produces numbers that look reasonable and are not:

How each aggregate combines across chunks Sums and counts add. Minima and maxima take the smaller or larger of the two. Means need a sum and a count kept separately. Medians, percentiles and exact distinct counts cannot be combined and need the whole column. aggregate combine with safe to chunk? sum, count, size addition yes min, max element-wise min / max yes mean, weighted average sum and count separately with care median, percentile, exact nunique cannot be combined no — collect the column

For the bottom row there is still a way through: stream just that one column into a list or a NumPy array. A single float column of 400,000 values is about three megabytes, so an exact median is affordable even when the full frame is not.

Common pitfalls and gotchas

SymptomCauseFix
Chunked read slower than a full readskiprows paging, re-parsing each callUse an openpyxl row iterator
Memory still climbsEvery chunk appended to a listAggregate per chunk, keep only the accumulator
Averages slightly wrongChunk means averagedAccumulate sum and count; divide at the end
Groups missing from the resultadd without fill_valuerunning.add(part, fill_value=0)
Too many open files after many runsWorkbook never closedClose it in a finally block
Header row appears in the dataIterator not advanced past row 1Consume the header with next(rows)
Blank rows create NaN-only recordsTrailing formatted rows in the sheetSkip rows where every value is None

Performance and scale notes

One parse of the sheet is unavoidable, so the floor on time is however long openpyxl takes to stream the file — typically a few seconds per hundred thousand rows with values_only=True. Everything you save beyond that comes from doing less per row: fewer columns picked, no per-row Python function calls, and vectorised work on the chunk rather than on individual values. If the same large file is read repeatedly, convert it once to Parquet and read that instead; a chunked Excel read is the right tool for a file you receive once, not for one you query daily.

Conclusion

Excel has no chunksize, so build one: stream rows with openpyxl in read-only mode, batch them into DataFrames of twenty to a hundred thousand rows, and aggregate each batch into a small accumulator. Keep the columns narrow, combine with add(fill_value=0), handle means as sum-and-count, and never hold more than one chunk. Peak memory then depends on your chunk size rather than on the size of the file.

Frequently asked questions

Does read_excel support chunksize? No. chunksize exists for read_csv only, because CSV can be read as a stream. An .xlsx sheet is zipped XML that has to be parsed, so chunking has to be built on a row iterator.

Can I use skiprows and nrows to page through the file? You can, but each call re-parses the sheet from the start, so a ten-page read costs roughly ten full reads. Use it only for sampling.

Which aggregations work across chunks? Sums, counts, minima, maxima and any groupby of those. Means need the sum and the count accumulated separately. Medians and exact distinct counts need all the data at once.

What chunk size should I use? Large enough that pandas overhead is amortised, small enough to fit comfortably in your memory budget — 20,000 to 100,000 rows suits most spreadsheet data.

Up to the parent guide:

Related guides: