Excel - Long Lag on Data Entry

[alwise]

Greetings,

I have a 195MB workbook with 7 worksheets.  The only data entry cells are on 1st sheet, about 50 cells. My problem is that there is a 10-12 second delay on entry to any one of these cells. Calculations time is only 2-3 seconds, which is acceptable to me. I've set to Manual Calcs, I've removed all conditional formats; in fact I've cleared all formatting for the other 6 sheets, with only some bolding and merged cells on sheet1. I've disabled all excel and com add-ins. 

Strange too, is that occasionally I can get immediate response when entering, meaning I can move to new entry cell. But if I were to delete the value from that cell the workbook will hang for 10+ seconds.

I'm at a loss here. Any help would be greatly appreciated.

Thanks, Art

[#2]

@alwise 

195 MB is a huge size for an Excel file. What's the reason of this?

If you have only 50 cells for data entry on first sheet, what's there on other 6 Sheets?

Are you having some formulas on other sheets which involve some kind of cell comparison or formulas which refers to whole columns or rows?

Maybe it's time for you to revise those formulas. If formulas are complex, make some helper columns and simplify the formulas.

Also, select each sheet and press Ctrl+End to detect the last cell used and if you find that irrelevant, delete the unused blank rows and columns.

Make sure that you have not copied down the formulas to large number of rows.

You may also consider saving your file as binary file .xlsb to reduce the file size.

[#3]

@Subodh_Tiwari_sktneer Thanks for your reply. I have saved as binary file and double-checked all used ranges. My formulas are very simple, and do not use sumifs, etc or refer to whole columns or rows. The 2nd thru 4th sheets are tables of helper columns, simple calcs. The 5th thru 7th sheets are essentially cartesian products of tables in 2~4 referencing those helper cells directly. I did all matrix lookups in vba and hardcoded the appropriate cell references in these larger tables 5~7. 

So I don't believe calculations are the issue. After whatever is happening on data entry is done and I can again control movement to another cell, the calculations themselves take only a few seconds. Given the complexity of the application I find that acceptable. I have essentially built this is access as well, with the caresian product tables already defined. The queries to join these tables with 'helper' tables providing data for calculations, which in this case must be done by the query, are much slower than can be done in excel.

It's really only the data entry lag that has me concerned. Best debugging clue I can provide is that 1st entry after a full recalc will lag. I can then do multiple additional entries without issue. BUT, if I instead delete the value in the cell I'd just entered, I will experience the lag again.

The workbook is large but formulas are not complex and books calcs quickly. There is something else causing this lag I believe.

[#4]

Hi folks, I think I understand what was causing the delay. It was a calc issue in a sense. 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 across 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.

[#5]

Hi @Subodh_Tiwari_sktneer, You have a 195 MB huge size Excel file and you don't know the reason of this.  You have done most of the things to optimize the file for making calculations work faster but still it is slow.

Recently, I faced similar problem with an excel file in which used to enter data & perform calculation on daily basis and one day it was taking more time in calculations, switching to sheets and while opening & closing.

After perusing the data I found that by mistake some one pasted the several images of a cell instead of copy paste of value from one cell to another. The data entry guy who just copied the value from a cell & pasted a image to below cell. Later on again he copied the data from pasted cell and this time he copied that pasted image also and again pasted in the cell below to it. This action was done many a times and due to this i was having several images in my excel file. This resulted in slow processing the file.

I identified those images & replaced with data into the cells and issue was fixed.