Reducing Workbook Size by Removing Unused Styles and Data

A workbook with 20,000 rows of data and a file size of 60 MB is a puzzle. The data cannot account for it. Somewhere in the file is weight that does no work: cells formatted a million rows down, styles copied in from forty other workbooks, pivot caches nobody needs, pictures pasted at camera resolution. Finding it takes a few minutes with the right look. This post shows how to see what is large, then goes through the common causes and the fix for each. It is a spoke of Excel files that are too big or too slow.

Look inside first

An xlsx is a zip archive. Do not guess where the size is; list it. Work on a copy:

cp report.xlsx /tmp/r.zip
unzip -lv /tmp/r.zip | sort -k1 -n -r | head -15

The columns show the uncompressed size, the compression method and the compressed size, with the part's name. The compressed column is what the file costs on disk. Names tell the story:

A single worksheet part of 400 MB uncompressed with 3 MB of real data points at a bloated used range. A styles.xml of 20 MB points at style accumulation. Large files in xl/media are images. A pivotCache part as big as the data is the cache. The listing turns a vague complaint into a target.

Cause 1: the used range extends far beyond the data

Press Ctrl+End. Excel jumps to the last used cell, the intersection of the last row and column that ever held data or formatting. If your data ends at row 400 and Ctrl+End goes to row 800,000 or to column XFD, the sheet carries empty cells that have formatting, and they are saved and scanned.

Typical sources: formatting applied to whole columns, a macro that wrote and cleared cells, a fill color dragged down, someone who pressed Ctrl+A and formatted the sheet. Fix:

1. Select the rows below your data (click the row header, then Ctrl+Shift+Down).

2. Right-click, Delete (not just press the Delete key, which clears contents and leaves the formatting). Do the same for unused columns to the right.

3. Save. Press Ctrl+End again; it should land at the last real cell. If it does not, save and close and reopen, because Excel resets the used range on save.

Home, Clear, Clear All on the unused rows achieves the same. Conditional formatting applied to whole columns and data validation extended to the end of the sheet also keep the used range alive; remove or limit them.

Cause 2: unused cell styles

Every combination of font, fill, border and number format is a style. Copying sheets between workbooks drags the source's styles along, and Excel never removes the ones that are not used. A workbook that has been passed around for years can hold tens of thousands of styles. They bloat styles.xml, slow opening and eventually trigger "Too many different cell formats" errors when the cap on cell formats (64,000) is reached.

Check: Home, Cell Styles. A gallery that scrolls through hundreds of oddly named styles is the sign. Remedies:

• Delete unused styles. Right-click a style in the gallery and Delete. For hundreds of them a macro is practical: loop ActiveWorkbook.Styles, skipping built-in ones, and delete those not in use. Many people use a free add-in for this.

• Copy cells to a clean workbook. Move the data and formulas sheet by sheet with Paste Special values and formats, which brings only the styles actually used. Not formula-dependent structures such as names are carried, so check them.

• Open in a different tool and save. LibreOffice rewrites the styles table when it saves, which often reduces it. Verify that formatting and formulas survived.

A cleaner approach for generated files is prevention: libraries that reuse style objects (a format created once and applied to many cells) produce small style tables, while code that creates a new style per cell produces enormous ones.

Cause 3: pivot caches

A PivotTable stores a snapshot of its source data in a cache inside the file, so it can be explored without the source. A pivot over 500,000 rows stores 500,000 rows. Several pivots on the same source share a cache if they were created from each other, and duplicate it if they were created independently.

To shrink: PivotTable Analyze, Options, Data tab, untick Save source data with file. The file then stores only the pivot's summary, and the pivot must be refreshed (and the source available) to change its layout. You can also tick Refresh data when opening the file. If the data is in a table in the same workbook, its rows are saved twice, once in the sheet and once in the cache; the unticked option removes one copy. To share caches among pivots, copy an existing pivot or create new ones from the same Data Model, instead of from separate ranges.

Cause 4: images and shapes

A screenshot pasted at full resolution can weigh megabytes. Many workbooks accumulate logos on every sheet, each embedded separately. In xl/media you will see them. Fixes:

• Select a picture, Picture Format, Compress Pictures, and choose a lower resolution (web or email quality), applying to all pictures.

• Replace repeated pictures with one on a template sheet, or remove decoration.

• Check for hidden or tiny shapes. Home, Find & Select, Selection Pane lists every object, including ones hidden behind cells; thousands of invisible comment boxes or line shapes from pasted web content are a classic cause. Select All Objects with Go To Special, Objects, and delete the unwanted.

Cause 5: hidden sheets, names and links

Hidden and very hidden sheets are still saved. Unhide them in the Visual Basic editor if needed and delete what is unused. Defined names accumulate when sheets are copied, and thousands of broken names slow the workbook: Formulas, Name Manager, filter by names with errors, and delete. External links keep cached copies of linked workbooks: Data, Edit Links, Break Link when the link is dead, and check xl/externalLinks after.

Cause 6: formulas that could be values

Hundreds of thousands of formulas cost space in the sheet XML (the formula text is stored per cell, or shared, depending on how they were created) and time at calculation. A column that is final could be pasted as values. Keep the logic documented and, if it matters, keep a formula copy elsewhere. This is a trade of auditability for size; make it deliberately (why Excel is slow with formulas over many rows).

Cause 7: the file format

.xlsb (Excel Binary Workbook) stores the same content in a binary form that is typically smaller and opens and saves faster for very large or formula-heavy files. The costs: fewer tools read it (pandas needs pyxlsb, openpyxl cannot), and some features and macro security behaviors differ. Use it for workbooks that people only open in Excel. For data exchange stay with xlsx (what the XLS, XLSX and XLSB formats are).

Measure the result

Record the size before and after each change, in the order you made them, so you know which fix paid. A realistic outcome from cleaning a bloated workbook is a large reduction from the used range and styles together, and a modest one from images and caches. Save as a new name each time so you can go back. Check that the file still opens, that formulas return the same values and that formatting is intact. If you used a macro to delete styles, open both versions and compare a few sheets by eye.

A script can do the survey in bulk. Walk a folder of workbooks, open each as a zip with Python's zipfile, and report the top parts by size. It finds the worst files and the reason in a minute:

import zipfile, pathlib

for p in pathlib.Path("shared").rglob("*.xlsx"):
    with zipfile.ZipFile(p) as z:
        parts = sorted(z.infolist(), key=lambda i: i.compress_size, reverse=True)[:3]
    print(p.name, [(i.filename, i.compress_size) for i in parts])

Prevent the next one

Start new workbooks from a clean template. Do not format whole columns. Paste values and formats between workbooks with Paste Special instead of copying whole sheets. Save pivot caches without source data unless needed. Compress pictures when inserting. If a file is generated by code, create styles once and reuse them. Put large raw data in a database or a CSV, and keep the workbook for the summary.

When the size is real data

There are a million rows of content. A file of that size is large because of the data. Reduce what you store (aggregate, archive old years) or move it out of Excel (analyzing more than a million rows without Excel limits).

It is mostly shared strings. Long text in many cells is genuine content.

Compression is already good. Repeating data compresses well; unique data does not.

The file is a backup of something. Size is the cost of keeping the history.

Microsoft documents the limits in Excel specifications and limits and gives guidance on reducing the file size of workbooks. To check a small extract as JSON, use the CSV to JSON converter.

Python's zipfile module reads the same listing from code.