1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Circular Reference

By

Excel Circular Reference

Excel Circular Reference

© Ted French
Definition:

A circular reference occurs in Excel when:

  1. A formula contains a cell reference to the cell containing the formula itself. An example of this type of circular reference would be if the formula =B4 + B5 was located in cell B5.

  2. As shown in the image to the right, a formula references another formula which either directly or indirectly refers back to the cell containing the original formula.

Circular Reference Warning

If a circular reference occurs, Excel displays an Alert dialog box indicating the problem.

User options when this dialog box appears are to click OK or Help - neither of which will fix the circular reference problem.

If you read the long and somewhat confusing message in the dialog box you will discover that:

  • clicking on Help is meant to be used when unintentional circular references occur - this takes you to Excel's help file information on circular references

  • clicking OK is used to tell Excel that the circular reference was done intentionally and that it should leave it in place

Unintentional Circular References

If the circular reference was done unintentionally, the help file information will tell you how to go about finding and removing circular references.

The help file will direct you to use Excel's Error Checking tool located under Formulas > Formula Auditing on the ribbon.

Intentional Circular References

Excel's circular reference doesn't offer a fix for a circular reference problem because not all circular references are mistakes.

While these intentional circular references are less common than the unintentional ones, they can be used if you want Excel to iterate or run a formula multiple times before producing a result.

Enabling Iterative Calculations

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

To enable iterative calculations:

  1. Click on the File tab (or the Office button in Excel 2007)

  2. Click Options to open the Excel Options dialog box

  3. In the left hand panel of the dialog box, click on Formulas

  4. In the right hand panel of the dialog box, select the Enable iterative calculation check box

Below the check box options are available for:

  • setting the maximum number of iterations - the number of times Excel should recalculate the formula

  • setting the maximum amount of change acceptable between calculation results - the smaller the number, the more accurate the result

©2014 About.com. All rights reserved.