Back in January I posted a few ideas to reduce workbook size. This post got quite a few comments, so I’d thought I’d write them up into a proper post.
Also Gorden has noticed a strange thing happening, so I wanted to highlight that a bit better too.
Here is a check list!
- Zip the file!
- Used Range see here.
- Removing any VBA code
- Remove Formatting.
- Use Rob Bovey’s code clearer
- Duplicate pivotcaches (ethoros)
- Excel Filter Ranges (Gordon)
- Embedded Pictures (Jon von der Heyden)
- XL 2007, Store as .xlsb (sam)
- VlookUps, External data (Charlie Hall)
Please check back in the original post for explanations of these if they don’t make sense.
And here the one Gordon recently found, which is the odd one! What do you make of it?
I recently found a method that can reduce the file size of an Excel file containing significant amounts of VBA, sometimes by as much as 50%.
- Note file size
- Open file but do not enable VBA/macros
- Make no changes
- Save & close file
- Check file size again
Even after using Rob Bovey’s Code Cleaner this can sometimes make a huge difference to the size of the file. Many of the workbooks I manage are read-only and reside on a network share somewhere, and as this can make these files much smaller (1300KB down to 700KB etc.) it improves load times, particularly over VPN connections, resulting in an improved user experience.
The files work exactly the same after this is carried out and although I’ve done some examination to see what might have changed I’ve not managed to find anything. Most likely some internal decrofting* is responsible, but I’ve really no idea why this works.
I’ve just tried this on a file and it went form 7.2 megs to 6.6 megs, not bad for doing nothing.
I cant think what it would be that causing this, maybe P-code compilation or something?
* decrofting, some sort of Scottish thing. Crofting is small scale land sharing for framing, so decrofting must be the un sharing of land… or something!
Yes, croft is the Scottish version of cruft :)
Re: Size reduction by saving under new name.
Yes halving is possible for some files.
As I understand it an early definition of the microsoft office standard for documents used to include a copy of the previous document within the next saved version.
So save a document as a new name = original size, then mod the document and save again. Now both versions are saved within the current document! Double the size! As good an idea as dlls right!
This approach was originally to give you recourse if your current version became corrupted. Somehow you could extract the previous version, ie prior to the corruption.
Something like an undo after save option.
It seems to be inconsistently applied now but I used to find Arena documents, *.doe files were in this format and could be halved by saving under a new name. Arena is a discrete event simulation graphical programming environment now sold by Rockwell.
Best Regards
Richard
@VeryTidyBoy
No, not a new name, just open (without enabling macros), save, then close. You don’t need to make *any* changes to the file.
You can also reduce the file size by removing unused number formats and styles
I want to know just one thing…once I have deleted all the formattings from the excel and saved it..how can I get back my formattings? Lets say I send the file after removing the formatting to another computer, how will I get back all the formattings?
Hi,
You wont be able to get them back, you could copy them in if you had a copy of the original formates through.
Thanks
Ross