Excel: Locating Circular References

The information in this article applies to:

• Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 1.x, 2.2, 3.x, 4.x, 5.0 5.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0

SUMMARY

A circular reference exists when formulas refer to and are dependent on each other. For example, if cell A1 contains the formula "=B1" and cell B1 contains the formula "=A1", a circular reference has been created because both formulas depend on each other for their answer.

If you create a circular reference in Microsoft Excel, you receive the error message "Can't Resolve Circular References" when you enter the formula. The status bar then displays the following message where "cell_ref" is part of the circular reference:

   Circular: cell_ref

WORKAROUNDS

To find all cells involved in the circular reference, do the following:

Microsoft Excel Version 5.0 and later

Menu Method

1. Select the cell whose cell reference is displayed in the status bar.

2. From the Edit menu, choose Go To, and then choose the Special button.

3. Select the Precedents option, and then select the All Levels option. Choose OK.

Toolbar Method

1. Select the cell whose cell reference is displayed in the status bar.

2. From View menu, choose Toolbars.

3. From the Toolbars list, select the Auditing check box, and choose OK.

4. From the Auditing Toolbar, choose the Trace Precedents button. Click the Trace Precedents button until the system beeps, indicating that there are no more precedents.

Visual Basic Method

1. In a new module, enter the following:

         Sub Example()
            ActiveSheet.CircularReference.Select
            ActiveCell.Precedents.Select
         End Sub
   
   

2. Select the sheet tab of the worksheet that contains the circular reference, and run the Example macro.

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

Microsoft Excel Versions 4.0 and Earlier

1. Select the cell whose cell reference is displayed in the status bar.

2. From the Formula menu, choose Select Special.

3. Select the Precedents option, and then select the All Levels option. Choose OK.

Microsoft Excel 1.5 and Earlier

In Microsoft Excel versions 1.5 and earlier, the error message "Can't Resolve Circular References" will be displayed if formulas refer to each other in a circular manner.

To find all cells involved in the circular reference, do the following:

1. From the Options menu, choose Calculation.

2. Select Manual.

3. Select Iteration.

4. Enter at least 100 (default) in the Maximum Iterations box.

5. Choose the OK button.

6. From the Options menu, choose Calculate Now or press COMMAND+= on the keyboard.

7. Look at the screen for any numbers that are changing rapidly or "flashing." These cells contain circular references and Microsoft Excel is performing iterative calculations on these formulas, changing the cell value with each iteration.

8. If this is a large worksheet, scroll to view another portion of the sheet and repeat steps 6 and 7. Continue checking each portion of the worksheet until all circular references are found.

MORE INFORMATION

By default, the status bar is displayed in Microsoft Excel versions 5.0, 4.0, 3.0 and 2.2. To display the status bar at the bottom of the screen in Microsoft Excel version 5.0, choose Status Bar from the View menu. In Microsoft Excel versions 4.0 and earlier, choose Workspace from the Options menu, select the Status Bar check box, and choose OK.