How to make Workbooks smaller / Excel performance better

[[email protected]]

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
<[email protected]> wrote in message
news:[email protected]...
>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!
>

[[email protected]]

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

On 22 Dec 2005 09:22:41 -0800, [email protected] wrote:

>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!!

[[email protected]]

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!!

[eMnocean]

WOW This is incredible. My file size went from 11.6MB to a mere 2.4MB. I was so worried the file was gonna be to big to work properly.

Although the selecting of the cells still seems to stall a tini bit.

Here are my questions:

1. Is there any way I can just cancel the cells not used? The default max ranges in an excel spreadsheet is like 65536 rows by 256 columns, so the idea would be to limit the default max range to my max used range. The way I have it set up is that the window doesn't even move (the pane is frozen), so I have no use for all the cells surrounding the first viewable cells on the screen.

2. If this is not possible, even if I delete the rage of cells to be the actual used range of cells(as the fix by Otto mentions), is the file size affected by all the rest of the not used cell ranges?

3. I also have shapes as buttons to navigate through the workbook, is that increasing the file size significantly? how can I compress the buttons?

4. Essentially I just need the workbook to not stall when being used. How else can I help it.

Thanks bunch~~

[vlady]

first you should start your own thread...
this thread is 7 years old....

[eMnocean]

Damn really. isn't it better if future viewers see everything? It would def help me if I was just learning excel for the first time.

AND, it doesn't really matter that the thread is 7 years old, the issue is still very much relevant.

[Cutter]

Hello eMnocean, and welcome to the forum. As Vlady has pointed out, the forum rules do not allow users to post questions in another user's thread.

Here's the rule:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[Phlopped]

I just managed to get a 124MB file down to 2.5MB by simply saving it as an open document file, then saving that as an excel file. hope that helps anyone else with huge excel files...

[eMnocean]

WOW, that's incredible !!

But what is an "Open Document" file? I don't see it as an option when I try to save. I'm running excel for mac 2011. Also quick question... would this trick damage any type of Conditional formatting, buttons, data validations...or any thing of that nature?

[Cutter]

eMnocean

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc

Start your own thread.