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. Jun 10, 2015 at 14:26
  • Have you turned off automatic calculation to see if that is affecting it?
    – CharlieRB
    Jun 10, 2015 at 15:53
  • Are you sure you have 10 GB of RAM? That's a very unusual configuration, though possibly not impossible. Jun 12, 2015 at 15:16

1 Answer 1

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, 2015 at 14:58

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