File Size Management

[cubs610]

Hi Everyone,


Ok- I'll admit this question is subject to interpretation. A lot of people in my office are looking for "file size management techniques", or put another way they cram a lot of data into a workbook, it loads slow and now they want to fix it.


Any thoughts....


Thanks,

Dave

 

[Hui]

There are a few basic rules with regards to minimising file sizes


1. Only produce Pivot tables and Data Tables as needed and remove when finished

2. Don't add columns which are simply based on other columns but don't feed else where

such data can be added at report time

3. Delete columns and rows which don't have any data past the limits of your data from Time to Time

4. Save as Excel Binary Files (*.xlsb), these are generally about 30-50% smaller than *.xlsx and open faster as well without any loss of functionailty

5. Limit use of Volatile Functions, these don't save space but will allow your workbook to calculate faster when it needs to.


That's a start,

But I'm sure there are more we can add?

 

[dan_l]

If you've got multiple pivot tables, always source them from the first pivot table.


Also, if you've got a bunch of formulas that don't need to be constantly calculating, just copy/paste special values.