1

I have an Excel file that has a happy number of sheets with various formulas, ranging from simple sums to far more complex array formulas.

I have always had the sheet set to automatic calculation, but recently due to the size of the file, around 60mb, I changed to manual calculation. When I hit F9 to run the calculation, the status bar changes to Calculating: (4 Processor(s)): 0% and then sits there.

No matter how long I leave the calculation running, the longest time being overnight, the calculation never rises above 0%. Hitting escape will stop the calculation without issues, indicating Excel itself has not crashed.

I am able to stop the calculation at any point, save and close, and reopen the file without any issues.

I have deleted the most recent array formulas added to eradicate any errors this might have caused.

Has anyone any tips for what else I could check to see what is causing this issue?

  • Can you check the same workbook on another pc? Seems to be an issue with your office installation. – Máté Juhász Mar 29 '16 at 15:16
  • I have tried that with the same issue. I am currently working through a duplicate copy of the document delete one worksheet at a time to see if there is one in particular causing the issue. – Tim Wilkinson Mar 29 '16 at 15:31
  • Try creating a new workbook, add some simple calculations (do not copy from the other workbook), then test using manual calculations. If it works as it should, the workbook you are having trouble with is likely corrupt. If it presents the same problem, conduct a repair install of Office and try again. – CharlieRB Mar 29 '16 at 15:42
  • Once your spreadsheet gets to 60MB you should really start looking at different technology to present the information. – Burgi Mar 30 '16 at 11:57
  • 2
    You should post that as an answer. However I feel my original point still stands, you should investigate server based report technologies to move the heavy processing out of the spreadsheet. – Burgi Mar 30 '16 at 13:57
1

Ok I figured it out and its all my own fault. I had hidden everything from row 400 downwards, but had a macro with the range set incorrectly and it was adding array formulas all the way down to row 10000000, so it was technically still working its way through, but was just taking ages. I have since corrected this, and the file size has dropped to around 20MB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.