When a
formulaA sequence of values, cell references, names, functions, or operators in a cell that produces a new value from existing values. A formula always begins with an equal sign (=).
refers to the cell that contains the formula, either directly or indirectly, Excel
creates a circular reference. For example, if you enter a formula in cell A1, such as
=A1/A1 or
=A1+A2, Excel
can’t successfully calculate a result because the formula in A1 depends on the cell A1 itself. Because Excel can’t resolve this circular reference, 0 is displayed in the cell. Excel returns 0 in a cell when it encounters a circular reference.
Do any of the following:
 | Locate and remove a circular reference |
A circular reference in a cell causes an unexpected result in the cell, and can also cause a message to appear. In both cases, you can locate and remove the incorrect reference. Don’t be tempted to delete a formula just because it creates a circular reference – you should attempt to fix it. If you didn’t create the formula that is returning a circular reference, contact the person who created the formula to find out its purpose.
-
Look in the status bar to locate the circular reference, and then click that cell.
-
If no indicator appears in the cell, on the Formulas tab, under Audit Formulas, turn on
tracer arrowsWhen auditing formulas, arrows that show the relationship between the active cell and related cells. Tracer arrows are blue when pointing from a cell that provides data to another cell; when a cell contains an error value, such as #VALUE! or #DIV/0!, the tracer arrows are red. A black arrow points from a sheet icon 
to the selected cell on another sheet.
by clicking Trace Precedents 
or Trace Dependents 
.
Tracer arrows indicate the cells that affect the value of the currently selected cell. You can move between cells in a circular reference by double-clicking the tracer arrows. The following picture shows tracer arrows that point to A2, a cell that contains a circular reference.
-
Review the cells highlighted by the tracer arrows and correct the formula in the cell that is causing the circular reference.
-
Press
RETURN. When the circular reference is corrected, the tracer arrows move to the next circular reference; if there is one.
 | Note The status bar identifies one circular reference at a time, for example, "Circular: A2". To locate multiple circular references in a sheet, you must first resolve the current circular reference before the status bar shows the location of the next circular reference. |
-
Repeat these steps until the status bar no longer displays a message about a circular reference.
| Make a circular reference work by changing the number of times Excel iterates formulas |
Unless you are an experienced Excel
user and are highly familiar with iterative calculations, it is unlikely that you will want to keep any circular references intact. If you want to keep the circular reference, you can enable iterative calculations, but you must determine how many times the formula should recalculate. When you turn on iterative calculations without changing the values for maximum iterations or maximum change, Excel
stops calculating after 100 iterations or after all values in the circular reference change by less than 0.001 between iterations, whichever comes first. However, you can control the maximum number of iterations and the amount of acceptable change.
-
On the Excel menu, click Preferences.
-
Under Formulas and Lists, click Calculation 
, and then under Iteration, select the Limit iteration check box.
-
In the Maximum iterations box, type the number of iterations that you want to set as the maximum number of times Excel
recalculates.
The higher the number of iterations, the more time Excel
needs to calculate a sheet.
-
In the Maximum change box, type the amount that you want to set as the maximum amount of change between calculation results.
The smaller the number, the more accurate the result and the more time Excel
needs to calculate a sheet.
