Concept: Circular References
Caution: In virtually all cases, a circular reference indicates an error that you must
correct. We strongly recommend not enabling iterative calculations, particularly
in workbooks that contain live data; doing so obscures data errors that can lead
to poor performance and unexpected behavior
.Circular references occur in a workbook when a cell refers to itself, or to another cell that
references the original cell. The formula requires a result that is dependent on the
original cell so an error occurs.
A red icon 
indicates that Worksheets didn't finish calculating; the workbook is in a state
where some formulas didn't fully run and are showing stale values. You can hover the
cursor over the icon to see a tooltip with information about the problem. There are
two common causes for a workbook to prematurely stop calculating: either the
calculation limit was reached, or one or more circular references exist in the
workbook. If the problem is caused by a circular reference, you can click the red
icon to navigate to the cell location of the reference. If you have more than one
circular reference, Worksheets navigates to the location of the first one.
indicates that Worksheets didn't finish calculating; the workbook is in a state
where some formulas didn't fully run and are showing stale values. You can hover the
cursor over the icon to see a tooltip with information about the problem. There are
two common causes for a workbook to prematurely stop calculating: either the
calculation limit was reached, or one or more circular references exist in the
workbook. If the problem is caused by a circular reference, you can click the red
icon to navigate to the cell location of the reference. If you have more than one
circular reference, Worksheets navigates to the location of the first one.In some rare situations, you might want to allow circular references.
Example: You have a basic cost of $1,000,000 for a project. A consultant earns a 5% fee based
on the total project cost, which is $1,000,000 plus the consultant fee. Because the
fee is part of the calculation, it's a circular reference in the spreadsheet, so you
need to allow iterative calculation to occur.
A | B | C |
|---|---|---|
Basic Cost | $1,000,000.00 | |
Consult Fee | =B3*C2 | 5% |
Total Cost | =SUM(B1:B2) |
To enable circular references in a workbook to calculate:
- Select .
- SelectEnable Iterative Calculationand enter values for:
- Maximum iterations: Enter a value of 100 or fewer iterations.
- Maximum change: Enter a maximum change value. The default is .001. Worksheets does the circular calculation for the number of iterations that you specified, or until the result changes by less than this value.
If you enable iterative calculation but the iteration calculation limit is reached, a yellow
icon
displays in
the workbook.
displays in
the workbook.If you open a workbook containing circular references, and your recalculation setting is
Manual
, a message displays; select to update the formula.