When a formula refers back to its own cell, either directly or indirectly, it creates a circular reference. A circular reference can have a significant impact on performance because it can iterate indefinitely. Iteration is the repeated recalculation of a worksheet until a specific numeric condition is met. By default, iterative calculations are turned off in Microsoft® Excel®. You can handle a circular reference by doing one of the following: remove the circular reference or enable iterative calculations. In this tip we explain how you can locate and remove a circular reference.
Note: Download the sample workbook to practice this exercise.
Applies to: Microsoft Excel 2007, 2010 and 2013
The screenshot below will be used for this example.
1. To find the total for the revenue balances enter the formula =SUM(B2:G2) into cell G2.
- This will create a circular reference because the formula lives in cell G2 and it is trying to calculate itself.
2. An error message will be displayed.
3. Click OK
4. The status bar also displays the message “circular references” as shown in the screen shot below.
5. If you can’t find the error, select Formulas, click the arrow next to Error Checking, point to Circular References, and then click the first cell listed in the submenu.
6. Review the formula in the cell (G2). In this case type the correct formula as =Sum(B2:F2).
7. If cell G2 is not the cause of the circular reference error, click the next cell in the Circular References sub-menu as per screenshot above.
8. Continue to review and correct circular references in the workbook until the status bar no longer displays the message “Circular References”.
9. If you have circular references in other worksheets, but not in the active worksheet, the status bar displays only “Circular References” with no cell addresses.
10. You can move between cells in a circular reference by double-clicking the tracer arrows. Tracer arrows indicate the cells that affect the value of the currently selected cell.
11. To show tracer arrows for this example, select the Formulas tab and Trace Precedents as shown in the screenshot below.
The result will be as shown in the screenshot below.
