what kinds of formulas or formula techniques slow down a workbook?

[union]

Hello Everyone,

I just started a new job and I have inherited a workbook that wasn't created by me. The workbook given to me is a massive workbook that as has about 10 different worksheets in it that have a ton of calculations. the formulas that are used in a significantly consistent basis is this workbook are offset, sumif formulas and they all these formulas tend to refernce other worksheets in this workbook. I was wondering if anyone can provide me any insight thanks.

[Kaper]

The type of functions is basically always data intensive. You can check if no unneeded reference to too large ranges or whole rows/columns - especially in parameters of offset are not used. second concept if in formulas there are nested all the time the same formulas (like finding last cell for offset etc then it could be wise to keep this data outside - calculated only once. either in a cell or in a named value.
Also check for array formulas - they tend to look very effective but are often not as quick as regular ones.

[MrShorty]

"Volatile" functions tend to slow down a workbook, because they are executed with each calculation event rather than fitting into the calculated dependency trees. Examples of volatile functions: OFFSET(), INDIRECT(), NOW(), and so on.

[TMS]

Avoid Volatile functions:

Excel’s Volatile Functions.
Some of Excel’s functions are obviously volatile: RAND(), NOW(), TODAY()

Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO()

Some are volatile in some versions of Excel but not in others: INDEX()became non-volatile in Excel 97.

A number of functions that are documented by Microsoft as volatile do not actually seem to be volatile when tested:

INDEX(), ROWS(), COLUMNS(), AREAS()

and CELL("Filename") IS volatile although a MSKBN article says its not.

One particular syntax of SUMIF is volatile in Excel 2002 and subsequent versions. This occurs when the size of the first range argument is not the same as the second (sum_range) argument.

Courtesy: http://www.decisionmodels.com/calcsecretsi.htm

If you use =TODAY() or =NOW(), instead of repeating the volatile function in every cell, put it in a single cell, maybe on a hidden sheet, and the refer to that cell ... only one volatile cell instead of potentially hundreds or thousands.


Avoid Array Functions with whole column references (Excel 2007+ has over 1 million rows).

Avoid "pre-preparing" formulae in multiple row/columns/cells based on, say, input in the first column.

Don't use excessive formatting, Conditional Formatting, Data Validation beyond the used range of data.

If you do use Conditional Formatting and add and delete rows or columns, you may end up with multiple references to the Conditional Formula relating to different blocks of data. This can be very slow.


Regards, TMS