# What slows down Excel



## mkw4949 (Jul 30, 2008)

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.


----------



## Smitty (Jul 30, 2008)

See http://www.decisionmodels.com/ for some good tips.

HTH,


----------



## Jonmo1 (Jul 30, 2008)

All the volatile functions will decrease performance.
Here's a list
http://www.bettersolutions.com/excel/EDK153/ME810324332.htm

And yes, array formulas definately decrease performance.
I don't know about the SIZE, but performance at least.

But sumif is not considered an array formula..

SUMPRODUCT and anything enlosed in {} (requiring Ctrl+shift+enter) is an array. Probably some others, but I'm not sure...

Also, using entire column references will decrease performance.
like Vlookup(A1,B:C,2,FALSE), instead of Vlookup(A1,A1:B1000,2,FALSE)

Consider using dynamic named ranges instead
http://www.cpearson.com/excel/excelF.htm#DynamicRanges

Hope this helps...


----------



## mkw4949 (Jul 30, 2008)

Thanks for your imput! not the answer I was hoping for but oh well.


----------



## iliace (Jul 30, 2008)

Here's an article on MSDN (for Excel 2007 but largely applicable to previous versions). Also from the Decision Models folks.

http://msdn.microsoft.com/en-us/library/aa730921.aspx

Another useful article on the calculation chain in general:

http://msdn.microsoft.com/en-us/library/aa140058.aspx


----------



## PA HS Teacher (Jul 30, 2008)

My understanding is that the Direct and Match functions are not volatile, and hence do not recalcuate unless directly affected by changes in the sheet.

Are you using Indirect to deal with variable references within a sheet?  Or linked to other files?  Could you provide an example of a couple types of Indirect based formulas you are using?


----------



## mkw4949 (Jul 30, 2008)

The formula in question is:

=IF(OR(U$9<$C13,U$9>$C14),0,NORMDIST(U$9,AVERAGE(OFFSET($T$9,0,$C13):OFFSET($T$9,0,$C14))^$C23,STDEV(OFFSET($T$9,0,$C13):OFFSET($T$9,0,$C14)),$D23)^$C22)

What it does is set up a curve over which I am distributing costs. based on simple input cells the user is able to change the type of curve (i.e. bell curve, straight line, front and back loaded) and the time frame over which the costs will be incurred. Specifically the time frame is driven by the Offset function. So the right now it's not a huge "Speed" problem but it is creating a very large file. so I end up needing the above formula for each month of cost for each cost. so I have say 5 different cost line items and each cost runs for 10 years (120 columns one for each month). so you can see how the number muliplies FAST.


----------



## mkw4949 (Jul 30, 2008)

The Crux of the above formula is the NORMDIST function.


----------



## Smitty (Jul 30, 2008)

What I generally do with "mega formulas" is keep a spare or master formula and only apply it where and when I need it.  Then paste values for everything that's been updated.

Once you figure out a methodology that works for you, you can write a macro to apply it when and where needed and then value it out.


----------



## PA HS Teacher (Jul 30, 2008)

Try Replacing These types of reference:
OFFSET($T$9,0,$C13)

With something more like this:
Index($T$9:$IV$9,$C13)


----------



## mkw4949 (Jul 30, 2008)

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 (Jul 30, 2008)

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


----------



## PA HS Teacher (Jul 30, 2008)

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


----------



## iliace (Jul 30, 2008)

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 (Aug 1, 2008)

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


----------



## mkw4949 (Aug 1, 2008)

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 (Aug 4, 2008)

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


----------



## mkw4949 (Aug 4, 2008)

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 (Aug 4, 2008)

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 (Aug 4, 2008)

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 (Aug 4, 2008)

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.


----------



## mkw4949 (Jul 30, 2008)

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.


----------



## Stormseed (Aug 5, 2008)

mkw4949 said:


> 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”?


 
In accordance with the points mentioned above, your file size would increase considerably with using cell colors and most importantly *borders* as well. Moreover, my personal experience - Excel file size will zoom up when you use Array Formulae over large ranges. Another problematic function is OFFSET(). *T*ry not to use it or your will experience problems later on..also, replacing OFFSET() with INDEX() is a very wise decision, I should say that too ! It does help extensively for faster calculation and also reduces the file size.


----------

