Reduce Excel workbook file size (when it’s large for no particular reason)

A large number of empty cells may be formatted, causing them to increase the file size even when those cells are empty.

The easy way is to run the Clean Excess Cell Formatting command. Enable the Inquire add-in from the COM Add-ins section, then on the Inquire tab, click Clean Excess Cell Formatting.

Another way to check this is to open the XLSX file in 7-Zip or any archive program. Go into the xl/worksheets directory and look for any sheets with a vastly higher size than the others:

excelsize.png

In this case, sheet 54 is the offender. Open the xl/workbook.xml and find the line with the matching ID:

<sheet name=”July2019″ sheetId=”182″ r:id=”rId54“/>

Delete the sheet from within Excel, or if you’re feeling brave, delete sheet54.XML from 7-Zip. When the file is next opened, Excel will inform you that the file is corrupted and it will recover the contents. Make a backup copy before doing this, though.

Using the format cleaner, the file size went from ~120MB to ~25MB. Deleting the XML brought it down to ~10MB, and opening/saving was unsurprisingly much faster.

Advertisements

Comments are closed.

Create a free website or blog at WordPress.com.

Up ↑

%d bloggers like this: