21

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?

Improve this question
4
  • 9
    If it has no data - just delete it. We can't guess what was done to it in the past.
    – harrymc
    Dec 7, 2021 at 17:20
  • 4
    I haven't checked the actual file, but the screencap in @SaaranshGarg's answer suggest that there may be data leakage by leaving the file up for download (especially the "refers to" column). Note that the xml files can be opened directly in notepad++ or really any text editor or browser after unzipping. 100MB xml files may have more than a URL to JRP Solutions in BC. Might be a real security risk.
    – Yorik
    Dec 7, 2021 at 19:46
  • 3
    (1) Welcome to Super User. It would appear that you have accidentally created two accounts. This will interfere with commenting, editing your own posts, and accepting an answer. You should use the contact form and select ‘‘I need to merge user profiles’’ to have your accounts merged. In order to merge them, you will need to provide links to the two accounts. For your information, these are superuser.com/users/1647607/ollie and superuser.com/users/1647617/ollie. You’ll then be able to edit your question, if necessary.  (2) Are you doing “Save” or “Save As…” ?
    – Scott - Слава Україні
    Dec 8, 2021 at 18:27
  • May not be the issue but It's possible to hide sheets in excel
    – JimmyJames
    Dec 10, 2021 at 16:19

2 Answers 2

Reset to default
58

XLSX files are zipped files. I Unzipped this file and found the culprit under the directory /xl/externalLinks enter image description here

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.

enter image description here

After deleting these references the file size was reduced to 21kB.

Improve this answer
1
  • 26
    When I say "This file"; the question earlier contained the file which is now redacted for privacy reasons.
    – Saaransh Garg
    Dec 8, 2021 at 17:06
5

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.

Improve this answer
2
  • 2
    "Clear formatting" is a menu command which removes all formatting. It would certainly be possible to explicitly add formatting which looks like the default formatting: is that what you mean here?
    – Andrew Leach
    Dec 8, 2021 at 12:47
  • @AndrewLeach yes, basically formatting that just looks unformatted. Someone kindly edited my answer to clarify.
    – Mokubai ♦
    Dec 8, 2021 at 22:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.