Lean, mean file size in Excel

[tuxalot]

I have a workbook that contains 11 sheets, several VBA modules, and a couple of userforms. Total file size is 530k. No charts, few images. Seems a bit bulky.

I know Excel Bloat is a common problem in workbooks that are shared (across a lan for instance). For example: the file size for a scheduling workbook that I use at work often goes from 400k to 3 megs in a couple of days, with a team of 8 providing minimal input. Here's the kicker...The act of creating a "fresh" workbook, formatting it the same as the original, then copying the data from the old to the newly created workbook reduces file size back down to 400k. Why is this?

Making a long story even longer, but back to my point, would this process help reduce the file size of my workbook mentioned above? This workbook is an (11) sheet template file that resides on my server and is used by all (single user, not shared).

Sorry about getting so wordy. Thanks in advance for your help.

Darin

 

[StevePB]

Hi Darin,

How much data is there on each worksheet ? Are there any calculations or functions such as vlookup ?

I have often had to reduce the size of files passed around by people at work (typically a few thousand rows by around 8 columns), and have found that removing vlookups by copying and pasting back as values reduces the file size significantly.

There are some interesting suggestions at the following address:

http://www.microsoftexceltraining.com/Excel/ExcelProblems.htm

Regards, SteveB.

 

[GixxerG]

I've found that a common reason excel files appear unusually large is due to inadvertant cell formatting. e.g. if I've given global volumes of cells attributes that I didn't need to and this in turn has blown up the file size. Consequently to copy only the necessary cells to a new file eliminates all of the unnecessary extra cells and hence reduces the file size.
.

 

[tuxalot]

Hi Steve,

Yes there are calculation such as vlookup, as well as others. The strange thing though, I've recorded a macro that selects all cells from the old file and pastes all (paste normal) into a new file. This is of course, selecting all blank cells, all formatting, all functions, calculations, etc. Amazingly, the file size by performing such operation reduces the file size by 20-500% !!!

Strange but true. Maybe somebody could shed some light on this strange Excel anomoly.

Darin