Working with Large Excel Files in Python

Spreadsheets grow until something breaks: a script that took four seconds takes four minutes, a laptop starts swapping, or a scheduled job is killed for using too much memory. Almost always the fix is not a faster machine but a different access pattern — streaming instead of loading, reading four columns instead of forty, and handing bulk data to a format built for it. This guide, part of Advanced Data Transformation and Cleaning, works through those patterns in the order you should try them.

Install the dependencies

pip install pandas openpyxl pyarrow

pyarrow is only needed for the Parquet handoff at the end, but it is the single most useful addition to a large-data toolbox.

Make a big file to experiment on

Every measurement below is more convincing on your own hardware, so start by generating something substantial:

import numpy as np
import pandas as pd

rows = 400_000
rng = np.random.default_rng(seed=7)

df = pd.DataFrame({
    "Order_ID": np.arange(1, rows + 1),
    "Order_Date": pd.date_range("2024-01-01", periods=rows, freq="min"),
    "Region": rng.choice(["North", "South", "West", "Central"], rows),
    "SKU": rng.choice([f"SKU-{n:04d}" for n in range(500)], rows),
    "Quantity": rng.integers(1, 20, rows),
    "Unit_Price": rng.uniform(5, 200, rows).round(2),
    "Notes": "",
})
df.to_excel("large.xlsx", index=False, sheet_name="Orders")
print(df.memory_usage(deep=True).sum() / 1e6, "MB in pandas")

That write alone takes a while — which is itself the lesson. Writing 400,000 rows through the default engine builds every cell as an object before serialising.

Step 1: read fewer columns

Before any clever streaming, read less. usecols is applied during parsing, so skipped columns are never converted into Python objects at all:

import time
import pandas as pd

start = time.perf_counter()
full = pd.read_excel("large.xlsx", sheet_name="Orders")
print(f"all columns: {time.perf_counter() - start:.1f}s, "
      f"{full.memory_usage(deep=True).sum() / 1e6:.0f} MB")

start = time.perf_counter()
slim = pd.read_excel("large.xlsx", sheet_name="Orders",
                     usecols=["Order_Date", "Region", "Quantity", "Unit_Price"])
print(f"four columns: {time.perf_counter() - start:.1f}s, "
      f"{slim.memory_usage(deep=True).sum() / 1e6:.0f} MB")

On a typical export the saving is large, because the columns you do not need are usually the wide text ones. This is the cheapest optimisation available and the one most often skipped — see reading specific columns from Excel with pandas for the full set of usecols forms.

Step 2: right-size the dtypes

pandas defaults to 64-bit numbers and Python objects for text. Neither is necessary for most spreadsheet data:

import pandas as pd

slim = pd.read_excel("large.xlsx", sheet_name="Orders",
                     usecols=["Order_Date", "Region", "SKU", "Quantity", "Unit_Price"])
before = slim.memory_usage(deep=True).sum() / 1e6

slim["Quantity"] = pd.to_numeric(slim["Quantity"], downcast="integer")     # int64 -> int8
slim["Unit_Price"] = pd.to_numeric(slim["Unit_Price"], downcast="float")   # float64 -> float32
slim["Region"] = slim["Region"].astype("category")                         # 4 distinct values
slim["SKU"] = slim["SKU"].astype("category")                               # 500 distinct values

after = slim.memory_usage(deep=True).sum() / 1e6
print(f"{before:.0f} MB -> {after:.0f} MB")

category is the big win on spreadsheet data, because columns like region, status and product code repeat the same handful of strings for hundreds of thousands of rows. Downcasting numbers helps less but is free. Do it after reading, once, rather than in every downstream step.

Step 3: stream rows with read_only mode

When you need every row but only a few values from each, openpyxl's read-only mode parses the sheet lazily and never builds the whole grid:

from openpyxl import load_workbook

wb = load_workbook("large.xlsx", read_only=True, data_only=True)
ws = wb["Orders"]

header = [c.value for c in next(ws.iter_rows(min_row=1, max_row=1))]
qty_col = header.index("Quantity")
price_col = header.index("Unit_Price")
region_col = header.index("Region")

totals = {}
for row in ws.iter_rows(min_row=2, values_only=True):
    region = row[region_col]
    totals[region] = totals.get(region, 0) + (row[qty_col] or 0) * (row[price_col] or 0)

wb.close()
print({k: round(v) for k, v in totals.items()})

values_only=True is what makes this fast: openpyxl yields plain tuples instead of Cell objects, skipping the most expensive allocation in the whole library. wb.close() matters too — a read-only workbook holds the underlying zip open until you close it, and a long-running job that forgets will run out of file handles.

The trade is real: a read-only sheet cannot be written to, ws.max_row may be unreliable until the sheet has been walked, and cell styling is not available. For aggregation and extraction that is a fair price.

Step 4: process in chunks

Sometimes you genuinely need DataFrame operations but not the whole frame at once. openpyxl streaming plus a chunk buffer gives you both:

import pandas as pd
from openpyxl import load_workbook

