Tools for diagnosing performance extremes of Excel calculations

[6StringJazzer]

Are there any tools or techniques available to diagnose performance behavior of Excel workbook calculation? I have a workbook that requires anywhere from 50% to 97% CPU utilization to recalculate, sometimes for durations measured in minutes.

There are about 45,000 lines of data of employee timesheet charges. I have another sheet that maps each charge number to a project (many to one) and also shows that latest date that number was charged, using array formulas that look at the raw data. Another sheet shows the latest charge number charged by each staff, again using array formulas that look at the raw data.

Although I know the candidates that could be causing this, and I am confident I can resolve it by trial and error, I was wondering if there are tools that perform dynamic analysis of Excel execution, similar to how there are dynamic analysis tools for executing conventional code.

[Domski]

Fast Excel might be worth a look: http://www.decisionmodels.com/index.htm

I've never tried it myself though.

Dom

[MarvinP]

Hey 6SJ,

I've always done it manually in my code. I did find a huge help in making Calculation Manual and then doing a Range.Calculate on my own so Excel wouldn't try to calculate on it's own with each value change.

After looking a while for sites, try http://betterandfasterdecisions.com/...in-excelfinal/ to see if it gives you any ideas.