How to Get Rid of Circular References in Excel

by Jeff Cunningham

A "circular reference" is when a formula within Microsoft Excel refers back to its own cell either directly or indirectly. This causes the program to iterate the formulas indefinitely, and prevents it from calculating the other open workbooks. One way to resolve the problem is to use Microsoft's built-in debugging feature to examine the formulas that you have entered into the spreadsheet's cells and remove or correct the circular references manually.

Excel 2003

1

Open the "Tools" drop-down menu at the top of the screen if the "Circular Reference" toolbar is not displayed, then click "Customize."

2

Click on the "Toolbars" tab at the top of the window that appears, then check the "Circular Reference" check box if it is not already checked. Click the "OK" button and close the window.

3

Click on and highlight the first cell in the "Navigate Circular Reference" box within the "Circular Reference" toolbar.

4

Examine the formula in the highlighted cell carefully for errors; i.e., referencing the cell itself. If you find no such mistake that could be the cause of the circular reference message, click to the next cell in the "Navigate Circular Reference" box. (If the word "Circular" is shown in the status bar without referencing which cell, switch to another worksheet.)

5

Continue through each cell, reviewing and correcting any circular references present in each until the status bar no longer shows the word "Circular."

Excel 2007

1

Click on the "Formulas" tab at the top of the window.

2

Click on the arrow on the "Error Checking" button in the "Formula Auditing" box.

3

Select the first cell listed in the submenu under "Circular References."

4

Examine the formula in the cell closely and look for self-references. If you find no cause of the circular reference in the cell, click to the next one in the "Circular References" submenu.

5

Repeat the previous step and review each cell in turn until the words "Circular References" disappear from the status bar.

Tip

  • check In Excel 2007, you can move from one cell to another within a circular reference by double-clicking the tracer arrows.

Items you will need

About the Author

Jeff Cunningham has written on science and technology since 2007. He has co-authored volumes on science education and offered commentary on spaceflight on the Google Lunar X Prize blog. Cunningham has a Bachelor of Science in aerospace engineering from the University of Central Florida.

Photo Credits

  • photo_camera Stockbyte/Stockbyte/Getty Images