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.
Introduction 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.
- Compute some formulas by hand; do they equal Excel's results?
- Are the numbers reasonable and within the expected range?
- Verify the accuracy with source documents; make sure you transferred the information correctly.
- Do your labels accurately describe the values within the worksheet?
- When multiplying numbers by values with decimals, do the values that print out add up correctly? This is a common problem because of Excel's automatic rounding.
- When creating formulas that you find awkward, use values that are easy to test.
Tracking 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:
-
From the View menu, select Toolbars » Formula Auditing
The Formula Auditing toolbar appears.
Macintosh:
-
From the View menu, select Toolbars » Auditing
The Auditing toolbar appears.
To access the Circular Reference toolbar:
-
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. -
Select the Toolbars tab
-
From the Toolbars scroll box, select Circular Reference
The Circular Reference toolbar appears.
-
Click OK
Correcting Circular References
-
On the Circular Reference toolbar, from the Navigate Circular Reference pull-down list, select the cell reference to be adjusted
-
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.
