1

(Excel 2010, Win 7, 10GB RAM): I have a complex workbook, 4MB on disk, that takes 3-4 full minutes to load from disk. The main worksheet's workhorse range is 30 rows by 90 columns, many with lengthy formulas, and many columns have 5 or 6 conditional formatting criteria.

After it's in RAM, it recalcs reasonably quickly (3 sec), so my question is about why it takes so long to load from disk. Because recalc happens quickly, I suspect the conditional formatting (not the formulas) is what causes the initial load-from-disk to take 3+ minutes.

Just curious if anyone has some experience with similarly long load times due to conditional formatting complexity (or numerous complex formulas).

3
  • Do you have references for external data? That can even be part of the delay, however 3-4 minutes is really long. – Máté Juhász Jun 10 '15 at 14:26
  • Have you turned off automatic calculation to see if that is affecting it? – CharlieRB Jun 10 '15 at 15:53
  • Are you sure you have 10 GB of RAM? That's a very unusual configuration, though possibly not impossible. – ChrisInEdmonton Jun 12 '15 at 15:16
2

I've seen older complex workbooks pick up quirks after a few years of usage, for varying reasons. In many cases I've seen performance improve by re-saving the file in Excel's new 'binary format', .xlsb. This should dramatically reduce the file size, and may iron out faults the file may have inherited. Even if it doesn't resolve your root cause you may cut your file size by 75% and may save just as much time opening the file up each time.

When we encountered generic difficulties like this on our office system, it was actually an issue with the network efficiency, that just became more obvious when opening larger files. A few things I'd check:

  1. Is it definitely just the one file which does this, or is there a smaller lag on other files in the same location? If you're in an office environment then talk to your helpdesk about whether the location of your files' storage could be impacting your load times.
  2. Are there any Excel Add-ins which could be complicating the issue? Try re-installing them or Excel itself.
  3. If you do suspect the formulas, try simplifying them, or even testing a trimmed file with half as many rows to see if it takes noticeably less time to load.
1
  • WOW, thanks Steve! Although binary didn't reduce the file size much (5% smaller), it reduced the load-from-disk time from 3.5 minutes to 1.5 seconds. Not sure what makes the difference, but I'm very happy with the result. Doesn't get much better than that. – Steve123 Jun 12 '15 at 14:58

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