How to make Workbooks smaller / Excel performance better

[samer.kurdi]

I have a workbook that is accessed by a lot of users using different
spec machines. The workbook is periodically updated with data added
from different sources in an automated fashion using macros.

Of late the file has grown from 23megs to a whopping 93megs, although
as far as I can tell the new worksheets and charts added should
*logically* constitute no more than a 20% increase in size from the
previous incarnation, and anyway most of the data is stored as values
(i.e. formulas removed after processing).

This growth in file size has resulted in increased loading times and
system slowdowns whenever the workbook is accessed.

Note that one thing that I have done recently is move a handful of
charts so that they are located inside worksheets rather than in their
own tabs. Could this have had such a marked effect on increasing the
size?

My questions are:
(1) Is there anything I can do to make the file smaller
(2) Why has the file grown so big all of a sudden
(3) What can I do to improve Excel's performance? I have 2 gigs of ram
on my machine and a really fast processor and still suffer from
annoying slowdowns when working with big files (and am pretty sure I do
not have viruses/spyware on my machine)

Any thoughts would be extremely appreciated!

 

[Otto Moehrbach]

Excel has a problem with determining the range, call it used range, in a
sheet. For instance, do this:
Open a new blank file.
Type something in cell E10.
Select A1.
Now do Ctrl - End. This should take you to the last cell in the used range.
Note that Excel selects E10. Good.
Now clear E10.
Select C5 and type something in that cell.
Select A1.
Do Ctrl - End again.
Notice that Excel selects E10.
You know the last cell in the used range is C5. Excel thinks it's E10.
The size of the file is due, in part, to what Excel thinks is the used
range.
The above example is over a small range. If the above were to happen over
thousands of rows and hundreds of columns, the difference in the size of the
file would be significant. Even huge.
Go to each of the sheets in this file you have and do Ctrl-End and see how
Excel's idea of the used range and your idea of it differ.
With the many users of the file and many sources of data coming into the
file that you describe, this may be your problem.
What you have to do, if this is a problem, is delete all the columns after
the real last column and the same with the rows. Then save the file, close
the file, reopen the file, and see what you have in size.
There are ways to automate this process of resetting the used range if your
file has many sheets. Post back with what you find after you follow the
above suggestion and if you need more. HTH Otto

 

[samer.kurdi]

Thanks Otto, for the tip...

You're quite right... Excel thinks many of my sheets end at column IV,
and thinks most of them end about 100 columns to the right of where
they actually do end. It also adds a few thousand rows on some
worksheets for good measure.

Moreover, even after I go in and select then delete the rows and
columns it still doesn't redefine its idea of where the sheet ends, and
pressing ctrl+end sends me back to the exact same ending cell every
time no matter how many times I try to delete rows/columns... Please
Help!!

 

[Gord Dibben]

samer

After the deletion of rows and columns you must save the workbook for changes
to become apparent.

In some versions of Excel a save/close/re-open is necessary.


Gord Dibben Excel MVP

 

[samer.kurdi]

Wow.... I was able to cut down the file size by way more than half,
back to a sensible 29 megs. Thanks a whole bunch!!