Lag on First Data Entry after Recalc

[alwise]

Hi, I'm using Excel 2016 64bit on Win10. I have a 195Mb workbook, 7 sheets. Set to manual recalc and the book calcs in 2-3 seconds which is fine. My issue is that enter entering a value in one of the 50 or so input cells (all on sheet1), excel is typically unresponsive for up to 20 seconds before I can enter another value.

The first entry after recalculating always causes a lag. After that I can change several cells without issue and manual calcs without problem, with one exception. If after entering 1st value if I delete that value I again get a lengthy lag.

I don't think it's a calc issue but there are no volatile functions. I've used vba to create cell references to precedent functions, I'm not using any index functions. I removed all formatting and conditional formatting. I've disabled add-ins, turned hardware acceleration off, made microsoft xps default printer, moved input sheet to separate sheet. Nothing has helped. I've used tables on data in other 6 sheets and converted back to ranges, no impact.

 

[DRSteele]

Welcome to the forum.

I suggest that the calculation lag is purely a consequence of having such a large file - 195Mb is huge.

Is there any of it you can move to an external file? Better yet, is it the case that a large portion of the file is taken up by raw data? In so, the data could be stored in the Excel's Data Model and accessed through PowerQuery or PowerBI instead of residing in the worksheet.

 

[alwise]

Welcome to the forum.

I suggest that the calculation lag is purely a consequence of having such a large file - 195Mb is huge.

Is there any of it you can move to an external file? Better yet, is it the case that a large portion of the file is taken up by raw data? In so, the data could be stored in the Excel's Data Model and accessed through PowerQuery or PowerBI instead of residing in the worksheet.

I've used powerquery in the past. I can't say I've seen a big advantage to using datamodel versus using access backend with either PQ of MS Query. Again, thougn, I don't believe it's a calc issue. I can calc in a few seconds (powerquery would be much longer).

 

[alwise]

Solved...while it's true that calcs only took 2-3 seconds, the delay on data entry appears to have been the result of excel marking all dependent cells (which cascade accross helper sheets) as dirty, i.e. smart calculation.

The workaround for me was using vba ActiveWorkbook.ForceFullCalculation = True, which turns smart calcs off. I then can enter values either manually or by vba, and the resulting calc time is still far less that the smart calc updates required, marking each dependent cell as dirty on data entry.