This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2007

Correcting Circular References

Circular references are a common error in Excel that can cause problems with your formulas. These errors occur when a formula refers to itself to determine the answer.

EXAMPLE: A circular reference will occur if the function =SUM(E3:G3) is displayed in cell G3.
Example of a circular reference

In the example, cell G3 serves as both the result of the function and as one of the cells used to find the function's result. If you type a circular reference into Excel, a dialog box will appear to help you avoid this error. If you find yourself working with a document containing circular references, the following steps will help you locate and correct them.

return to topAbout the Formula Auditing Tools

Button
Name
Function
Trace Dependents button Trace Dependents Dependents are cells used in a formula that contain formulas referring to other cells.
EXAMPLE: If you are summing cells D2:D25 and cell D15 contains a formula which uses values from cells C20:C25, then cell D15 is a dependent of your formula.
Trace Precedents button Trace Precedents Precedents are cells used by a formula in another cell.
EXAMPLE: If you are summing cells D2:D25, those cells are precedents of your formula.
Remove all arrows button Remove All Arrows When you trace dependents or precedents, Excel displays arrows indicating the cells related to your formula. Once you have found the problem with your formula, you can remove the arrows by clicking this button.

 

return to topLocating a Circular Reference

  1. Select the cell containing the circular reference

  2. From the Formulas command tab, in the Formula Auditing group, click TRACE PRECEDENTS or TRACE DEPENDENTS
    NOTES:
    The Formula bar displays the formula.
    Arrows display the precedent or dependent cells.

  3. Identify and correct the problem

  4. If you have more than one circular reference to correct, repeat steps 2 – 3

Excellence. Our Measure. Our Motto. Our Goal.