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 2003/2004

Correcting Circular References

In order to troubleshoot formulas, one of the steps to take is to identify and correct all circular references within your worksheet.

return to topIntroduction to Troubleshooting

As you are reviewing your worksheets, asking yourself the following questions can help you identify problems within your worksheet and ensure the integrity and accuracy of your worksheets.

return to topTracking Down Circular References

Circular references occur when a formula refers to itself to determine the answer. For example, if the function =sum(b1:b10) was to be displayed in cell B10, we would have a circular reference because the result of this formula is required to determine the result. To correct the problem, place the function in a different cell, perhaps B11, or edit the range to stop at B9.

When you try to enter a formula containing a circular reference, a dialog box appears, helping you avoid such references. The Formula Auditing and Circular Reference toolbars can also help you avoid circular references.

To access the Formula Auditing toolbar:


Windows:

  1. From the View menu, select Toolbars » Formula Auditing
    The Formula Auditing toolbar appears.
    Formula Auditing toolbar

Macintosh:

  1. From the View menu, select Toolbars » Auditing
    The Auditing toolbar appears.
    Auditing toolbar

To access the Circular Reference toolbar:

  1. Windows: From the View menu, select Toolbars » Customize...
    The Customize dialog box appears.
    Macintosh: From the View menu, select Toolbars » Customize Toolbars/Menus...
    The Customize Toolbars/Menus dialog box appears.

  2. Select the Toolbars tab

  3. From the Toolbars scroll box, select Circular Reference
    The Circular Reference toolbar appears.
    Circular Reference toolbar

  4. Click OK

return to topCorrecting Circular References

  1. On the Circular Reference toolbar, from the Navigate Circular Reference pull-down list, select the cell reference to be adjusted

  2. In the Formula bar, review and correct the formula until the status bar no longer reflects a circular reference
    HINT: To display arrows showing which cells are referenced in the formula, on the Circular Reference toolbar, click TRACE DEPENDENTS or TRACE PRECEDENTS.

Excellence. Our Measure. Our Motto. Our Goal.