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!
