Leading the way in Microsoft Office Development

What are Circular References ?

A circular reference is created if you enter a formula into a cell that links directly or indirectly to itself.

A circular reference is just a formula that depends on its own value.

There should not be any circular references in your workbooks unless you specifically want them to be there.

If you receive a circular reference warning when you are not expecting one it means that the formula has been entered incorrectly.

These can be created very easily (often by mistake). Just link cell B2 to cell B3 and then link cell B3 to cell B2.

Normally circular references are created by mistake although there are a few occasions when they can be useful.

When an open workbook contains a circular reference this is indicated in the status bar.

The cell containing the circular reference is only displayed when the cell is on the active worksheet.

Iteration

This enables you to have circular references and offers a method of controlling it.

You can enter a circular reference if the "Iteration" checkbox is ticked on your (Tools > Options)(Calculation tab).

Excel will continue to iterate until either a maximum number of iterations have been performed or until the values change by less than a given amount.

Circular references can be used to solve convergence problems because each time Excel recalculates the results in the cells get closer and closer to a particular value.

Maximum Iterations - The default maximum number of iterations is 100.

Maximum Change - The default maximum change between the values is 0.001.

Calculation

If the word calculate appears in the status bar after the iterations are finished, more iterations are possible.

You can either accept the current result, increase the number of iterations or lower the maximum change amount.

Circular Reference Toolbar

This toolbar can be very useful in helping to track down any cells that contain circular references and to help track down the cause.

You can display this toolbar by selecting (View > Toolbars > Circular Reference).

This toolbar can only be manually displayed when an open workbook contains a circular reference.

Using tracer arrows will help to identify all the cells that a particular formula refers to.

If you glance at the formula or the status bar you can pinpoint the exact cell that contains the error.

For more details, please refer to the Circular Reference Toolbar page.

Persistent Settings

The Iteration value and the calculation setting are workbook specific although Excel retrieves these settings from the first workbook that is opened.

These settings are not changed (by default) for any subsequent workbooks that are opened.

You can manually change these options for a workbook but there is no guarantee that the next time you open the workbook it will use those settings.

If the Iteration checkbox is not ticked and you have the calculation mode set to Automatic then when you open a workbook containing a circular reference the warning message will be displayed.

You can prevent this by explicitly setting the Iteration checkbox value to ticked in code.

If you have a Personal.xls file then this is "always" the first workbook to be opened when you launch Excel and therefore it is this setting that will always be used.

This subroutine can be added to the ThisWorkbook module of your Personal.xls to make sure that whenever you open Excel the Iteration checkbox will be ticked.

Things to Remember

Some circular references are useful (in the case of convergence) and some are even essential in certain situations.

Circular references can be useful when you want a value to converge.

Once the circular reference error has been dismissed it will not appear again until the worksheet is recalculated.

You cannot reference its own value in a function either by cell reference or in VBA – cannot make a reference to it at all