Excel should calculate large workbooks more efficiently

[DKoinonia]

I work in a company that has not invested much money in databases, so the few
programmers that we do have are backlogged with database projects. As a
result many of us make due with spreadsheets, some of which are very
large/complex. Manual calculation is a normal setting for these files.

While working with these large/complex files, I have observed that Excel's
calculation process is inefficient. For example, if I edit the value of a
cell for which there are no dependents, calculating cells should not take
longer than a second or two. On the other hand, editing cells that have many
dependents should take longer...in my specific case, Excel takes minutes to
calculate.

My suggestion is that only cells for which a dependent has changed should be
subject to recalculation.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[JMB]

One thing that can also slow things down is if you use many volatile
functions which recalculate every time Excel does. Then, of course, those
dependents would have to be recalculated.

Some of them, I believe, are
OFFSET
INDIRECT
NOW
TODAY
RAND
RANDBETWEEN

 

[DKoinonia]

Agreed. We use VLOOKUP, HLOOKUP, SUMIF, and SUBTOTAL a lot, and when you're
dealing with large spreadsheets, those formulas take a very long time to
refresh.

Does anyone at Microsoft hear my cry, I wonder?

 

[JMB]

This site discusses excel's calculation. Maybe some tips in it. Also, looks
like they're product analyzes your workbook to help find calculation
bottlenecks. I've never used, so have no opinion, but they advertise Rob
Bovey has used it (he is an MVP on this site and although I don't know him,
I've found the opinions of the experts on this site to be well worth
listening to -maybe you could try to get his opinion).

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

Rob Bovey's site.
http://www.appspro.com/About/AboutAppsPro.htm

 

[JMB]

Another link (on the same site) that discusses speed
http://www.decisionmodels.com/optspeed.htm

 

[DKoinonia]

I'll check those out. Thanks!