Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Find Out Why an Excel File Is Slow to Open

Diagnose a sluggish workbook from Python without opening it in Excel: which parts of the xlsx package take the space, sheets whose used range stretches far beyond the data, thousands of cell styles, piles of conditional-formatting rules and data validations, volatile functions such as INDIRECT and OFFSET, external links, and a one-page diagnosis sheet that ranks what to fix first.
Large files & performanceopenpyxl

"The forecast workbook takes two minutes to open" is a complaint with half a dozen usual causes, and guessing which one wastes an afternoon. Maybe a sheet's used range runs to row 1,048,576 because someone formatted whole columns; maybe the file has accumulated forty thousand cell styles from years of copy-pasting between workbooks; maybe a hundred conditional-formatting rules each cover entire columns; maybe thousands of INDIRECT formulas recalculate on every change. An .xlsx file is a zip archive of XML parts, so all of these leave traces that Python can measure without starting Excel. This page builds a diagnostic script that inspects the package and the sheets, then writes a one-page report ranking the likely causes. It belongs to Working with Large Excel Files in Python.

Prerequisites

Bash
pip install openpyxl pandas

forecast.xlsx is a deliberately unhealthy workbook: 400 rows of real data, formatting scattered down to row 60,000, a few hundred one-off font styles, 120 conditional-formatting rules over whole blocks, a validation range far larger than the data, INDIRECT formulas on every row and a link to another workbook. Every check below works on real files the same way.

Step 1 — What takes the space inside the package

Start with the zip archive itself. Listing its parts by uncompressed size shows immediately whether the bulk is sheet data, styles, images, pivot caches or something else:

Python
import zipfile
import pandas as pd

with zipfile.ZipFile("forecast.xlsx") as z:
    parts = pd.DataFrame([(i.filename, i.file_size, i.compress_size) for i in z.infolist()],
                         columns=["part", "bytes", "compressed"])
parts = parts.sort_values("bytes", ascending=False)
parts["share %"] = (parts["bytes"] / parts["bytes"].sum() * 100).round(1)
print(parts.head(6).to_string(index=False))

A healthy workbook is dominated by its sheet XML (xl/worksheets/sheet1.xml and friends) in proportion to its data. Here the first sheet is the largest part by far, although it holds only 400 rows — a sign that something other than data is being stored there. Other parts to watch: a large xl/styles.xml points to style bloat, xl/pivotCache/ parts to pivot caches holding copies of source data, xl/media/ to images, and xl/externalLinks/ to links to other files. How to look inside the parts is covered in Inspect the XML Inside an xlsx File with Python.

Step 2 — Used range versus real data

Excel loads every row in a sheet's used range — every row that has a value or formatting. Compare the dimension each sheet declares with the last row that actually contains a value:

Python
from openpyxl import load_workbook

wb = load_workbook("forecast.xlsx")
ranges = []
for ws in wb.worksheets:
    last_value_row = max((c.row for row in ws.iter_rows() for c in row if c.value is not None), default=0)
    ranges.append({"sheet": ws.title, "declared": ws.dimensions, "max_row": ws.max_row,
                   "last row with a value": last_value_row,
                   "empty rows carried": ws.max_row - last_value_row})
print(pd.DataFrame(ranges).to_string(index=False))

The Forecast sheet's data ends at row 401, but its declared dimension is A1:H60000: almost 60,000 rows that contain nothing but formatting, which openpyxl reports as 59,599 empty rows carried. Excel has to load, lay out and keep every one of them, and every whole-column operation then covers them too. This is the single most common cause of slow, oversized workbooks, and the fix is simple — delete the empty rows below the data (and columns to the right), which removes their formatting. Delete Empty Rows and Columns with openpyxl shows how, and Fix openpyxl max_row Counting Empty Rows explains why max_row reports the larger figure.

Data ends at row 401, the used range does not The Forecast sheet holds values in rows 1 to 401. Below that, formatted cells and rule ranges continue down to row 60,000, so the used range covers sixty thousand rows although only four hundred contain data. A B C D E 1 Row Month Region Units Note 2 2 2026-03 South 2 data 3 401 2026-06 North 1 last value 4 402 … 60,000 formatting only 5 used range A1:H60000 ≈ 150× the data

