In which order does Excel process its formulae?

Question

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?

Answer 1

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:

• OzGrid.com: Speed up Slow Calculations in Excel
• MSDN Library: Recalculation in Microsoft Excel 2002
• Microsoft Support: How formula calculations are performed in Excel
• MSDN Library: Improving Performance in Excel 2007

Answer 2

Usually when Excel slows down while editing, it is because of volatile cells which have many dependents and therefore the dependents and their dependents also become volatile and ALL recalculate when an edit is made. You can try the profiler in the FormulaDesk add-in to track down all volatile cells and their calculation speeds.

Disclaimer: FormulaDesk is my product