When working with Excel, we sometimes encounter this warning message:
“Careful, we found one or more circular references in your workbook that might cause your formulas to calculate incorrectly.”
Figure 1. Circular Reference warning message
As defined, a circular reference means that a cell contains a formula that refers to itself, or it contains a reference to another cell, whose value also refers back to the selected cell.
Circular reference examples
Formula containing the cell itself
Below is an example of a formula that results to a circular reference. Note that the formula in cell C8 is =SUM(C3:C8), which contains the cell C8 itself.
Figure 2. Formula containing the cell itself
When the warning message displays, we can either click OK, Help or close the message window. Excel then returns the last calculated result for the cell, or it simply returns zero “0”.
Figure 3. Zero value for cell with circular reference
Cell reference to a cell that refers back to itself
The example below shows that cell B2 refers to the value in cell C2. However, C2 also refers back to B2. Hence, the circular reference.
There are cases when precedent or dependent tracer arrows are instantly shown. We can use this feature to easily trace and fix circular reference.
Figure 4. Two cells referring back to each other
How to find circular references?
Error Checking menu
In order to find the cells containing circular references in our worksheet, we follow these steps:
- Click Formulas tab > Error Checking > Circular References
This option displays the last cell that was entered with a circular reference. In this case, cell C8.
Figure 5. Circular Reference cell address
Status Bar
Alternatively, the status bar also helps us find circular reference, as shown below.
Figure 6. Status Bar displays cell with circular reference
If the circular reference exists in another worksheet, the status bar only displays “Circular References”.
Figure 7. Status Bar preview of circular reference in another sheet
In order to identify the cell with circular reference in another sheet, we click on the Error Checking menu in Formulas tab.
Figure 8. Circular Reference cell address in another sheet
How to remove circular reference?
Excel does not have a formula to remove circular references at once. That task falls on us and we need to review and edit the formula in each cell identified with circular reference. For example, in cell C8 below, we can edit the formula such that it does not refer back to itself.
Figure 9. Circular Reference example
Change the formula in C8 to =SUM(C3:C7). The cell will display the result of calculations “2700”. In order to verify that we have removed the circular reference, the Error Checking menu in Formulas tab will not display any cells with circular reference.
Figure 10. Output: Remove circular reference
How to allow circular reference?
Generally, we don’t want to have circular references in our worksheet. However, there are some rare cases where circular references are needed to arrive at a desired result.
A circular reference is not allowed when iterative calculations are turned off. By default, iterative calculations are disabled in Excel. In order to enable iterative calculation and thus allow circular reference, we follow these steps:
- Click File > Options
- In Excel Options dialog box, click Formulas and tick Enable iterative calculation
Figure 11. Enable iterative calculation in Excel Options
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment