This process is equal to two or three iterations of the calculation.Īfter 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. Try to move the circular calculations onto a single worksheet or optimize the worksheet calculation sequence to avoid unnecessary calculations.īefore the iterative calculations start, Excel must recalculate the workbook to identify all the circular references and their dependents. Therefore, you usually get slow calculation if your circular references span more than one worksheet. For example, in cash flow and interest calculations, try to calculate the cash flow before interest, calculate the interest, and then calculate the cash flow including the interest.Įxcel calculates circular references sheet-by-sheet without considering dependencies. Frequently you can 'unroll' the circular references by using algebra so that iterative calculation is no longer needed. Minimize use of circular references with iterationĬalculating circular references with iterations is slow because multiple calculations are needed, and these calculations are single-threaded.

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. To increase clarity and avoid errors, design your formulas so that they don't refer forward (to the right or below) to other formulas or cells. Do not use forward referencing and backward referencing Learn how to improve performance related to types of references and links.

Applies to: Excel | Excel 2013 | Office 2016 | VBAįollow these tips for optimizing many frequently occurring performance obstructions in Excel.

Define sub in excel vba on mac