3

I have a 1MB xlsx Excel spreadsheet which causes Excel to stop responding every other minute or so. Excel never crashes but it becomes unresponsive for a while (10 to 40 seconds). Any ideas what could be causing it?

This is what I have tried / thought of - to no avail:

  • There are no external links (Data -> external links is greyed out)
  • No macros nor any user-defined function
  • I have tried 'open and repair' but the problem persisted; the log said "some parts of this workbook may have been repaired or discarded" which is the most unhelpful message ever - were they or were they not? If yes, which parts?
  • There are no data tables and calculation is set to manual anyway.
  • I have tried it on two PCs, both quite new and with 16GBs of RAM
  • This doesn't happen with any other file
  • It happens more frequently than the autosave interval of 10 mins but it happens even if I disable autosave altogether
  • It doesn't use any add-ins; I have also disabled every add-in
  • It's only 1 MB so it's definitely not a lot of data and the calculations are nothing fancy - mostly sums and multiplications.
  • I can't find any errors in the formulas; I had thought maybe something like an xlookup, which my version of Excel doesn't support, was used, but it seems not.

Unfortunately I cannot share the actual file because of confidentiality etc.

I have tried it with Office Professional Plus 2019 - I don't have another version of Office to test it with.

Thanks!

Improve this question
8
  • 2
    Could you have circular formulas? Go to File > Options > Formulas, under Calculation, select "Enable iterative calculation" and click OK.
    – harrymc
    Nov 17, 2020 at 13:05
  • 2
    I don't know how effective is this setting or if this is detected at all when the option is off. Try to enable it and set the maximum to very low number such as 1.
    – harrymc
    Nov 17, 2020 at 13:38
  • 2
    Have you tried copying the entire file's contents into a new file, save the new file and see if the problem persists?
    – Dave White
    Nov 17, 2020 at 14:36
  • 2
    @Pythonistaanonymous off the top of my head not that I can think of, but once you copied everything you can mass search and replace. Bit of a pain in the behind but I've found it's worth the trouble if it solves the problem.
    – Dave White
    Nov 17, 2020 at 22:07
  • 2
    Done as requested.
    – harrymc
    Nov 18, 2020 at 16:25

2 Answers 2

Reset to default
4

The solution for the poster was to open menu File > Options > Formulas, and then in the section "Calculation options":

  • Check "Enable iterative calculation"
  • Set "Maximum iterations:" to 1
  • Click OK.

My explanation for its working is that the spreadsheet contained cyclic formulas, but that without this setting they were not checked. Setting the maximum to 1 enabled the check for circular calculations, and stopped it after only one extra (too many) calculation.

enter image description here

Improve this answer
3
  • 1
    Thank you! It works but I don't understand why; if there is a circular formula (which I haven't found), when iterative calculations are disabled, Excel shouldn't get stuck doing them, right?
    – Pythonista anonymous
    Nov 18, 2020 at 17:12
  • 1
    Apparently when iterative calculations are disabled, then the check for them is also disabled.
    – harrymc
    Nov 18, 2020 at 17:13
  • 1
    What's odd is that if I go to Formulas -> formula auditing -> error checking, "circular references" is greyed out, which suggests there are none. Anyway, your answer fixed my issue - even though I still have no clue why - thanks!
    – Pythonista anonymous
    Nov 19, 2020 at 9:52
3

Try saving the file in an older xls format then resaving it back to newer xlsx or perhaps the binary format .xlsb

Do you use any volatile functions e.g. NOW, RAND, OFFSET or INDIRECT... or perhaps conditional formatting? In theory manual calculations should prevent volatility issues. Does the problem occur regardless of whether you are viewing or editing the file (or even if the workbook is not in focus)? Do you perhaps have other workbooks open?

Improve this answer

You must log in to answer this question.

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