Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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.

enter image description here

(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:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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