Speed Up xlwings with Bulk Reads and Writes

xlwings makes a running Excel workbook feel like a Python object: sheet["B2"].value = 42 and the cell changes on screen. That convenience hides the cost that makes most slow xlwings scripts slow. Every .value read or write is a call from your Python process into the Excel process — across a process boundary, through COM on Windows or AppleScript on macOS. One call is quick. Ten thousand calls in a loop are not. This guide, part of Automating Excel With xlwings: The Basics, replaces cell loops with bulk transfers and turns off the Excel features that slow down large updates.

Prerequisites

xlwings and a desktop Excel on Windows or macOS:

pip install xlwings pandas

The examples assume a workbook with a sales table starting in cell A1 of a sheet named Data.

Step 1 — Recognise the slow pattern

This is the pattern to look for. It reads a column one cell at a time and writes a result back one cell at a time — two calls into Excel per row:

import xlwings as xw

wb = xw.Book("sales.xlsx")
sheet = wb.sheets["Data"]
last = sheet.range("A1").end("down").row
for row in range(2, last + 1):
    amount = sheet.range(f"C{row}").value          # one call
    sheet.range(f"D{row}").value = amount * 1.2    # another call

On a few dozen rows nobody notices. On ten thousand rows it takes long enough that people assume Excel has hung. The logic is trivial; the time is all in the round trips.

Step 2 — Read and write whole ranges

The fix is to move the data once in each direction and do the work in Python. expand() finds the contiguous block from a starting cell, and .value on a multi-cell range returns a list of rows:

data = sheet.range("A1").expand().value          # one call: list of rows, header first
header, rows = data[0], data[1:]
col = header.index("amount")
results = [[r[col] * 1.2] for r in rows]          # plain Python, no Excel calls
sheet.range("D2").value = results                 # one call writes the whole column

Writing a nested list fills a block starting at the given cell: a list of one-element lists fills a column, a list of lists fills a rectangle. Writing a flat list fills a row; to write a flat list down a column instead, use .options(transpose=True).

Step 3 — Go straight to pandas

For table-shaped data, the options converter reads a block directly into a DataFrame and writes one back, still in a single call each way:

import pandas as pd

df = sheet.range("A1").options(pd.DataFrame, header=1, index=False, expand="table").value
df["amount_incl_tax"] = df["amount"] * 1.2
summary = df.groupby("region", as_index=False)["amount_incl_tax"].sum()

out = wb.sheets["Summary"]
out.clear_contents()
out.range("A1").options(index=False).value = summary

header=1 treats the first row as column names; index=False stops xlwings from using the first column as the index on read and from writing the index on write. Doing the transformation in pandas rather than cell by cell is the same shift described for openpyxl in Speed Up openpyxl with read_only Mode: move data out, compute, move it back.

Step 4 — Stop Excel from redrawing and recalculating

When a script makes many changes — writing several ranges, formatting, inserting rows — Excel by default repaints the window, fires workbook events and recalculates dependent formulas after each one. Turn those off for the duration of the update and restore them afterwards, even if the script fails. A context manager makes that automatic:

from contextlib import contextmanager

@contextmanager
def fast_mode(app):
    previous = (app.screen_updating, app.enable_events, app.calculation)
    app.screen_updating = False
    app.enable_events = False
    app.calculation = "manual"
    try:
        yield app
    finally:
        app.calculation = previous[2]
        app.enable_events = previous[1]
        app.screen_updating = previous[0]

with fast_mode(wb.app):
    out.range("A1").options(index=False).value = summary
    out.range("A1").expand("right").font.bold = True
    out.autofit()
wb.app.calculate()          # one recalculation at the end

Restoring the previous values — rather than forcing them back on — matters when the script runs inside a larger macro or a user session that had its own settings. Calculating once at the end replaces the hundreds of recalculations Excel would otherwise run while the ranges were being written.

Step 5 — Chunk very large ranges

A single call moving hundreds of thousands of cells can run into memory or COM limits and fail with an unhelpful error. The chunksize option splits the transfer into blocks of rows while keeping your code unchanged:

big = sheet.range("A1").options(pd.DataFrame, header=1, index=False,
                                expand="table", chunksize=10_000).value
out.range("A1").options(index=False, chunksize=10_000).value = big

