0

I have a .xls file with PivotTables that is 500mb large, and when I convert it to an .xlsx format, it drops to a mere 5mb. Even after clicking Data>Refresh All which refreshes the PivotCaches, the file size remains small. The same size reduction occurs if I save as .xlsm or .xlsb.

Microsoft's documentation states that XML format and ZIP container is much more memory efficient than binary documents. I have no reason to believe data loss or corruption occurred as functionality remains the same, is compression a plausible explanation for such a drastic (90%) reduction in file size? Is there a way to actually test that the file size reduction was caused by the compression?

Thank you.

  • idle curiosity: have you tried ZIPing the XLS file? If yes, how big is the resulting ZIP archive? – cybernetic.nomad Jul 23 at 16:05
  • @cybernetic.nomad the resulting ZIP archive is 50mb – dekinai Jul 23 at 16:54
1

.xlsx files, like all their post-2007 Office cousins such as .docx, .pptx etc are zip containers.

You can change the extension to .zip and take a look at the contents of the file and folder structure (more interesting for documents with media in such as PowerPoint files with images). If you unzip to a folder you should be able to see the relative sizes.

I would not be at all surprised to find 90% compression ratio in many spreadsheets, as there is a lot of repetition with similar formulae etc. I seem to remember reading an article a long time ago (when these formats were relatively new) about the way Excel stores bits of formulae to give compression the best chance of big file size savings.

  • While it is reasonable to assume that zipping XML will achieve a high compression ratio, .xls files are not XML. – Blackwood Jul 23 at 16:40
  • Yes, XLS is based on BIFF (Binary Interchange File Format) and as such, the information is directly stored to a binary format. On the other hand, XLSX is based on the Office Open XML format, a file format that was derived from XML. The information in an XLSX file is stored in a text file that uses XML to define all its parameters. Based on Open XML Formats and file name extensions, files are automatically compressed and can be up to 75 percent smaller in some cases. – Herb Gu Jul 24 at 8:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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