Why the lag before Excel calculates?

[PattCash]

Does anybody know the reason for the lag before calculation in a large workbook? And potentially how to resolve...


Specifically, when I change a particular input it takes a minute to complete the calculation - during that minute I can look at the Processes tab of Task Manager in Windows and can see for the first 57 seconds Excel is Not Responding and the CPU % doesn't change. In the last 3 seconds the CPU % shoots up and the calculation progress appears at the bottom of Excel.


What's happening in the first 57 seconds? Is Excel assessing calculation chains perhaps? The worksheet has approx 18 million cells with formula (which I appreciate is a lot) but only about 50 unique formula (calculations are in 360k-cell blocks) and so I wouldn't have thought the calculation chain is particularly complex. Calculations themselves aren't particularly complex either and no volatile functions / arrays / sumproduct etc.


It feels like Excel is assessing the volume of the task for a long time (57 secs) and then quite quickly does the job at the end (3 secs)... I appreciate reducing the volume of formula will probably speed things up but I'm interested in understanding the reason for/what's happening during the initial lag.

Any thoughts would be appreciated.

Dan

 

[Joe4]

The worksheet has approx 18 million cells with formula

Whoa! That is a lot of cells with calculations!

With Auto Calculation, Excel determines which cells need to be re-calculated when a change to the data is made. It does this by tracking "dependencies". However, there is a limit to how many "dependencies" it can track (it may be something like 1 million). So, when you get over that number, since it cannot track the dependencies any further, it re-calculates ALL calculations in your workbook every time you change your data!

What you can do is change the calculation mode to manual. Then when you change data, it will not automatically do any calculations. When you are ready for it to recalc, just press F9. Then it will take about a minute to do ll the calcs, but at least it only does it when you are ready for it to, and not every time you change any cell. This is especially helpful if you have to update multiple cells.

However, when I come across something like this, it usually a red flag that you are probably using the wrong tool for the job. If you have that many formulas, I am guessing what you probably really have is a relational database. As such, a database program like Access, SQL, MySQL, or Oracle is the more appropriate tool to use for something like that (though people can make Excel work like a relational database, it is NOT a relational database program).

 

[PattCash]

Joe, thanks for such a prompt and detailed response. Yes certainly a lot of calculations - I would never normally build a workbook this size, and I do have the option to scale it down significantly, but was interested particularly in what was occurring during the lag. I'm certainly across the dependency tracking/ dependency tree, "dirty cells" etc, but hadn't realised there was a limit to the number of dependent cells that can be tracked.


Interestingly, changing some inputs results in no lag before the actual recalculation, others a long one, and others somewhere in-between. The fact there are varying lengths in the lag (i.e. Not Responding) suggests something else is going on other than simply everything being recalculated due to this 1m limit being reached, as otherwise they would presumably then take the same period of time.



I'm assuming the lag therefore is perhaps Excel assessing the existing calculation chain, determining it needs rebuilding due to number of dependencies, rebuilding it and then calculating. But I'm surprised the CPU usage doesn't increase during this.


The workbook is a revenue forecasting model and I need to keep to Excel. I can reduce size by rolling it up to a higher level but have a feeling structuring existing calculations will help optimise it to an extent also.


I'll be interested in doing more research around the dependency limit you mention. Any further thoughts also welcome.

 

[Joe4]

I'll be interested in doing more research around the dependency limit you mention.

I came across it some years ago (10+) when supporting someone who had workbooks of Excel files with 20-30 sheets of data, spanning dozens of columns and tens of thousands of rows, mostly with VLOOKUP formulas to other sheets.
It wasn't readily apparent, and I had to do some in-dpeth digging using Google searches, but I did find some information on it. So I would recommend starting there.