Therefore, you usually get slow calculation if your circular references span more than one worksheet.Try to move the circular calculations onto a single worksheet or optimize the worksheet calculation sequence to avoid unnecessary calculations.
After the circular references and their dependents are identified, each iteration requires Excel to calculate not only all the cells in the circular reference, but also any cells that depend on the cells in the circular reference chain, together with volatile cells and their dependents.If you have a complex calculation that depends on cells in the circular reference, it can be faster to isolate this into a separate closed workbook and open it for recalculation after the circular calculation has converged.It is important to reduce the number of cells in the circular calculation and the calculation time that is taken by these cells.Summary: This article discusses tips for optimizing many frequently occurring performance obstructions in Microsoft Excel.This article is one of three companion articles about techniques that you can use to improve performance in Excel as you design and create worksheets.
For more information about how to improve performance in Excel, see In this article References and Links Minimizing the Used Range Allowing for Extra Data Lookups Array Formulas and SUMPRODUCT Using Functions Efficiently Faster VBA Macros Excel File Formats Performance and Size Workbook Opening, Closing, Saving, and Size Other Performance Optimizations Conclusion About the Authors Additional Resources Published: June 2010 Provided by: Charles Williams, Decision Models Limited │ Allison Bokone, Microsoft Corporation │ Chad Rothschiller, Microsoft Corporation │ About the Authors Contents To increase clarity and avoid errors, design your formulas so that they do not refer forward (to the right or below) to other formulas or cells.
Forward referencing usually does not affect calculation performance, except in extreme cases for the first calculation of a workbook, where it might take longer to establish a sensible calculation sequence if there are many formulas that need to have their calculation deferred.
Calculating circular references with iterations is slow because multiple calculations are needed.
Frequently you can "unroll" the circular references so that iterative calculation is no longer needed.
For example, in cash flow and interest calculations, try to calculate the cash flow before interest, then calculate the interest, and then calculate the cash flow including the interest.
Excel calculates circular references sheet by sheet without considering dependencies.