Step 3 — Styles, rules and validations

Every distinct combination of font, fill, border, alignment and number format is a separate cell style in styles.xml. Workbooks that receive pasted content from many sources accumulate thousands, and Excel slows down well before its limit of 64,000. Count them, together with conditional-formatting rules and data validations and the cells they cover:

Python
from openpyxl.utils.cell import range_boundaries

def area(ref):
    total = 0
    for part in str(ref).split():
        c1, r1, c2, r2 = range_boundaries(part)
        total += (c2 - c1 + 1) * (r2 - r1 + 1)
    return total

print("cell styles in workbook:", len(wb._cell_styles))
for ws in wb.worksheets:
    cf_rules = sum(len(rules) for rules in ws.conditional_formatting._cf_rules.values())
    cf_cells = sum(area(cf.sqref) * len(rules) for cf, rules in ws.conditional_formatting._cf_rules.items())
    dv_cells = sum(area(dv.sqref) for dv in ws.data_validations.dataValidation)
    print(f"{ws.title:9} CF rules: {cf_rules:4}  CF cell-evaluations: {cf_cells:>10,}  validated cells: {dv_cells:>8,}")

The style count is modest here, but on files that have been through many hands it is often the first number to check — a few hundred is normal, tens of thousands is a problem. The 120 conditional-formatting rules each cover a block of 360,000 cells, so Excel has around 43 million rule evaluations to consider whenever it redraws the sheet, and the list validation spans 60,000 rows for 400 rows of data. Rules applied to whole columns or far beyond the data are a frequent result of copying formatted rows; consolidating duplicate rules and shrinking their ranges to the data usually restores speed. List and Remove Conditional Formatting with openpyxl covers the clean-up. (_cell_styles and _cf_rules are internal openpyxl attributes; they are stable in practice but not part of the public API.)

Some functions are volatile: Excel recalculates them on every change anywhere in the workbook, along with everything that depends on them. INDIRECT, OFFSET, NOW, TODAY, RAND and RANDBETWEEN are the usual suspects. Count them, and find links to other files:

Python
import re

VOLATILE = re.compile(r"\b(INDIRECT|OFFSET|NOW|TODAY|RAND|RANDBETWEEN|CELL|INFO)\s*\(", re.I)
EXTERNAL = re.compile(r"\[[^\]]+\.xls[xmb]?\]", re.I)
findings = []
for ws in wb.worksheets:
    formulas = [c for row in ws.iter_rows() for c in row if isinstance(c.value, str) and c.value.startswith("=")]
    vol = [c for c in formulas if VOLATILE.search(c.value)]
    ext = [c.coordinate for c in formulas if EXTERNAL.search(c.value)]
    findings.append({"sheet": ws.title, "formulas": len(formulas), "volatile": len(vol),
                     "volatile functions": sorted({m.upper() for c in vol for m in VOLATILE.findall(c.value)}),
                     "external links": ext})
print(pd.DataFrame(findings).to_string(index=False))
with zipfile.ZipFile("forecast.xlsx") as z:
    print("externalLink parts:", [n for n in z.namelist() if "externalLink" in n])

Half of the Forecast sheet's 801 formulas use INDIRECT, which is volatile; in a large model, a few thousand such formulas can make every edit pause while they all recalculate. Here INDIRECT("C"&ROW()) simply means "the cell in column C on this row", which a plain =C2 does without volatility — most uses of INDIRECT and OFFSET in the wild can be replaced like this, or with INDEX. The formula in H1 refers to another workbook, Budget 2025.xlsx; external links make Excel try to update or prompt on opening, which is slow when the linked file lives on a network share. The XML parts list may not show an externalLink part when the link was written as a plain formula, as here — that is why the formulas are scanned too. Find External Links and Broken References in a Workbook goes further.

The usual causes and how they show up A bloated used range shows as a large sheet part and a max row far beyond the data. Style bloat shows as a large styles part and thousands of cell styles. Rule bloat shows as many conditional formatting rules over huge ranges. Volatile formulas show as INDIRECT, OFFSET or NOW in formulas. External links show as bracketed file names in formulas or externalLink parts. symptom in the file fix used range max_row ≫ last value row delete empty rows and columns style bloat thousands of cell styles clear formats, use named styles rule bloat many CF rules × huge ranges merge rules, shrink ranges volatile formulas INDIRECT, OFFSET, NOW replace with direct refs or INDEX external links [Book.xlsx] in formulas break or convert to values

Step 5 — A ranked diagnosis sheet

Pull the findings together into one workbook a colleague can act on — each potential cause with its measured value, a threshold, and a verdict:

Python
ws = wb["Forecast"]
cf_cells = sum(area(cf.sqref) * len(rules) for cf, rules in ws.conditional_formatting._cf_rules.items())
checks = [
    ("Empty rows carried in used range", ranges[0]["empty rows carried"], 1_000),
    ("Cell styles in workbook", len(wb._cell_styles), 4_000),
    ("Conditional-format cell evaluations", cf_cells, 1_000_000),
    ("Validated cells beyond data", sum(area(dv.sqref) for dv in ws.data_validations.dataValidation) - 400, 10_000),
    ("Volatile formulas", findings[0]["volatile"], 200),
    ("Formulas linking to other workbooks", len(findings[0]["external links"]), 0),
]
report = pd.DataFrame(checks, columns=["Check", "Found", "Threshold"])
report["Verdict"] = ["fix" if f > t else "ok" for f, t in zip(report["Found"], report["Threshold"])]
report["Severity"] = (report["Found"] / report["Threshold"].replace(0, 1)).round(1)
report = report.sort_values(["Verdict", "Severity"], ascending=[True, False])
report.to_excel("forecast_diagnosis.xlsx", index=False)
print(report.to_string(index=False))

Each check is compared with a rough threshold beyond which it typically hurts, and the list is sorted so the most severe problems come first. The thresholds are rules of thumb, not hard limits — a workbook with 3,000 styles and nothing else wrong may be fine — but the ranking points the fix at the biggest cause first. For this file the bloated used range and the conditional-formatting rules lead the list, with only the style count passing; fixing those two alone usually turns a two-minute open into a few seconds. Running the same script on the "after" file shows whether the fix worked.

How far each check exceeds its threshold For the forecast workbook, empty rows carried in the used range are about 60 times the threshold and conditional formatting evaluations about 43 times. Validated cells beyond the data are 6 times, volatile formulas twice and external links once over. The cell style count is well within its threshold. empty rows in used range 59.6× CF cell evaluations 43.2× validated cells beyond data 6.0× volatile formulas 2.0× cell styles 0.1× · ok

Common pitfalls

  • Judging by file size alone. A small file can be slow (volatile formulas); a large one fast (plain data).
  • Trusting max_row as the data size. It includes formatted empty rows.
  • Copying formatted rows down to the bottom of the sheet. It extends the used range and every rule.
  • Using INDIRECT or OFFSET for simple references. They are volatile; use direct references or INDEX.
  • Forgetting links to other workbooks. They slow opening and may prompt users.

Conclusion

A slow workbook usually has a measurable cause: a used range far larger than the data, thousands of cell styles, conditional-formatting rules and validations over huge ranges, volatile functions recalculating constantly, or links to other files. Python can find each one without Excel — by sizing the parts of the zip package, comparing each sheet's dimension with its last real value, counting styles, rules and validated cells with openpyxl, and scanning formulas for volatile functions and external references. Rank the findings, fix the biggest first, and rerun the diagnosis to confirm.

Frequently asked questions

Why is my Excel file so slow to open? The most common causes are a used range far beyond the data because of formatting, very many cell styles, conditional formatting or validation over huge ranges, many volatile formulas such as INDIRECT or OFFSET, and links to other workbooks.

How can I check an Excel file's used range with Python? Load it with openpyxl and compare ws.max_row and ws.dimensions with the last row that actually contains a value. A large gap means formatting extends far below the data.

Which Excel functions are volatile? INDIRECT, OFFSET, NOW, TODAY, RAND, RANDBETWEEN, CELL and INFO recalculate on every change, together with all formulas that depend on them.

How do I see what takes up space in an xlsx file? Open it with Python's zipfile module and list the parts by size. The sheet XML, styles.xml, media, pivot caches and external link parts are the usual large ones.

More on large files & performance

More on large files & performance →