Reduce the File Size of an Excel Workbook with Python

A 6 MB monthly report is a nuisance: mail systems reject it, shared drives sync it slowly, and people open it with a sigh. Most advice about shrinking Excel files is folklore — "remove formatting", "save as binary" — and most of it guesses at the cause. A workbook is a ZIP of parts, so the cause can be measured: list the parts, see which ones are large after compression, and fix those. In practice, one or two culprits account for nearly all the size. This page measures a deliberately bloated workbook, removes the bloat step by step with the size after each step, and ends with the habits that keep generated reports small in the first place. It belongs to Working with Large Excel Files in Python.

Prerequisites

pip install openpyxl pillow pandas

monthly_report.xlsx contains the usual suspects: 5,000 rows of real data; a fill colour applied to 55,000 empty rows below it (someone formatted "the whole column"); 3,000 cells with individual font colours; a 1600 × 1600 photo inserted at full resolution; and a hidden sheet with 20,000 rows of last year's data.

Step 1 — Measure the parts

import os
import zipfile

def part_sizes(path, top=6):
    z = zipfile.ZipFile(path)
    rows = sorted(((i.filename, i.file_size, i.compress_size) for i in z.infolist()), key=lambda t: -t[2])
    print(f"{path}: {os.path.getsize(path) / 1e6:.2f} MB")
    for name, raw, packed in rows[:top]:
        print(f"  {name:28} {raw / 1e3:9,.0f} kB raw  {packed / 1e3:9,.0f} kB in the file")

part_sizes("monthly_report.xlsx")

The compressed size of each part is what you pay for. Here the photo — xl/media/image1.png — is 5.8 MB of a 6.6 MB file, because PNG data is already compressed and ZIP cannot shrink it further. The two sheets are 3.9 MB and 2.9 MB of XML but only about 0.4 and 0.3 MB once compressed; styles.xml is 400 kB raw and 18 kB compressed. Without measuring, most people would have started on the formatting. Inspecting packages is covered in Inspect the XML Inside an .xlsx File with Python.

Step 2 — Delete the formatted empty rows

Cells that only carry formatting still exist in the sheet XML, and they push Excel's used range — and max_row — far below the data:

from openpyxl import load_workbook

def last_data_row(ws):
    last = 0
    for i, row in enumerate(ws.iter_rows(values_only=True), start=1):
        if any(v is not None for v in row):
            last = i
    return last

wb = load_workbook("monthly_report.xlsx")
ws = wb["Data"]
data_end = last_data_row(ws)
print("max_row:", ws.max_row, "| last row with data:", data_end)
ws.delete_rows(data_end + 1, ws.max_row - data_end)
wb.save("step1_rows.xlsx")
print(f"{os.path.getsize('step1_rows.xlsx') / 1e6:.2f} MB")

max_row was 60,000 while the data ends at row 5,000. Deleting the tail in one delete_rows call removed 55,000 formatted cells and about 0.28 MB. The bigger benefit is often not the size but the behaviour: Excel scrolls to row 60,000, prints blank pages and makes Ctrl+End useless until the tail is gone. Why max_row overstates the data is explained in Fix openpyxl max_row Counting Empty Rows.

Step 3 — Remove or move archive sheets

wb = load_workbook("step1_rows.xlsx")
print([(s.title, s.sheet_state, s.max_row) for s in wb.worksheets])
wb.remove(wb["Old data"])
wb.save("step2_sheet.xlsx")
print(f"{os.path.getsize('step2_sheet.xlsx') / 1e6:.2f} MB")

Hidden sheets are invisible to readers and fully paid for in the file. Last year's 20,000 rows cost about a third of a megabyte; in real workbooks, hidden "data" and "calc" sheets are often the largest parts after images. If the data is still needed, keep it in a separate file — or better, Parquet, as in Convert Excel Files to Parquet with Python — and link to it from the report.

Step 4 — Shrink the image

The photo is 1600 pixels square and displayed as a thumbnail. Resize it to the size it is shown at. openpyxl keeps images on load, but re-encoding them is simplest by rewriting the image part inside the package:

import io
from PIL import Image

src = zipfile.ZipFile("step2_sheet.xlsx")
photo = Image.open(io.BytesIO(src.read("xl/media/image1.png")))
resized = io.BytesIO()
photo.resize((600, 600), Image.LANCZOS).save(resized, "PNG", optimize=True)
with zipfile.ZipFile("step3_image.xlsx", "w", zipfile.ZIP_DEFLATED) as out:
    for info in src.infolist():
        data = resized.getvalue() if info.filename == "xl/media/image1.png" else src.read(info.filename)
        out.writestr(info.filename, data)
print(f"{os.path.getsize('step3_image.xlsx') / 1e6:.2f} MB")
check = load_workbook("step3_image.xlsx")
print("images still present:", len(check["Data"]._images), "| rows:", check["Data"].max_row)