def excel_chunks(path, sheet, size=50_000, columns=None):
    wb = load_workbook(path, read_only=True, data_only=True)
    ws = wb[sheet]
    rows = ws.iter_rows(values_only=True)
    header = list(next(rows))
    keep = [header.index(c) for c in (columns or header)]

    buffer = []
    for row in rows:
        buffer.append([row[i] for i in keep])
        if len(buffer) >= size:
            yield pd.DataFrame(buffer, columns=columns or header)
            buffer = []
    if buffer:
        yield pd.DataFrame(buffer, columns=columns or header)
    wb.close()

running = 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)["Revenue"].sum()
    running = part if running is None else running.add(part, fill_value=0)

print(running.round(2))

The pattern generalises to any aggregation that can be combined across chunks — sums, counts, minima and maxima all work. Averages need care: accumulate the sum and the count separately and divide at the end, because the mean of chunk means is only correct when the chunks are the same size.

Step 5: writing large output

Writing has its own streaming mode. write_only=True builds the file row by row and never holds the sheet:

from openpyxl import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet("Export")
ws.append(["Order_ID", "Region", "Revenue"])

for order_id in range(1, 300_001):
    ws.append([order_id, "North", order_id * 1.5])

wb.save("streamed.xlsx")

Rows must be appended in order and cannot be revisited, so column widths, conditional formatting and totals have to be planned before the loop rather than added afterwards. xlsxwriter offers an equivalent through its constant_memory option, and is generally the faster of the two for pure generation.

Step 6: know when to leave xlsx behind

The honest answer for genuinely large data is that .xlsx is the wrong container. It is a presentation format that happens to hold data, and every technique above is a way of paying less for that mismatch:

import pandas as pd

df = pd.read_excel("large.xlsx", sheet_name="Orders")

df.to_csv("orders.csv", index=False)              # universal, streamable, no types
df.to_parquet("orders.parquet", index=False)      # typed, compressed, columnar

# Later runs read the fast format instead
fast = pd.read_parquet("orders.parquet", columns=["Region", "Quantity", "Unit_Price"])
print(len(fast))

Parquet keeps dtypes, compresses well, and lets you read three columns of a hundred without touching the rest. The pattern that works in practice is a split: bulk data moves through CSV or Parquet, and Excel is produced only for the summary a person actually opens — usually a few hundred rows, which is exactly the size spreadsheets are good at.

One more lever: skip the styles

A surprising share of the time spent opening a workbook goes on its style table rather than its values. openpyxl parses every font, fill and border definition before it hands you a single cell, and a file that has been edited by many people over several years accumulates thousands of them. rich_text=False (the default) and read_only=True already avoid most of the cost, and where you control the source file, reducing the number of distinct styles helps every consumer:

from openpyxl import load_workbook

wb = load_workbook("large.xlsx")
print(f"{len(wb._cell_styles)} distinct cell style(s)")
print(f"{len(wb._fonts)} font(s), {len(wb._fills)} fill(s), {len(wb._borders)} border(s)")

Those counts are diagnostic rather than actionable on a file you receive, but they explain the otherwise baffling case of two files with identical row counts where one takes five times longer to open. When your own job generates the file, reuse a small number of named style objects rather than constructing a new Font per cell — the visual result is the same and the style table stays tiny.

What to reach for when

Reading only part of a very wide sheet

Some exports are not tall but wide: two hundred columns of which you need six. usecols handles the pandas side, and openpyxl can restrict the cell range directly, which avoids parsing the rest of each row:

from openpyxl import load_workbook
from openpyxl.utils import column_index_from_string

wb = load_workbook("wide.xlsx", read_only=True, data_only=True)
ws = wb["Data"]

first = column_index_from_string("B")
last = column_index_from_string("G")

for row in ws.iter_rows(min_row=2, min_col=first, max_col=last, values_only=True):
    if row[0] is None:
        continue                      # skip rows where the key column is blank
    # row now holds exactly columns B..G for this record
    pass

wb.close()

Bounding min_col and max_col is worth doing even in streaming mode, because a row of two hundred values is two hundred tuple entries whether you look at them or not. On a sheet where the interesting columns sit at the left, this alone can halve the parse time.

The same reasoning applies to rows. If a monthly job only needs the current month and the sheet is sorted by date, min_row plus an early break once the dates run past your window turns a full-file scan into a partial one — provided you can trust the sort, which is worth asserting rather than assuming.

Measure before you optimise

Every recommendation above has exceptions, and the only way to know which applies to your file is to measure it. Three numbers are enough: wall-clock time, peak memory, and the size of the frame you ended up with:

import time
import tracemalloc

import pandas as pd

def measure(label, fn):
    tracemalloc.start()
    start = time.perf_counter()
    result = fn()
    elapsed = time.perf_counter() - start
    _, peak = tracemalloc.get_traced_memory()
    tracemalloc.stop()
    size = result.memory_usage(deep=True).sum() / 1e6 if hasattr(result, "memory_usage") else 0
    print(f"{label:28} {elapsed:6.1f}s  peak {peak / 1e6:7.0f} MB  frame {size:6.0f} MB")
    return result

