0

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.

Improve this question
9
  • 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
    Jan 21, 2016 at 19:56
  • 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
    Jan 21, 2016 at 19:57
  • 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.
    – Aganju
    Jan 21, 2016 at 19:59
  • 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.
    – Manuel Aldunate Barros
    Jan 21, 2016 at 20:07
  • 1
    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
    Jan 22, 2016 at 8:38

2 Answers 2

Reset to default
1

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.

Improve this answer
3
  • 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).
    – Manuel Aldunate Barros
    Jan 21, 2016 at 19:39
  • @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.
    – BruceWayne
    Jan 21, 2016 at 19:41
  • 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..
    – Manuel Aldunate Barros
    Jan 21, 2016 at 19:45
0

I had this issue and found that the culprit was in my new rules for conditional formatting. They had circular reference. Once removed, everything started working normally. Thank you Aganju for the hint. When managing conditional rules, ensure to select This Worksheet from the drop-down Show formatting rules for. Otherwise you don't see all rules.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .