Recalc on HUGE workbook

[Steph]

Hello. A guy I work with has a workbook that is 150MB large. He has a ton
of SumIf's and Lookups in it, and the calc time is extremely slow (like
hours!) He has it on manual recalc, but every time he hits f(, it takes
hours - I think becasue Excel is recalculating the entire workbook, right?
So, is there a quick fix that will allow him to recalc only the cells that
were affected by a change, instead of recalcing all of the thousands of
formulas? Or any suggestion to speed it up other than simply rewriting the
entire workbook to run more efficiently! Thanks!

 

[Fredrik Wahlgren]

Hello. A guy I work with has a workbook that is 150MB large. He has a ton
of SumIf's and Lookups in it, and the calc time is extremely slow (like
hours!) He has it on manual recalc, but every time he hits f(, it takes
hours - I think becasue Excel is recalculating the entire workbook, right?
So, is there a quick fix that will allow him to recalc only the cells that
were affected by a change, instead of recalcing all of the thousands of
formulas? Or any suggestion to speed it up other than simply rewriting the
entire workbook to run more efficiently! Thanks!

According to this URL http://www.decisionmodels.com/calcsecrets.htm, Excel
will do a full recalculation when there are more than 65536 dependencies.
Since this workbook is so large, I think the limit has been reached. This
suggest that the only way to ensure smart calculation is by breaking it up
into smaller files.

/Fredrik