measure("everything", lambda: pd.read_excel("large.xlsx", sheet_name="Orders"))
measure("four columns", lambda: pd.read_excel(
    "large.xlsx", sheet_name="Orders",
    usecols=["Order_Date", "Region", "Quantity", "Unit_Price"]))

tracemalloc reports Python allocations only, which is exactly what you want here — the growth is in objects, not in buffers. Run the comparison on your real file rather than on a generated one: real exports have wide free-text columns, merged cells and per-cell styling, and those are precisely the things that make a read expensive.

The number that decides your approach is peak memory against the limit your job runs under. A container capped at 512 MB cannot load a frame that peaks at 900 MB no matter how briefly, and streaming is not an optimisation there — it is the only option.

Do not make the reader's file large either

A file that is slow for your script is usually slow for the person who opens it, and the causes are different. Python cares about cell count; Excel cares about formulas, styles and conditional formats:

from openpyxl import load_workbook

wb = load_workbook("large.xlsx")
ws = wb["Orders"]

cells = ws.max_row * ws.max_column
formulas = sum(
    1 for row in ws.iter_rows() for c in row
    if isinstance(c.value, str) and c.value.startswith("=")
)
rules = sum(len(rng.rules) for rng in ws.conditional_formatting)

print(f"{cells:,} cells · {formulas:,} formulas · {rules} conditional-format rule(s)")
if formulas > 50_000:
    print("consider writing values instead of formulas for the bulk rows")

Two habits keep delivered workbooks quick. Write values rather than formulas for bulk rows, keeping live formulas for the handful of summary cells a reader might change. And apply conditional formatting to a range rather than to individual cells — one rule over A2:D100000 costs a fraction of ten thousand rules over single cells, and looks identical.

That split is the single structural decision that makes large reporting workflows pleasant. The detail is still available to anyone who needs it, and the artefact people actually open stays a few hundred kilobytes.

A worked pipeline

Putting the pieces together, a monthly job over a very large export looks like this:

import pandas as pd

SOURCE = "large.xlsx"
COLUMNS = ["Order_Date", "Region", "SKU", "Quantity", "Unit_Price"]

def build_summary():
    running = None
    for chunk in excel_chunks(SOURCE, "Orders", size=50_000, columns=COLUMNS):
        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")
        )
        running = part if running is None else running.add(part, fill_value=0)
    return running.reset_index().sort_values(["Month", "Region"])

summary = build_summary()
summary.to_parquet("orders_summary.parquet", index=False)

with pd.ExcelWriter("summary.xlsx", engine="openpyxl") as writer:
    summary.to_excel(writer, sheet_name="Monthly", index=False)

print(f"{len(summary)} summary row(s) from a 400,000-row source")

Peak memory stays at roughly one chunk, the workbook a person receives has a few hundred rows, and the full detail is one Parquet read away. Wrap it in the error handling and logging patterns and it is ready to be scheduled.

Common errors and fixes

Key takeaways

• .xlsx in memory is routinely twenty to fifty times its size on disk, because every cell becomes an object.
• Reading fewer columns is the cheapest and most effective optimisation; try it before anything clever.
• category dtypes shrink the repeated-string columns that dominate spreadsheet exports.
• read_only with values_only=True streams rows as tuples and skips the expensive allocations — close the workbook afterwards.
• Chunked processing works for any aggregation that combines across blocks; means need sums and counts kept separately.
• write_only (or xlsxwriter's constant_memory) streams output, at the cost of not being able to revisit a row.
• Past a certain size the right move is a format change: bulk data in Parquet or CSV, a small workbook for the reader.

Frequently asked questions

Why does a 40 MB xlsx use over a gigabyte of memory?.xlsx is zipped XML. Every cell becomes a Python object carrying its value, style reference and coordinate, so the in-memory form is routinely twenty to fifty times the file size.

What is the single biggest win on a slow read? Reading fewer columns. usecols is applied while parsing, so the cells you skip are never turned into objects at all.

Does read_only mode change what I can do? Yes. A read-only worksheet streams rows and cannot be written to, and cells are lightweight read-only objects without full style information.

When should I stop using xlsx altogether? When the data is machine-to-machine. Convert to CSV or Parquet for the pipeline and produce a spreadsheet only for the summary a person actually reads.

Is there a row limit? A worksheet holds at most 1,048,576 rows and 16,384 columns. Long before that, opening the file becomes painful for the reader, which is a better limit to design against.

Related

Up to the parent guide:

• Advanced Data Transformation and Cleaning — the pipeline these techniques sit inside.

Go deeper here:

• Read a Large Excel File in Chunks with pandas — the chunking pattern in full.
• Speed Up openpyxl with read_only Mode — streaming reads and their limits.
• Write Large DataFrames to Excel with write_only Mode — generating big workbooks without the memory.
• Convert Excel to CSV with Python — the handoff to a streamable format.
• Process Multiple Excel Files in Parallel with Python — using more than one core on a folder of files.

Related areas:

• Read Specific Columns from Excel with pandas — the usecols forms in detail.
• Cleaning Excel Data with pandas — the transformations you will be streaming.
• Combine Multiple Excel Files into One with Python — where large inputs usually come from.