What slows down Excel

[mkw4949]

i have a general question about the type of formulas that will slow excel down and inflate the file size. For example from what I've heard using an arrey formula will generally (i.e. sumproduct, sumif or anything inside []) slow down you spread sheet but will it also increase the file size? What about the Offset formula? If I use ALOT of Offset functions will that severely impact my spread sheets performance. And I do mean ALOT as in thousands.

Also what directly impacts the file size and how can I better manage this? I am a pretty advanced user and I've looked in books but have not found the answer. I would greatly appreciate any help. Thanks.

 

[mkw4949]

won't INDEX return a value? I need it to return a Range. I can try though. Thanks

 

[PA HS Teacher]

It will return a range, the same way entering =A1 will return the range A1.

 

[iliace]

You could use a single OFFSET.

=OFFSET($T$9,0,$C13,1,$C14-$C13+1)

instead of

=OFFSET($T$9,0,$C13):OFFSET($T$9,0,$C14)
I doubt that will improve performance significantly, but it's a start.

 

[mkw4949]

I replaced my OFFSET functions with INDEX and it works like a charm. Thanks man. you rock!

 

[mkw4949]

This is true. my goal was to eliminte the volitile functions completely though. I'm using INDEX which is works. However I've heard conflicting reports regarding INDEX. Depending on the source I've heard that INDEX eith is or is NOT a volitile function. I'm using over 1,000 of the formulas in my spread sheet so every little bit will help.

 

[RoryA]

The longer your formulas are, the larger your file will be. (as well as obviously the more data you have!)

 

[mkw4949]

so file size is strictly a function of the number of characters contained in the file. Does this mean that if your file references another worksheet in the same file you will increase the file size as well? For example does “A1” take up less space then “Sheet1!A1”?

 

[RoryA]

I think that there are other factors that will influence file size, but formula length is one. It stands to reason really - the formula string has to be stored somewhere. I doubt you will see a huge difference between Sheet1!A1 and A1 (other than the possible side effect of cross-sheet dependencies) but if you have a large number of long formulas, your file size will increase. You can see this by replacing any long repetitive formulas with defined name formulas with short names. Same calculations and values, but smaller file.

 

[erik.van.geit]

Hello,

There a bunch of unknown reasons why filesize increase. It's one of the major problems that they seem not to get solved. Once I have changed one single character in one single cell with no dependencies in a file to get 10 extra Mb. Another time the same effect just unhiding and hiding rows.
This seems to appear when one was working a long time on the same worksheet(s) and within the VBA project. Creating + deleting, moving objects, cutting cells, ... at a certain moment Excel seems to produce quite some garbage in the background: files get corrupt ...

Advice: once the project is about finished and ready for release, then you should really take the time to REBUILD your workbook

kind regards,
Erik

 

[PA HS Teacher]

I agree with Erik.

When I have larger workbooks, I have had many workbooks that are radically smaller when I rewrite them from scratch, or else paste small pieces at a time, saving and measuring the file size as I go to identify any bottlenecks. I've found that the majority of the time the new file is smaller than the original.

This is especially true in workbooks where I have done a lot of 'stuff'. Adding/Deleting rows, Adding/changing a lot of formatting, generating many tables, or using many formulas.

I too have seen some extreme examples, 10's of MB.