What Is a Circular Reference in Microsoft Excel?

Bad formulas can create circular references in Excel.

The circular reference error in Microsoft Excel lets you know a formula contains a reference to itself--either within the formula or through another cell in the formula. In some cases, circular references are expected, even planned, but they can also indicate errors.

Circular reference stops calculations

A circular reference stops calculations because it creates a loop under default settings. Basically, Microsoft Excel is trying to solve simultaneous equations on the same variable. Excel has trouble knowing when to stop guessing.

Fixing a circular reference

If you accidentally make a circular reference, ignore the pop-up warning or fix the formula immediately. Check for existing circular references using the "Error Checking" function of the "Formula Auditing" menu. Excel will trace the circular reference so you can determine the best way to adjust your formula.

Forcing the calculation

If the circular reference is included to iterate--run through a calculation several times--you can modify the way Microsoft Excel handles calculations. Adjust the calculation settings in the "Excel options" by reducing the number of iterations performed for each calculation.

References

• Microsoft Office : Remove or allow a circular reference