Calculation performance

[Vinod]

Hi all,

I've a workbook with hard coded formulas without VBA code. Which includes
Groups & outlines, name ranges, conditional formats and functions like if,
sumif, indirect, arrays, etc., for each change in a single cell taking too
much of time for recalculating the workbook. And also the same time consuming
for recalculation during groups expanding and collapse.

Any one can help me how to recalculate only dependent cells and conditional
formats for the same.

And how to stop recalculation during expanding and collapse groups.

Advanced Thanks,
Vinod.

 

[Jim Thomlinson]

Check out this link. It has great explanations abut what XL is doing in the
background that may be adversely effecting your calculation performance...

http://www.decisionmodels.com/index.htm

Specifically read the sections on Calculations Secrets and Optimising Speed...

 

[Conan Kelly]

Vinod,

the INDIRECT() function (along with several others) is a volitile (<-- I
think that is the correct terminology) function. This volitile function is
what is causing your workbook to recalc at the slightest change. Search for
"volitile functions" in XL's help (or Google).

If it is necessary to use volitile functions, then you will probably want to
create a way to toggle automatic/manual calculation and only recalc ([F9])
when necessary.

HTH,

Conan