Excel freeze when I change the data of only one cell

Question

Windows 7 Pro, Office Excel 2013, i7 last generation processor, 8 GB RAM.

I'm working on a project of about 25 MB, a lot of index formulas, tables (not range) and everything is local(the info I use is in the same document).

But I have this only sheet that is slower than the rest (the calculation options are in manual mode always), and in these sheet when I change the number of an specific cell, it freezes for about 40 secs (only if I change the data, not with the calculation), also this cell data is used in only 4 cells of the document in different sheets.

I don't know what is happening because I'm in manual mode with formulas. Also in that same sheet when I try to add new rows, it get stuck too.

I have recently had the same problem and I solved it by copy/paste all my work into a new excel workbook. But after 2 weeks It appeared again.

I also don't have any macro or conditional formatting, the document is on my computer (not a network).

In particular: When I change the data 1 time it freeze, after this if I dont calculate the sheet again, I can change the data freely with no problem. But when I calculate again and then change again the data of the cell it freeze.

Solution

Having lots of index/matching can really slow the worksheet down, especially if it's tons of data. I'm assuming your data is in a table-like format, and not just random formulas everywhere.

Say you have an index/match that is in range A1:A1000. Keep A1 as a formula, but take A2:A1000 and copy/paste special as Values. This will keep a formula, in case you need to run it again, but will greatly speed up the worksheet.

Also, check if you have any Volatile functions which could slow it down as well.

Finally, do you have any Worksheet_Change type events in the sheets? That may also contribute to the size/slow down.

Comments

[Aganju]

Did you try to hit CTRL+BREAK during the delay? If it is running a macro, it would stop there and pop up the debugger; obviously you know what's the problem then.

[Aganju]

Try removing all conditional formatting (don't save) and do it then. If the problem is gone, you can selectively remove them to find out which one is it.

[Aganju]

I had similar issue if you have the same formula copied in a large range - the first time you edit any of those fields, Excel needs to internally break that 'they are all the same' into 'now I need a gazillion copies'. That's a bit fuzzy worded, because i have no access to Excel code, but that's how it seems.

[Manuel Aldunate Barros]

So what do you recommend to do? Imagine that 8 sheets have to access to this cell, and 2000 formulas in each sheet. The thing is that I already have this problem befor with way fewer data, and a copy/paste everithing in another sheet and the problem dissapear, and now 2 weeks later it appered again, with way more data.

[Dave]

Lots of formulas and stuff - Sigh... If you're not going to be clear then we can't understand ;) . Please explain what is stuff. Also where is the file saved (locally, network). This is a very strange issue to debug. Please also clarify, is the issue it's slow when you change the data of the cell, or when you perform the calculation? You're best to edit your post with this data so others can read it too :)

[Dave]

Try opening the file from command prompt, using Excel /safe to run in safe mode. And turn off hardware acceleration

[Dave]

Are you using tables in the worksheet(s) or ranges only?

[Manuel Aldunate Barros]

With safe mode: No result! Thanks @Dave. I edited again my post. I use tables.

[Dave]

Using tables like this is a known issue. I can't Cite the posts as out and replying Via mobile but if you move to range only, the issue will go. I don't believe you will find a fix.

[Manuel Aldunate Barros]

I appreciate your contribution. But is not about the calculation speed. Because I only recaclulate with F9 , cause I have the calculation on manual mode. When I change the value of only 1 cell (B3) it takes a lot of time (in the others is fast), and when I add a row, only in one of the sheets (the cell B3 with the problem is in the same sheet).

[BruceWayne]

@ManuelAldunateBarros - I'm thinking it's freezing as it's recalculating all the formulas. I have had a spreadsheet with TONS of formulas, and when I'd update it (or run a macro), it'd look like Excel froze, but it was actually running behind what appeared to be a frozen screen. That's why I mention trying to reduce the number of live formulas.

[Manuel Aldunate Barros]

Yeah I thought that too, but is not all the time, like 1 of 3 times I change the number it collapses. I was thinking that is more about a corruption of the worksheet or something like that... I really don't know..