Ten thousand rows per chunk is a reasonable starting point; wide tables may need smaller chunks. Chunking still moves the data in a handful of calls rather than one per cell, so it remains fast.

Step 6 — Format ranges, not cells

Formatting has the same trap as values. Setting a colour on each cell in a loop is one call per cell; setting it on the whole range is one call. Build the target range once and style it:

last_row = out.range("A1").end("down").row
money = out.range(f"B2:B{last_row}")
money.number_format = "#,##0.00"
out.range(f"A1:B1").color = (67, 56, 202)
out.range(f"A1:B1").font.color = (255, 255, 255)

For formatting that depends on the values — highlighting negative numbers, for example — prefer an Excel conditional formatting rule over a Python loop that colours cells one by one: one rule covers the range and stays correct when the data changes.

Step 7 — Measure it on your own machine

The gap between the two approaches depends on the machine, the Excel version and whether Excel is visible, so measure it rather than trusting anyone's numbers — including a quoted speed-up from a blog. A small harness times both versions of the same work on a scratch sheet:

import time

def timed(label, fn):
    start = time.perf_counter()
    fn()
    print(f"{label:>14}: {time.perf_counter() - start:.2f} s")

scratch = wb.sheets.add("Scratch")
n = 2_000

def per_cell():
    for i in range(1, n + 1):
        scratch.range(f"A{i}").value = i

def bulk():
    scratch.range("B1").value = [[i] for i in range(1, n + 1)]

timed("cell by cell", per_cell)
timed("one call", bulk)
scratch.delete()

Run it with n at a few hundred first — the per-cell version grows linearly with the number of cells, and at a few thousand it is already slow enough to make the point. The bulk version barely changes as n grows, because it is still one call. Keeping a harness like this next to a slow production script is the quickest way to find which loop is responsible before rewriting anything.

When xlwings is the wrong tool

If the workbook does not need to be open in Excel — no live recalculation, no macros, nobody watching — skip the application entirely. openpyxl and xlsxwriter write files directly without any cross-process calls, and pandas reads them far faster than any automation of Excel. xlwings earns its place when you need the running application: a user's open workbook, formulas only Excel can calculate, or a button in Excel that runs Python, as described in Run Python from an Excel Button with xlwings RunPython. The trade-off is laid out in When to Use xlwings Instead of openpyxl.

Common pitfalls

• Hidden loops. for cell in rng: iterates cell objects and makes a call per cell just as surely as an explicit index loop. Read rng.value once instead.
• Forgetting to restore settings. A script that crashes with calculation on manual leaves the user's Excel not recalculating. Always restore in finally.
• Expanding across gaps. expand() stops at the first empty row or column, so a blank row in the middle of a table cuts the read short. Use an explicit address or a table object for data with gaps.
• Dates and numbers. Bulk reads return Python datetime objects for date cells and floats for numbers — including whole numbers — so cast integer columns explicitly after reading.

Conclusion

xlwings is slow only when it is used like a spreadsheet user: one cell at a time. Read whole ranges with expand() or straight into a DataFrame, do the work in Python, and write results back in one call per range. Switch off screen updating, events and automatic calculation around large updates and restore them in finally, chunk very large transfers, and format ranges rather than cells.

Frequently asked questions

Why is my xlwings loop so slow? Every .value access on a cell is a separate call from Python into the Excel process. Thousands of cells mean thousands of cross-process calls, each with fixed overhead. Reading or writing the whole range in one call removes almost all of that cost.

How do I read a whole table into pandas with xlwings? sheet.range("A1").options(pd.DataFrame, index=False, header=1, expand="table").value reads the contiguous block starting at A1 into a DataFrame in one call.

Does turning off screen updating really help? Yes, when you write many ranges or format cells, because Excel otherwise redraws the window after each change. It matters less for a single bulk write, but it is cheap to switch off and restore.

What does chunksize do? For very large ranges, options(chunksize=10_000) splits the transfer into blocks of that many rows, avoiding memory and COM limits that can make a single enormous call fail.

Related

• Up one level: Automating Excel With xlwings: The Basics — connecting to workbooks.
• Read and Write a Live Excel Workbook with xlwings — the basic read and write calls.
• xlwings vs pywin32 for Excel Automation — the same bulk rule applies to COM.
• Benchmark Python Excel Read and Write Speed — measuring the alternatives.