I've got a fairly large spreadsheet with major calculations going on, and it's starting to slow down every time a value that's part of a calculated field is modified. I'm in the process of optimizing the file, adding arrays where I can, and seeing where I can shave off a few milliseconds here and there.
Let's say there's data in Columns A-H. Column H is set based on relationships between values in Columns A, B and C, which change dynamically from an outside program. Users enter the data in Column F. Formulas in D & E calculate relationships between F & H and H & D, respectively.
How does Excel manage formulae in the case, for instance, where they're dependent on data further into the sheet? Will my value in H be available the first time that the formulae in D & E calculate? or, will D & E calculate based on an old value for H, because H's update hasn't happened yet?
Are there any efficiencies to be gained by positioning dependencies in particular rows or columns in the speadsheet? Do positions above and left the current position get processed sooner than things below and to the right?
Best Answer
Excel calculates cells toward the top left portion of the sheet before it processes those toward the bottom right. So yes, positioning your formulas based on their dependencies matter. In the image below, sheet B calculates faster than A.
(Source: Excel Slow to Respond – Avoiding Mistakes that Make Excel Slow Down to a Crawl)
If you're up for a bit of light reading, I suggest these articles: