Circle references in Excel formulas and how to deal with them

circular link occurs in Excel when a formula contains a reference to a cell that contains the formula.

The information in this article applies to Excel 2019, 2016, 2013, 2010, Excel Online, and Excel for Mac.

Content
  1. Example circular link
  2. circular warning
  3. User options
  4. Accidental Circular References
  5. Deliberate circular references
  6. Enable Iterative Calculations
  7. Show zeros in affected cells
  8. Learn more about the circular reference alert

Example circular link

An example of this type of circular reference is shown in the image below, where the formula is in the cell C1 contains a cell reference C1 in the formula:

 = A1 + A2 + A3 + C1 

A reference error can occur when a formula references another formula that eventually references the cell containing the original formula.

circular warning

As shown in the image above, if there is a circular reference in the Excel worksheet, the program will display a warning box indicating the problem. The message in the dialog box is specially worded because not all circular references in formulas are accidental, as described below.

User options

When this dialog box appears, the user can choose: Okay or reference , , which does not resolve the circular reference issue. When you read the message in the dialog box, you will see that:

  • reference is intended to be used in the event of an accidental circular reference. This will take you to the Excel help file via circular links.
  • Okay is used to tell Excel that the circular reference was intentional and that it should be left in place.

Accidental Circular References

If a circular reference has been created inadvertently, the help file shows how to find and remove circular references. The help file prompts you to use the tool error checking in Excel, on the Formulas tab. Many unintended cell references can be corrected without error checking by simply correcting the cell references used in the formula.

Deliberate circular references

Circle reference in Excel does not solve the circle reference problem because not all circle references are errors. While these intentional circular references are less common than unintended ones, they can be used if you want Excel to repeat or run a formula many times before getting the result.

Enable Iterative Calculations

Excel has an option to enable it iterative calculations if you plan to use them.

To enable iterative calculations:

  1. Select File (or button office in Excel 2007).

  2. Select parameters: to open the Excel Options dialog box.

  3. In the left pane of the dialog box, select formulas.

  4. In the right pane of the dialog box, check the box Enable Iterative Calculation .

Below the checkbox are the options:

  • Set the maximum number of iterations; this is the number of times Excel needs to recalculate the formula.

  • Set the maximum allowed change between calculation results – the smaller the number, the more accurate the result.

Show zeros in affected cells

For cells that contain circular references, Excel returns either: 0 , as shown in cell C1 in the example, or the last calculated value in the cell.

In some cases, formulas may run successfully before attempting to calculate the value of the cell reference they are in. When this happens, the cell containing the formula displays the value of the last successful calculation.

Learn more about the circular reference alert

After the first instance of a formula with a circular reference in a workbook, Excel may stop displaying the warning. It depends on the circumstances how and where additional circular references are made.

Examples of when a warning box with a warning message is displayed for the following circular references:

  • The first instance of a circular reference in an open workbook.

  • After all circular references in all open workbooks are deleted, a new circular reference is created.

  • After all workbooks are closed, a circular reference formula is created in the new workbook.

  • The workbook containing the circular reference is open.

Leave a Reply

Your email address will not be published.