The file drops from 5.99 MB to 0.73 MB. The image keeps its name and format (PNG), so no other part of the package needs to change, and openpyxl loads the result without complaint. For photographs, JPEG is far smaller still — the same 600-pixel image is 36 kB as JPEG against 552 kB as PNG — but changing the format means changing the part's name and content type, which is easiest done when the report is generated: resize and convert before calling add_image, as described in Resize and Position Images in Excel with openpyxl.

Step 5 — Fixes that barely help

Two popular remedies did little here, and it is worth knowing why:

from openpyxl.styles import Font

wb = load_workbook("step3_image.xlsx")
ws = wb["Data"]
for r in range(2, 3001):
    ws.cell(r, 3).font = Font(color="000000")
wb.save("step4_styles.xlsx")
print("styles.xml raw:", zipfile.ZipFile("step4_styles.xlsx").getinfo("xl/styles.xml").file_size, "bytes")
try:
    wb.save("step4_again.xlsx")
except ValueError as exc:
    print("second save:", exc)

Resetting the 3,000 individual font colours does not shrink styles.xml: openpyxl keeps every style it has loaded, used or not, and writes them all out again — and the part was only 18 kB compressed to begin with. Unless a workbook has tens of thousands of distinct styles (the "too many different cell formats" error), styles are not where the size is. The second surprise is a bug worth knowing: saving the same loaded workbook twice fails with ValueError: I/O operation on closed file, because openpyxl reads the image data from the original file only once. Load, change and save once per pass — which is why each step above reloads from the previous file.

Saving as .xlsb is sometimes suggested too; it does shrink large sheet XML, but Python cannot write that format, and images are the same size in either.

Step 6 — Keep generated reports small from the start

Most bloat is created by the script that builds the report, so prevention is cheap:

from openpyxl import Workbook
from openpyxl.drawing.image import Image as XLImage

thumb = Image.open("site_photo.png").convert("RGB")
thumb.thumbnail((600, 600))
thumb.save("site_photo_small.jpg", quality=85)

lean = Workbook()
ws = lean.active
for r in range(1, 5001):
    ws.append([r, f"Item {r % 300}", float(r) * 1.5, "North"])
for col in "ABCD":
    ws.column_dimensions[col].width = 12        # widths, not formatted cells
ws.add_image(XLImage("site_photo_small.jpg"), "F2")
lean.save("monthly_report_lean.xlsx")
part_sizes("monthly_report_lean.xlsx", top=3)

Insert images at display size and as JPEG for photos; format ranges that contain data rather than whole columns; keep archive data out of the report; and set column widths instead of styling empty cells. The rebuilt report holds the same data and picture in 0.16 MB — about one fortieth of the original — with the JPEG thumbnail taking 35 kB.

Common pitfalls

• Guessing the cause. Measure part sizes first; images and hidden data usually dominate.
• Formatting whole columns. Styled empty cells extend the used range and add XML.
• Saving a loaded workbook twice. With images, the second save fails; reload between saves.
• Expecting style clean-up to shrink files. openpyxl keeps unused styles; the gain is usually tiny anyway.
• Recompressing to a different image format in place. Changing PNG to JPEG inside the package requires updating the part name and content type; convert when generating instead.

Conclusion

Measure the compressed size of every part in the package before changing anything — in most bloated workbooks an oversized image or a hidden data sheet is nearly all of the size. Resize images to their display size (JPEG for photos), delete formatted rows below the data, move archive sheets out, and do not expect much from style clean-up. Reload between saves when the workbook contains images, and build reports lean from the start so there is nothing to shrink.

Frequently asked questions

Why is my Excel file so large? Usually because of embedded images at full resolution, hidden sheets with old data, or formatting applied to whole columns. Open the .xlsx as a ZIP with zipfile and compare the compressed size of each part to see which one it is.

How do I compress the images in an Excel file with Python? Read the image from xl/media inside the package, resize it with Pillow to the size it is displayed at, and write it back under the same name and format. For new reports, resize and convert photos to JPEG before inserting them with openpyxl.

Does removing formatting reduce Excel file size? Removing formatting from empty rows below the data helps, because those cells are stored in the sheet. Changing the styles of cells that hold data rarely helps, since styles compress well and openpyxl keeps loaded styles anyway.

Why does openpyxl say "I/O operation on closed file" when saving? The workbook contains images and has already been saved once since it was loaded; openpyxl reads image data from the source file only for the first save. Load the workbook again before saving a second time.

Related

• Up one level: Working with Large Excel Files in Python — big files, read and written efficiently.
• Delete Empty Rows and Columns with openpyxl — removing blank rows inside the data too.
• Zip Large Excel Attachments Before Emailing — when the file is as small as it gets.
• Remove Hidden Data Before Sharing an Excel File — hidden sheets as a privacy issue.