Unexplainably Large (empty) Excel File
I have an Excel file that is ~30MB and it contains nothing but a blank sheet. It has no data, tables, pictures or formatting - it is simply a blank sheet. I'd like to understand why this has happened and how to remedy it, please read below to understand how I generated it.
I started with a workbook with 40+ sheets but upon seeing the size was 35MB I tried troubleshooting the size by regular excel size reduction methods (e.g. formatting, deleting empty cells etc). This had no impact and hence I started deleting sheets one by one to identify which sheet was the culprit and after deleting all sheets (except a new blank sheet) the workbook was still 30MB. I've also used an Add-in (Get Sheet Size) to identify which sheet is the culprit and the output states that each sheet is 30MB+. Clearly this doesn't add up if there is many 40 sheets as it would indicate the file was 1.2GB which it isn't. This tool normally works effectively which seems indicate there is a more fundamental issue with the excel file rather than something trivial
Please can you help me understand why this file is so large and how I can reduce it's size?
XLSX files are zipped files. I Unzipped this file and found the culprit under the directory /xl/externalLinks
As the sheet was empty and couldn't have contained External links, So I navigated to Formulas > Defined Names > Name Manager to look for defined names and found that there were a lot of defined references.
After deleting these references the file size was reduced to 21kB.
By default Excel does not save or allocate cells that are empty, but it is possible to have a sheet that is full of nothing but empty cells with some formatting applied.
I believe I've managed to do this by applying "transparent" formatting to every cell in a sheet. The cells are technically empty, but are allocated due to being formatted.
In the past I have simply deleted all the empty rows in a sheet in order to reduce filesize, or deleted the offending sheet after copying out only the sections I cared about.