Bloating files in Excel 2007
 

Hi,
I have noticed that some of my Excel 2007 files seem to be getting
significantly larger without any obvious reason when opened and resaved. I
have a set of files that contains reasonably large data files and sets of
macros to analyse the data plus a few graphs linked to the output of the
analysis. One particular example contains the original data sheet (no links,
no formulae) with 11 columns of 98000 or so data points, a few empty sheets,
a sheet for the main macro looking at the data (not very large), a further
sheet with macro buttons to do some frequency analysis (perhaps 12 columns
with 98000 data points) and then 6 chart sheets linked to the various output
data summaries. The file started at about 8Mb (xlsb format). I then changed
the order of some of the original data columns and reran the macro etc and
the files has grown to 15 Mb, though it contains exactly the same size of
data sets and outputs (and the same macros and the same charts - though the
outputs are a bit different)... I can find no reason for the suddenly almost
doubling in size and have tries to no effect) to save the file as a .xlsm (24
Mb), close Excel, and reopen the file and resave it under a new file name as
an xlsb file but this has no impact on the final size. I can get back to the
original file size by reopening the smaller file and simply copying my
resorted data in to the first sheet, then run my macros etc and then save the
file, resulting in what should be an identical file to that which seems to
have doubled in size. But, as I am reusing my (rather large and involved
macro code and using many data sets, this is by no means a trivial
undertaking.
Does anyone have any ideas about possible causes for the file bloating and
remedies against it? Is Excel now suffering from the same malaise as has
affected Word and PowerPoint for quite a few versions (ie saving all sorts of
undo information that cannot ever be reused)? In this instance, I cannot even
try saving as an Excel 2003 file and then resaving as an Excel 2007 file as
my data sets are now too large (98000 rows).
Oh yes, another (annoying) perhaps related (?) symptom: for the files which
have suddenly doubled in size, I occasionally seem to have lost the format of
two of the charts (flat 2D surface plots) and these come up in one of the
Excel 2007 monochrome surface plot style, sometimes also set to a tiny zoom
with the charts taking up a few square cm in the centre of the screen but
changing the plots back to my original template and more appropriate zoom
does not reduce the size either.
Any help or suggestions would be gratefully appreciated.
Many thanks and best wishes, Boris