Skip to main content
Important COVID-19 updates   READ MORE »

(Archives) Microsoft Excel 2007: Correcting Circular References

Last updated

This article is based on legacy software.

Circular references are a common error in Excel that can cause problems with 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.
expense report

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.

About the Formula Auditing Tools

trace dependents 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 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 arrows 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.

Locating 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.

Was this article helpful? Yes No

View / Print PDF